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

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

返回目录

最后编辑于：2018.12.23 21:37:53

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 213,616评论 6赞 492
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 91,020评论 3赞 387
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 159,078评论 0赞 349
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 57,040评论 1赞 285
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 66,154评论 6赞 385
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 50,265评论 1赞 292
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 39,298评论 3赞 412
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 38,072评论 0赞 268
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 44,491评论 1赞 306
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 36,795评论 2赞 328
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 38,970评论 1赞 341
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 34,654评论 4赞 337
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 40,272评论 3赞 318
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 30,985评论 0赞 21
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 32,223评论 1赞 267
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 46,815评论 2赞 365
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 43,852评论 2赞 351