2. SQL编程之MySQL自定义函数 Hello world

2018-12-23 本文已影响0人 Alvinzane

function与procedure的区别

在Oracle的function中是只能读取table中的数据,不能修改数据.需要修改数据时就需要使用procedure.在语法上基本上没有区别.而在MySQL中的function就没有修改数据的限制,不过实际应用时也需要严格遵循function只读,procedure可写的规范.

function入门

在程序设计中,函数是最基本的代码复用方式. 在SQL编程中不仅可以使用MySQL本身提供的众多函数,还可以使用自定义函数.

既然都说入门了,那就从最基础的说起,先看一个简单的hello world:

DELIMITER $$

CREATE FUNCTION `F_HELLO_WORLD`(I_NAME VARCHAR(10)) RETURNS VARCHAR(30)
BEGIN
 -- 功能: 第一个 hello world SQL编程体验 create by dba

  -- 定变量,使用分号断句,不能少
 DECLARE C_RESULT VARCHAR(30);
  -- 赋值运算, 使用字符串连接函数把两个字串连接起来
  SET C_RESULT = CONCAT('hello,',I_NAME);

  -- 返回结果
 RETURN C_RESULT;
END$$

DELIMITER ;

复制以上代码,到client进行创建function:

# 记得要加上 -c, 程序中comments才不会被忽略
mysql -c -S /tmp/mysql3306.sock

# 先创建一个库
mysql> create database db_dba;
mysql> use db_dba;

# 然后把代码粘进来
mysql> DELIMITER $$
mysql> CREATE FUNCTION `F_HELLO_WORLD`(I_NAME VARCHAR(10)) RETURNS VARCHAR(30)
   -> BEGIN
   ->   -- 功能: 第一个 hello world SQL编程体验 create by dba
   ->
   ->   -- 定变量,使用分号断句,不能少
   ->   DECLARE C_RESULT VARCHAR(30);
   ->   -- 赋值运算, 使用字符串连接函数把两个字串连接起来
   ->   SET C_RESULT = CONCAT('hello,',I_NAME);
   ->
   ->   -- 返回结果
   ->   RETURN C_RESULT;
   -> END$$
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> DELIMITER ;

再来调用刚才的f_hello_world一下,看看:

mysql> select f_hello_world('world');
+------------------------+
| f_hello_world('world') |
+------------------------+
| hello,world            |
+------------------------+
1 row in set (0.00 sec)

OK, 至此我们已经完成第一个function的创建. 已经了解定义function的语法,参数的定义,返回值的定义,变量的定义,赋值的语法以及如何返回一个值.
简单小结一下:

在mysql的语法中,基本都上都不分大小写, database 和 table 名字除外. 所以定义function的名字是大小,但调用时用小写没有问题;
使用大写的语法编写function纯属于个人习惯, 改成小写完成没有问题;
在function体内的每个语句需要用分号来断句,和client下delimiter就冲突了,所以在命令行上执行function创建语句时,需要先修改delimiter,完成后,再把delimiter改为分号;

什么你创建不了function?是这个错误吗?

ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

其实也解决方式也很简单:

mysql> set global log_bin_trust_function_creators=1;
Query OK, 0 rows affected (0.00 sec)
mysql> \r
Connection id:    10
Current database: db_dba

然后你再试试.

接下来继续了解:
* 如何查看当前database下的所有function列表
* 如何查看一个function源码
* 如何修改一个function源码

如何查看当前database下的所有function列表

# 方法一
mysql> SHOW FUNCTION STATUS WHERE db = 'db_dba';
+--------+---------------+----------+----------------+---------------------+---------------------+---------------+---------+
| Db     | Name          | Type     | Definer        | Modified            | Created             | Security_type | Comment |
+--------+---------------+----------+----------------+---------------------+---------------------+---------------+---------+
| db_dba | F_HELLO_WORLD | FUNCTION | root@localhost | 2018-12-18 13:34:25 | 2018-12-18 13:34:25 | DEFINER       |         |
+--------+---------------+----------+----------------+---------------------+---------------------+---------------+---------+
1 row in set (0.00 sec)

