2. SQL編程之MySQL自定義函數(shù) Hello world

function與procedure的區(qū)別

在Oracle的function中是只能讀取table中的數(shù)據(jù),不能修改數(shù)據(jù).需要修改數(shù)據(jù)時就需要使用procedure.在語法上基本上沒有區(qū)別.而在MySQL中的function就沒有修改數(shù)據(jù)的限制,不過實際應(yīng)用時也需要嚴格遵循function只讀,procedure可寫的規(guī)范.

function入門

在程序設(shè)計中,函數(shù)是最基本的代碼復(fù)用方式. 在SQL編程中不僅可以使用MySQL本身提供的眾多函數(shù),還可以使用自定義函數(shù).

既然都說入門了,那就從最基礎(chǔ)的說起,先看一個簡單的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);
  -- 賦值運算, 使用字符串連接函數(shù)把兩個字串連接起來
  SET C_RESULT = CONCAT('hello,',I_NAME);

  -- 返回結(jié)果
 RETURN C_RESULT;
END$$

DELIMITER ;

復(fù)制以上代碼,到client進行創(chuàng)建function:

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

# 先創(chuàng)建一個庫
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);
   ->   -- 賦值運算, 使用字符串連接函數(shù)把兩個字串連接起來
   ->   SET C_RESULT = CONCAT('hello,',I_NAME);
   ->
   ->   -- 返回結(jié)果
   ->   RETURN C_RESULT;
   -> END$$
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> DELIMITER ;

再來調(diào)用剛才的f_hello_world一下,看看:

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

OK, 至此我們已經(jīng)完成第一個function的創(chuàng)建. 已經(jīng)了解定義function的語法,參數(shù)的定義,返回值的定義,變量的定義,賦值的語法以及如何返回一個值.
簡單小結(jié)一下:

  1. 在mysql的語法中,基本都上都不分大小寫, database 和 table 名字除外. 所以定義function的名字是大小,但調(diào)用時用小寫沒有問題;
  2. 使用大寫的語法編寫function純屬于個人習(xí)慣, 改成小寫完成沒有問題;
  3. 在function體內(nèi)的每個語句需要用分號來斷句,和client下delimiter就沖突了,所以在命令行上執(zhí)行function創(chuàng)建語句時,需要先修改delimiter,完成后,再把delimiter改為分號;

什么你創(chuàng)建不了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

然后你再試試.

接下來繼續(xù)了解:
* 如何查看當前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);
 -- 賦值運算, 使用字符串連接函數(shù)把兩個字串連接起來
 SET C_RESULT = CONCAT('hello,',I_NAME);

 -- 返回結(jié)果
 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源碼

# 先刪除再創(chuàng)建
mysql> drop function f_hello_world;
mysql> create function ...;

MySQL編程就不推薦在client中進行了,它僅適合在client中演示代碼而以,實際編寫工作需要借助更加高級的工具,推薦使用官方免費的MySQL Workbench.

再來一個真正有意義的例子

有一個地址表t_address存放所有地址,假設(shè)還有一個訂單表t_order,假設(shè)需要用到三個地址,分別為發(fā)貨人地址,收貨人地址,通知人地址,表結(jié)構(gòu)如下:

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);

現(xiàn)在我們要查出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;

查看一下結(jié)果和執(zhí)行計劃:

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來優(yōu)雅的改寫這條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;
  
  -- 返回結(jié)果
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 內(nèi)部的執(zhí)行計劃雖然沒有打印出來, 但是可以很明顯看出function內(nèi)也一個const的執(zhí)行計劃.

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

以是在筆記本中執(zhí)行的僅供參考.

最后總結(jié)一下
看完這篇文章,你至少了解了如下內(nèi)容:

  • 什么SQL編程,以及SQL編程能做什么事情;
  • function和procedure的區(qū)別,及基本使用場景;
  • 如何編寫一個function,及如何修改和刪除它;
  • 使用function優(yōu)雅的改寫SQL

返回目錄

最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容