# 方法二
mysql> select Db,Name,Type,Definer from mysql.proc where db='db_dba';
+--------+---------------+-----------+----------------+
| Db     | Name          | Type      | Definer        |
+--------+---------------+-----------+----------------+
| db_dba | F_HELLO_WORLD | FUNCTION  | root@localhost |
+--------+---------------+-----------+----------------+
4 rows in set (0.00 sec)

如何查看一个function源码

mysql> show create function f_hello_world\G
*************************** 1. row ***************************
           Function: f_hello_world
           sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
    Create Function: CREATE DEFINER=`root`@`localhost` FUNCTION `f_hello_world`(I_NAME VARCHAR(10)) RETURNS varchar(30) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci
BEGIN
 -- 功能: 第一个 hello world SQL编程体验 create by dba

 -- 定变量,使用分号断句,不能少
 DECLARE C_RESULT VARCHAR(30);
 -- 赋值运算, 使用字符串连接函数把两个字串连接起来
 SET C_RESULT = CONCAT('hello,',I_NAME);

 -- 返回结果
 RETURN C_RESULT;
END
character_set_client: utf8mb4
collation_connection: utf8mb4_unicode_ci
 Database Collation: utf8mb4_unicode_ci
1 row in set (0.00 sec)

如何修改一个function源码

# 先删除再创建
mysql> drop function f_hello_world;
mysql> create function ...;

MySQL编程就不推荐在client中进行了,它仅适合在client中演示代码而以,实际编写工作需要借助更加高级的工具,推荐使用官方免费的MySQL Workbench.

再来一个真正有意义的例子

有一个地址表t_address存放所有地址,假设还有一个订单表t_order,假设需要用到三个地址,分别为发货人地址,收货人地址,通知人地址,表结构如下:

CREATE TABLE `t_address` (
  `address_id` int(11) NOT NULL auto_increment,
  `address_name` varchar(50) NOT NULL,
  PRIMARY KEY (`address_id`)
) ENGINE=InnoDB;

CREATE TABLE `t_order` (
  `order_id` int(11) NOT NULL auto_increment,
  `address_id_shipper` int NOT NULL,
  `address_id_receiver` int NOT NULL,
  `address_id_notify` int NOT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB;

insert into t_address values (1,'aaaaaaaaaa'),(2,'bbbbbbbb'),(3,'ccccccccc'),(4,'ddddddddd');
insert into t_order values (1,1,2,3),(2,2,1,3),(3,3,1,2),(4,4,1,2);

现在我们要查出t_order表的所有地址名称,可以这样写:

select a.order_id,       
       b.address_name  as address_name_shipper,
       c.address_name  as address_name_receiver,
       d.address_name  as address_name_notify
from  t_order a
   left join t_address b on a.address_id_shipper=b.address_id
   left join t_address c on a.address_id_receiver=c.address_id
   left join t_address d on a.address_id_notify=d.address_id
where order_id = 1;

查看一下结果和执行计划:

mysql> select a.order_id,       
    ->        b.address_name  as address_name_shipper,
    ->        c.address_name  as address_name_receiver,
    ->        d.address_name  as address_name_notify
    -> from  t_order a
    ->    left join t_address b on a.address_id_shipper=b.address_id
    ->    left join t_address c on a.address_id_receiver=c.address_id
    ->    left join t_address d on a.address_id_notify=d.address_id
    -> where order_id = 1;
+----------+----------------------+-----------------------+---------------------+
| order_id | address_name_shipper | address_name_receiver | address_name_notify |
+----------+----------------------+-----------------------+---------------------+
|        1 | aaaaaaaaaa           | bbbbbbbb              | ccccccccc           |
+----------+----------------------+-----------------------+---------------------+
1 row in set (0.00 sec)

mysql> desc select a.order_id,       
    ->        b.address_name  as address_name_shipper,
    ->        c.address_name  as address_name_receiver,
    ->        d.address_name  as address_name_notify
    -> from  t_order a
    ->    left join t_address b on a.address_id_shipper=b.address_id
    ->    left join t_address c on a.address_id_receiver=c.address_id
    ->    left join t_address d on a.address_id_notify=d.address_id
    -> where order_id = 1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | a     | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
|  1 | SIMPLE      | b     | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
|  1 | SIMPLE      | c     | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
|  1 | SIMPLE      | d     | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
4 rows in set, 1 warning (0.00 sec)

全是主键扫描,性能没有问题,不过可以看出这种写法非常的臃肿, 如果再加其它表的join,那就更加难以维护了.

接下来,看看如何利用function来优雅的改写这条sql.

# 先写一个function

DELIMITER $$

CREATE FUNCTION `F_GET_ADDRESS_NAME`(I_ADDRESS_ID INT) RETURNS VARCHAR(50)
BEGIN
  -- 功能: 获取地址名称

  -- 定变量
  DECLARE C_RESULT VARCHAR(50);
  
  -- 赋值运算, 使用select into语法
  SELECT ADDRESS_NAME
  INTO   C_RESULT
  FROM   t_address
  WHERE  ADDRESS_ID = I_ADDRESS_ID;
  
  -- 返回结果
RETURN C_RESULT;
END$$

DELIMITER ;

改写后的SQL:

mysql> select order_id,f_get_address_name(address_id_shipper),f_get_address_name(address_id_receiver),f_get_address_name(address_id_notify) from t_order where order_id = 1;
+----------+----------------------------------------+-----------------------------------------+---------------------------------------+
| order_id | f_get_address_name(address_id_shipper) | f_get_address_name(address_id_receiver) | f_get_address_name(address_id_notify) |
+----------+----------------------------------------+-----------------------------------------+---------------------------------------+
|        1 | aaaaaaaaaa                             | bbbbbbbb                                | ccccccccc                             |
+----------+----------------------------------------+-----------------------------------------+---------------------------------------+
1 row in set (0.00 sec)

mysql> desc select order_id,f_get_address_name(address_id_shipper),f_get_address_name(address_id_receiver),f_get_address_name(address_id_notify) from t_order where order_id = 1;
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t_order | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

怎么样, 是不是就清爽多了. f_get_address_name 内部的执行计划虽然没有打印出来, 但是可以很明显看出function内也一个const的执行计划.

mysql> desc select address_name from   t_address  where  address_id = 1;
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t_address | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.10 sec)

接下来,还可以做一下两个版本的性能测试对比,function版:

#mysqlslap -S /tmp/mysql3306.sock -i 10 --number-of-queries 5000 -a "select order_id,f_get_address_name(address_id_shipper),f_get_address_name(address_id_receiver),f_get_address_name(address_id_notify) from db_dba.t_order where order_id = 1";
Benchmark
    Average number of seconds to run all queries: 5.464 seconds
    Minimum number of seconds to run all queries: 5.464 seconds
    Maximum number of seconds to run all queries: 5.464 seconds
    Number of clients running queries: 1
    Average number of queries per client: 5000

join版:

#mysqlslap -S /tmp/mysql3306.sock -i 10 --number-of-queries 5000 -a "select a.order_id,b.address_name,c.address_name,d.address_name from  t_order a left join t_address b on a.address_id_shipper=b.address_id left join t_address c on a.address_id_receiver=c.address_id left join t_address d on a.address_id_notify=d.address_id where order_id = 1"
Benchmark
    Average number of seconds to run all queries: 5.545 seconds
    Minimum number of seconds to run all queries: 5.545 seconds
    Maximum number of seconds to run all queries: 5.545 seconds
    Number of clients running queries: 1
    Average number of queries per client: 5000

以是在笔记本中执行的仅供参考.

最后总结一下
看完这篇文章,你至少了解了如下内容:

什么SQL编程,以及SQL编程能做什么事情;
function和procedure的区别,及基本使用场景;
如何编写一个function,及如何修改和删除它;
使用function优雅的改写SQL

返回目录