MySQL之存儲(chǔ)過(guò)程與函數(shù)用法及說(shuō)明

更新時(shí)間：2026年05月10日 10:29:02 作者：微軟技術(shù)分享

文章簡(jiǎn)要介紹了MySQL存儲(chǔ)過(guò)程和函數(shù)的基本概念、創(chuàng)建方法及調(diào)用方式,重點(diǎn)講解了存儲(chǔ)過(guò)程的的創(chuàng)建、調(diào)用、參數(shù)傳遞、變量定義、光標(biāo)、流程控制及常用內(nèi)置函數(shù)等,并提供了創(chuàng)建示例,對(duì)于每個(gè)概念和語(yǔ)法,都給出了相應(yīng)的代碼示例和解釋

簡(jiǎn)單的說(shuō),存儲(chǔ)過(guò)程就是一條或者多條SQL語(yǔ)句的集合,可以理解為腳本,但是起作用不僅限于批處理,下面我們將重點(diǎn)學(xué)習(xí)如何使用創(chuàng)建存儲(chǔ)函數(shù)和過(guò)程,變量的調(diào)用查看等,存儲(chǔ)過(guò)程是MySQL的一個(gè)重點(diǎn)內(nèi)容.

存儲(chǔ)程序可以分為存儲(chǔ)過(guò)程和函數(shù),MySQL中創(chuàng)建存儲(chǔ)過(guò)程和函數(shù)使用的語(yǔ)句分別是:CREATE PROCEDURE和CREATE FUNCTION.使用CALL語(yǔ)句來(lái)調(diào)用存儲(chǔ)過(guò)程,只能用輸出變量返回值.函數(shù)可以從語(yǔ)句外調(diào)用(即通過(guò)引用函數(shù)名),也能返回標(biāo)量值,存儲(chǔ)過(guò)程也可以調(diào)用其他存儲(chǔ)過(guò)程.

創(chuàng)建存儲(chǔ)過(guò)程

創(chuàng)建存儲(chǔ)過(guò)程,需要使用create procedure語(yǔ)句,其基本語(yǔ)法格式如下:

create procedure proc_name ([proc_parameter])
	[characteristics ...] routine_body

如上,create procedure為用來(lái)創(chuàng)建存儲(chǔ)函數(shù)的關(guān)鍵字,proc_name為存儲(chǔ)過(guò)程名稱,proc_parameter為指定存儲(chǔ)過(guò)程的參數(shù)列表,列表的形式如下所示:

[ IN | OUT | INOUT ] param_name type

其中,IN表示輸入的參數(shù),OUT表示輸出的參數(shù),INOUT表示即可以輸入也可以輸出,param_name表示參數(shù)名稱,type表示參數(shù)的類(lèi)型,該類(lèi)型可以使MySQL數(shù)據(jù)庫(kù)中任意的類(lèi)型.

創(chuàng)建簡(jiǎn)單存儲(chǔ)過(guò)程: 創(chuàng)建一個(gè)存儲(chǔ)過(guò)程Proc(),實(shí)現(xiàn)查詢lyshark數(shù)據(jù)表的所有字段.

MariaDB [lyshark]> delimiter //
MariaDB [lyshark]> create procedure Proc()
    -> BEGIN
    -> select * from lyshark;
    -> END //
Query OK, 0 rows affected (0.00 sec)
MariaDB [lyshark]> delimiter ;
MariaDB [lyshark]> call Proc;

以上存儲(chǔ)過(guò)程,只是執(zhí)行了查詢語(yǔ)句的操作,delimiter語(yǔ)句的作用是方式結(jié)束符與冒號(hào)相沖突,當(dāng)我們寫(xiě)完存儲(chǔ)過(guò)程時(shí),應(yīng)該使用相同的閉合語(yǔ)句恢復(fù).

創(chuàng)建帶參存儲(chǔ)過(guò)程: 創(chuàng)建一個(gè)存儲(chǔ)過(guò)程,查詢lyshark表中記錄,過(guò)程名稱為CountProc,并使用count(*)計(jì)算后把結(jié)果放入?yún)?shù)param1中,SQL語(yǔ)句如下:

MariaDB [lyshark]> delimiter //
MariaDB [lyshark]> create procedure CountProc(OUT param1 INT)
    -> BEGIN
    -> select count(*) into param1 from lyshark;
    -> END //
Query OK, 0 rows affected (0.01 sec)
MariaDB [lyshark]> delimiter ;

當(dāng)我們想要調(diào)用上面的存儲(chǔ)過(guò)程是,需要指定一個(gè)變量名來(lái)接收返回結(jié)果,此處指定為temp.

MariaDB [lyshark]> call CountProc(@temp);
Query OK, 1 row affected (0.00 sec)
MariaDB [lyshark]> select @temp;
+-------+
| @temp |
+-------+
|    17 |
+-------+
1 row in set (0.00 sec)

創(chuàng)建帶參存儲(chǔ)過(guò)程: 創(chuàng)建一個(gè)存儲(chǔ)過(guò)程CountProc1,然后調(diào)用這個(gè)過(guò)程,定義SQL語(yǔ)句如下:

MariaDB [lyshark]> delimiter //
MariaDB [lyshark]> create procedure CountProc1(IN id int,OUT temp INT)
    -> BEGIN
    -> select count(*) into temp from lyshark where Gid=id;
    -> END //
Query OK, 0 rows affected (0.00 sec)
MariaDB [lyshark]> delimiter ;

接著我們調(diào)用CountProc1這個(gè)存儲(chǔ)過(guò)程,并給它傳遞相應(yīng)的參數(shù).

MariaDB [lyshark]> select count(*) from lyshark where Gid=101;
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)
MariaDB [lyshark]> call CountProc1(101,@num);
Query OK, 1 row affected (0.00 sec)
MariaDB [lyshark]> select @num;
+------+
| @num |
+------+
|    3 |
+------+
1 row in set (0.00 sec)

創(chuàng)建存儲(chǔ)函數(shù)

存儲(chǔ)函數(shù)的使用方法與MySQL內(nèi)部函數(shù)的使用方法是一樣的,在MySQL中,用戶自己定義的存儲(chǔ)函數(shù)與MySQL內(nèi)部函數(shù)是一個(gè)性質(zhì)的.區(qū)別在于,存儲(chǔ)函數(shù)是用戶自己定義的,而內(nèi)部函數(shù)則是開(kāi)發(fā)者編寫(xiě)的.

創(chuàng)建存儲(chǔ)函數(shù): 創(chuàng)建儲(chǔ)存函數(shù)CountProc2,然后調(diào)用這個(gè)函數(shù),SQL語(yǔ)句如下:

MariaDB [lyshark]> delimiter //
MariaDB [lyshark]> create function CountProc2(myid INT)
    -> returns int
    -> BEGIN
    -> return(select count(*) from lyshark where Gid=myid);
    -> END //
Query OK, 0 rows affected (0.02 sec)
MariaDB [lyshark]> delimiter ;

接著我們來(lái)調(diào)用這個(gè)存儲(chǔ)函數(shù),SQL語(yǔ)句如下:

MariaDB [lyshark]> select CountProc2(101);
+-----------------+
| CountProc2(101) |
+-----------------+
|               3 |
+-----------------+
1 row in set (0.00 sec)

以上可以看出,返回結(jié)果,雖然存儲(chǔ)函數(shù)和存儲(chǔ)過(guò)程的定義稍有不同,但是可以實(shí)現(xiàn)相同功能.

創(chuàng)建存儲(chǔ)函數(shù): 創(chuàng)建名稱為NameZip,該函數(shù)返回select語(yǔ)句查詢結(jié)果,數(shù)值類(lèi)型為字符串,SQL語(yǔ)句如下:

MariaDB [lyshark]> delimiter //
MariaDB [lyshark]> create function NameZip()
    -> returns char(50)
    -> return (select s_name from suppliers where s_call='4521');
    -> //
Query OK, 0 rows affected (0.00 sec)
MariaDB [lyshark]> delimiter ;

調(diào)用一下看看效果,SQL語(yǔ)句如下.

MariaDB [lyshark]> select s_name from suppliers where s_call ='4521';
+------------+
| s_name     |
+------------+
| FastFruit. |
+------------+
1 row in set (0.00 sec)
MariaDB [lyshark]> select NameZip();
+------------+
| NameZip()  |
+------------+
| FastFruit. |
+------------+
1 row in set (0.00 sec)

如果在存儲(chǔ)函數(shù)中的RETURN語(yǔ)句返回一個(gè)類(lèi)型不同于函數(shù)的型的值,返回值將被強(qiáng)制為恰當(dāng)?shù)念?lèi)型.

提示:指定參數(shù)為IN,OUT或者INOUT只對(duì)PROCEDURE是合法的.FUNCTION中總是默認(rèn)為IN參數(shù).RETURNS子句只能對(duì)FUNCTION做指定,對(duì)函數(shù)而言這是強(qiáng)制的.它用來(lái)指定函數(shù)的返回類(lèi)型,而且函數(shù)體必須包含一個(gè)RETURN value語(yǔ)句.

聲明使用變量

變量可以在子程序中聲明并使用,這些變量的作用范圍實(shí)在BEGIN...END程序中,本小姐將介紹定義和賦值一個(gè)變量,定義變量的語(yǔ)句如下:

DECLARE var_name[,varname]...date_type [DEFAULT value];

如上,var_name為局部變量名稱,DEFAULT value子句給變量提供一個(gè)默認(rèn)值,值除了可以被聲明為一個(gè)常數(shù)之外,還可以被指定為一個(gè)表達(dá)式,如果沒(méi)有DEFAULT子句,初始值為NULL.

定義變量: 定義名稱為myparam的變量,類(lèi)型為INT,默認(rèn)值設(shè)置為100,只能在過(guò)程中使用.

DECLARE myparam INT DEFAULT 100;
DECLARE charsss char DEFAULT 'hello';

變量賦值: 定義3個(gè)變量,分別為var1,var2,var3,數(shù)據(jù)類(lèi)型為INT,使用SET為變量賦值,代碼如下:

DECLARE var1,var2,var3 INT;
MariaDB [lyshark]> set @var1=10,@var2=30;
Query OK, 0 rows affected (0.01 sec)
MariaDB [lyshark]> set @var3=@var1+@var2;
Query OK, 0 rows affected (0.00 sec)
MariaDB [lyshark]> select @var3;
+-------+
| @var3 |
+-------+
|    40 |
+-------+
1 row in set (0.00 sec)

使用數(shù)據(jù)光標(biāo)

查詢語(yǔ)句可能返回多條記錄,如果數(shù)據(jù)量非常大,需要在存儲(chǔ)過(guò)程和函數(shù)中使用光標(biāo)來(lái)逐條讀取查詢結(jié)果集中的記錄,光標(biāo)必須在聲明處理程序之前被聲明,并且變量和條件還必須在聲明光標(biāo)或處理程序之前被聲明.

聲明光標(biāo): 聲明名稱為cursor_lyshark的光標(biāo),SQL代碼如下:

declare cursor_lyshark cursor for select Name,Price from lyshark;

打開(kāi)光標(biāo): 打開(kāi)名稱為cursor_lyshark的光標(biāo),SQL代碼如下:

open cursor_lyshark

使用光標(biāo): 使用名稱為cursor_lyshark的光標(biāo),將查詢出的數(shù)據(jù)存入lyshark_name和lyshark_price這兩個(gè)變量中.

fetch cursor_lyshark into lyshark_name,lyshark_price;

關(guān)閉光標(biāo): 關(guān)閉名稱為cursor_lyshark的光標(biāo)文件.

close cursor_lyshark

使用流程控制

流程控制語(yǔ)句用來(lái)根據(jù)條件控制語(yǔ)句的執(zhí)行,MySQL中用來(lái)構(gòu)造控制流程的語(yǔ)句有:IF,CASE,LOOP,LEAVE,ITERATE,REPEAT,WHILE等,每個(gè)流程中可能包含一個(gè)單獨(dú)的語(yǔ)句,或者是使用BEGIN...END構(gòu)造的復(fù)合語(yǔ)句,構(gòu)造可以被嵌套.

IF-THEN-ELSE條件語(yǔ)句: IF語(yǔ)句用來(lái)判斷條件分支

首先傳遞一個(gè)數(shù)值,接收到parameter變量里,然后自增+1,并判斷,如果var=0則返回系統(tǒng)時(shí)間,var=1則返回系統(tǒng)日期.

CREATE PROCEDURE proc_1(IN parameter int)
BEGIN
    DECLARE var int;
    SET var=parameter+1;
		IF var=0
		THEN
				select CURRENT_TIME();
		ELSEIF var=1
		THEN
				select CURRENT_DATE();
		END IF;
  END

接著我們編譯這段過(guò)程,并測(cè)試依次傳入temp=-1和temp=1.

MariaDB [lyshark]> set @temp=0;
Query OK, 0 rows affected (0.00 sec)
MariaDB [lyshark]> call proc_1(@temp);
+----------------+
| CURRENT_DATE() |
+----------------+
| 2018-12-28     |
+----------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
MariaDB [lyshark]> set @temp=-1;
Query OK, 0 rows affected (0.00 sec)
MariaDB [lyshark]> call proc_1(@temp);
+----------------+
| CURRENT_TIME() |
+----------------+
| 07:23:04       |
+----------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

CASE-WHEN-THEN-ELSE語(yǔ)句: 另一個(gè)進(jìn)行條件判斷的語(yǔ)句,條件判斷分支結(jié)構(gòu)

CREATE PROCEDURE proc_2 (IN parameter INT)
    BEGIN
      DECLARE var INT;
      SET var=parameter+1;
      CASE var
        WHEN 0 THEN
          select "這個(gè)數(shù)值是0呀";
        WHEN 1 THEN
          select "這個(gè)數(shù)值是1呀";
        ELSE
          select "這個(gè)數(shù)值是其他呀";
      END CASE ;
    END ;

MariaDB [lyshark]> set @temp=0;
Query OK, 0 rows affected (0.00 sec)
MariaDB [lyshark]> call proc_2(@temp);
+---------------------+
| 這個(gè)數(shù)值是1呀       |
+---------------------+
| 這個(gè)數(shù)值是1呀       |
+---------------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
MariaDB [lyshark]> set @temp=-1;
Query OK, 0 rows affected (0.00 sec)
MariaDB [lyshark]> call proc_2(@temp);
+---------------------+
| 這個(gè)數(shù)值是0呀       |
+---------------------+
| 這個(gè)數(shù)值是0呀       |
+---------------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
MariaDB [lyshark]> set @temp=-9999;
Query OK, 0 rows affected (0.00 sec)
MariaDB [lyshark]> call proc_2(@temp);
+--------------------------+
| 這個(gè)數(shù)值是其他呀         |
+--------------------------+
| 這個(gè)數(shù)值是其他呀         |
+--------------------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

LOOP語(yǔ)句: 用于循環(huán)執(zhí)行重復(fù)語(yǔ)句,LOOP只會(huì)創(chuàng)建一個(gè)循環(huán)過(guò)程,并不會(huì)判斷.

首先使用LOOP語(yǔ)句進(jìn)行循環(huán)操作,id值小于等于5之前,將重復(fù)執(zhí)行循環(huán)過(guò)程,代碼如下:

CREATE PROCEDURE proc_4()
	BEGIN
      DECLARE id INT;
      SET id=0;
      LOOP_LABLE:LOOP
		SELECT "這里開(kāi)始計(jì)數(shù)";
        SET id=id+1;
        IF id >=5 THEN
          LEAVE LOOP_LABLE;  #退出循環(huán)
        END IF;
      END LOOP;
	END;

WHWHILE-DO…END-WHILE語(yǔ)句: 流程循環(huán)語(yǔ)句.

CREATE PROCEDURE proc_4()
    BEGIN
      DECLARE var INT;
      SET var=0;
      WHILE var<6 DO
        select "循環(huán)判斷";
        SET var=var+1;
      END WHILE ;
    END;

ITERATE語(yǔ)句: 通過(guò)引用復(fù)合語(yǔ)句的標(biāo)號(hào),來(lái)從新開(kāi)始復(fù)合語(yǔ)句

CREATE PROCEDURE proc_5()
  BEGIN
    DECLARE v INT;
    SET v=0;
    LOOP_LABLE:LOOP
      IF v=3 THEN
        SET v=v+1;
        ITERATE LOOP_LABLE;
      END IF;
      INSERT INTO t VALUES(v);
      SET v=v+1;
      IF v>=5 THEN
        LEAVE LOOP_LABLE;
      END IF;
    END LOOP;
  END;

REPEAT語(yǔ)句: 此語(yǔ)句的特點(diǎn)是執(zhí)行操作后檢查結(jié)果

CREATE PROCEDURE proc_6 ()
    BEGIN
      DECLARE v INT;
      SET v=0;
      REPEAT
        INSERT INTO t VALUES(v);
        SET v=v+1;
        UNTIL v>=5
      END REPEAT;
    END;

查看刪除過(guò)程

MySQL中,用戶可以使用SHOW STATUS語(yǔ)句或SHOW CREATE語(yǔ)句來(lái)查看存儲(chǔ)過(guò)程和函數(shù),也可以直接從系統(tǒng)的information_schema數(shù)據(jù)庫(kù)中查詢

show status查看存儲(chǔ)過(guò)程:

MariaDB [lyshark]> show procedure status like 'p%' \G
*************************** 1. row ************************
                  Db: lyshark
                Name: proc_1
                Type: PROCEDURE
             Definer: lyshark@%
            Modified: 2018-12-28 21:16:26
             Created: 2018-12-28 21:16:26
       Security_type: DEFINER
             Comment:
character_set_client: utf8mb4
collation_connection: utf8mb4_general_ci
  Database Collation: latin1_swedish_ci
12 rows in set (0.00 sec)

show create查看過(guò)程與函數(shù):

MariaDB [lyshark]> show create function lyshark.NameZip;
+----------+----------+--------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Function | sql_mode | Create Function                                                                                                                                  | character_set_client | collation_connection | Database Collation |
+----------+----------+--------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| NameZip  |          | CREATE DEFINER=`root`@`localhost` FUNCTION `NameZip`() RETURNS char(50) CHARSET latin1
return (select s_name from suppliers where s_call='4521') | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
+----------+----------+--------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+

select 查詢存儲(chǔ)過(guò)程:

MariaDB [lyshark]> select name,created from mysql.proc where db="lyshark" and type="PROCEDURE";
+------------+---------------------+
| name       | created             |
+------------+---------------------+
| CountProc  | 2018-12-28 04:19:58 |
| CountProc1 | 2018-12-28 04:31:10 |
| Proc       | 2018-12-28 03:47:30 |
| proc11111  | 2018-12-28 21:24:19 |
| proc3      | 2018-12-28 05:34:18 |
| proc4      | 2018-12-28 05:37:10 |
| proc8      | 2018-12-28 21:27:12 |
| proc_1     | 2018-12-28 07:20:08 |
| proc_111   | 2018-12-28 21:14:01 |
| proc_1111  | 2018-12-28 21:14:16 |
| proc_2     | 2018-12-28 07:27:32 |
| proc_3     | 2018-12-28 07:35:11 |
| proc_4     | 2018-12-28 21:15:40 |
| proc_444   | 2018-12-28 21:16:26 |
| wang       | 2018-12-28 04:19:07 |
| wang1      | 2018-12-28 04:25:30 |
+------------+---------------------+
16 rows in set (0.00 sec)

select 查詢存儲(chǔ)函數(shù):

MariaDB [lyshark]> select name,created from mysql.proc where db="lyshark" and type="FUNCTION";
+------------+---------------------+
| name       | created             |
+------------+---------------------+
| CountProc2 | 2018-12-28 05:04:28 |
| NameZip    | 2018-12-28 05:16:35 |
+------------+---------------------+
2 rows in set (0.00 sec)

show語(yǔ)句的其他用法:

MariaDB [lyshark]> show procedure status;
MariaDB [lyshark]> show function status;

刪除過(guò)程與函數(shù):

drop procedure lyshark;
drop function countproc;

常用內(nèi)置函數(shù)

函數(shù)表示對(duì)輸入?yún)?shù)值返回一個(gè)具有特定關(guān)系的值,MySQL提供了大量豐富的函數(shù),在進(jìn)行數(shù)據(jù)庫(kù)管理以及數(shù)據(jù)的查詢和操作時(shí)將會(huì)經(jīng)常用到各種函數(shù).通過(guò)對(duì)數(shù)據(jù)的處理,數(shù)據(jù)庫(kù)功能可以變得更加強(qiáng)大,更加靈活地滿足不同用戶的需求.各類(lèi)函數(shù)從功能方面主要分為以下幾類(lèi)：數(shù)學(xué)函數(shù)、字符串函數(shù)、日期和時(shí)間函數(shù)、條件判斷函數(shù)、系統(tǒng)信息函數(shù)和加密函數(shù)等.下面我們就來(lái)介紹一些基礎(chǔ)函數(shù)的使用.

數(shù)學(xué)函數(shù)

絕對(duì)值函數(shù):abx(x)

MariaDB [lyshark]> select abs(2) , abs(-10.5) ,abs(-100);
+--------+------------+-----------+
| abs(2) | abs(-10.5) | abs(-100) |
+--------+------------+-----------+
|      2 |       10.5 |       100 |
+--------+------------+-----------+
1 row in set (0.00 sec)

平方根函數(shù):sqrt(x)

MariaDB [lyshark]> select sqrt(10) , sqrt(50) , sqrt(-10);
+--------------------+--------------------+-----------+
| sqrt(10)           | sqrt(50)           | sqrt(-10) |
+--------------------+--------------------+-----------+
| 3.1622776601683795 | 7.0710678118654755 |      NULL |
+--------------------+--------------------+-----------+
1 row in set (0.00 sec)

獲取整數(shù)的函數(shù):ceil(x),ceiling(x),floor(x)

MariaDB [lyshark]> select ceil(-3.35) , ceiling(3.35) , floor(3.35);
+-------------+---------------+-------------+
| ceil(-3.35) | ceiling(3.35) | floor(3.35) |
+-------------+---------------+-------------+
|          -3 |             4 |           3 |
+-------------+---------------+-------------+
1 row in set (0.00 sec)

獲取隨機(jī)數(shù)函數(shù):rand(),rand(x)

MariaDB [lyshark]> select rand() , rand(10);
+--------------------+--------------------+
| rand()             | rand(10)           |
+--------------------+--------------------+
| 0.5317976954689227 | 0.6570515219653505 |
+--------------------+--------------------+
1 row in set (0.00 sec)

其他函數(shù):round(x),round(x,y),runcate(x,y)

MariaDB [lyshark]> select round(-1.14) , round(1.14) , round(1.66);        #對(duì)數(shù)據(jù)進(jìn)行四舍五入
+--------------+-------------+-------------+
| round(-1.14) | round(1.14) | round(1.66) |
+--------------+-------------+-------------+
|           -1 |           1 |           2 |
+--------------+-------------+-------------+
1 row in set (0.00 sec)
MariaDB [lyshark]> select round(1.38,1) , round(232.38989,3);              #對(duì)指定小數(shù)點(diǎn)后幾位進(jìn)行四舍五入
+---------------+--------------------+
| round(1.38,1) | round(232.38989,3) |
+---------------+--------------------+
|           1.4 |            232.390 |
+---------------+--------------------+
1 row in set (0.00 sec)
MariaDB [lyshark]> select truncate(1.31,1) , truncate(20.9999,2);           #截?cái)嗖僮?，y參數(shù)為保留小數(shù)點(diǎn)后幾位
+------------------+---------------------+
| truncate(1.31,1) | truncate(20.9999,2) |
+------------------+---------------------+
|              1.3 |               20.99 |
+------------------+---------------------+
1 row in set (0.00 sec)

字符串函數(shù)

計(jì)算字符串長(zhǎng)度:CHAR_LENGTH,字節(jié)長(zhǎng)度:LENGTH(str)

MariaDB [lyshark]> select CHAR_LENGTH('date'),CHAR_LENGTH('egg');
+---------------------+--------------------+
| CHAR_LENGTH('date') | CHAR_LENGTH('egg') |
+---------------------+--------------------+
|                   4 |                  3 |
+---------------------+--------------------+
1 row in set (0.00 sec)
MariaDB [lyshark]> select LENGTH('date'),LENGTH('egg');
+----------------+---------------+
| LENGTH('date') | LENGTH('egg') |
+----------------+---------------+
|              4 |             3 |
+----------------+---------------+
1 row in set (0.00 sec)
MariaDB [lyshark]>

合并字符串函數(shù):CONCAT()

MariaDB [lyshark]> select CONCAT('hello','lyshark'),CONCAT('my',NULL,'SQL');
+---------------------------+-------------------------+
| CONCAT('hello','lyshark') | CONCAT('my',NULL,'SQL') |
+---------------------------+-------------------------+
| hellolyshark              | NULL                    |
+---------------------------+-------------------------+
1 row in set (0.00 sec)

字符串替換:INSERT(s1,len,s2) 將從Quest替換,從第二個(gè)字符開(kāi)始,一直替換三個(gè).

MariaDB [lyshark]> select insert('Quest',2,3,'what') ;
+----------------------------+
| insert('Quest',2,3,'what') |
+----------------------------+
| Qwhatt                     |
+----------------------------+
1 row in set (0.00 sec)

大小寫(xiě)轉(zhuǎn)換:LOWER(str),LCASE(str) 將大寫(xiě)轉(zhuǎn)換成小寫(xiě)

MariaDB [lyshark]> select LOWER('LYSHARK'),LCASE('well');
+------------------+---------------+
| LOWER('LYSHARK') | LCASE('well') |
+------------------+---------------+
| lyshark          | well          |
+------------------+---------------+
1 row in set (0.00 sec)

大小寫(xiě)轉(zhuǎn)換:UPPER(str),UCASE(str) 將小寫(xiě)轉(zhuǎn)換成大寫(xiě)

MariaDB [lyshark]> select UPPER('black'),UCASE('mkdirs');
+----------------+-----------------+
| UPPER('black') | UCASE('mkdirs') |
+----------------+-----------------+
| BLACK          | MKDIRS          |
+----------------+-----------------+
1 row in set (0.00 sec)

比較字符串大小:STRCMP(s1,s2) 相同返回0,s1<s2返回-1,其他返回1

MariaDB [lyshark]> select STRCMP('lyshark','lyshark'),STRCMP('txt','ttxt'),STRCMP('ttxt','txt');
+-----------------------------+----------------------+----------------------+
| STRCMP('lyshark','lyshark') | STRCMP('txt','ttxt') | STRCMP('ttxt','txt') |
+-----------------------------+----------------------+----------------------+
|                           0 |                    1 |                   -1 |
+-----------------------------+----------------------+----------------------+
1 row in set (0.00 sec)

字符串逆序:REVERSE(str)

MariaDB [lyshark]> select REVERSE('lyshark');
+--------------------+
| REVERSE('lyshark') |
+--------------------+
| krahsyl            |
+--------------------+
1 row in set (0.00 sec)

日期時(shí)間函數(shù)

獲取當(dāng)前日期:CURDATE()

MariaDB [lyshark]> select CURDATE(),CURRENT_DATE(),CURDATE()+1;
+------------+----------------+-------------+
| CURDATE()  | CURRENT_DATE() | CURDATE()+1 |
+------------+----------------+-------------+
| 2018-12-24 | 2018-12-24     |    20181225 |
+------------+----------------+-------------+
1 row in set (0.00 sec)

獲取當(dāng)前時(shí)間:CURTIME()

MariaDB [lyshark]> select CURTIME(),CURRENT_TIME(),CURTIME()+1;
+-----------+----------------+-------------+
| CURTIME() | CURRENT_TIME() | CURTIME()+1 |
+-----------+----------------+-------------+
| 20:49:04  | 20:49:04       |      204905 |
+-----------+----------------+-------------+
1 row in set (0.00 sec)

獲取日期與時(shí)間:ALL

MariaDB [lyshark]> select CURRENT_TIMESTAMP(),LOCALTIME(),NOW(),SYSDATE();
+---------------------+---------------------+---------------------+---------------------+
| CURRENT_TIMESTAMP() | LOCALTIME()         | NOW()               | SYSDATE()           |
+---------------------+---------------------+---------------------+---------------------+
| 2018-12-24 20:50:19 | 2018-12-24 20:50:19 | 2018-12-24 20:50:19 | 2018-12-24 20:50:19 |
+---------------------+---------------------+---------------------+---------------------+
1 row in set (0.00 sec)

獲取UNIX時(shí)間戳:UNIX_TIMESTAMP()

MariaDB [lyshark]> select UNIX_TIMESTAMP(),UNIX_TIMESTAMP(NOW()),NOW();
+------------------+-----------------------+---------------------+
| UNIX_TIMESTAMP() | UNIX_TIMESTAMP(NOW()) | NOW()               |
+------------------+-----------------------+---------------------+
|       1545702698 |            1545702698 | 2018-12-24 20:51:38 |
+------------------+-----------------------+---------------------+
1 row in set (0.00 sec)

獲取年份:YEAR()

MariaDB [lyshark]> select year('18-10-10');
+------------------+
| year('18-10-10') |
+------------------+
|             2018 |
+------------------+
1 row in set (0.00 sec)

獲取月份:MONTH()

MariaDB [lyshark]> select month('2018-10-05');
+---------------------+
| month('2018-10-05') |
+---------------------+
|                  10 |
+---------------------+
1 row in set (0.00 sec)

獲取星期:DAYNAME()

MariaDB [lyshark]> select dayname('2018-10-25');
+-----------------------+
| dayname('2018-10-25') |
+-----------------------+
| Thursday              |
+-----------------------+
1 row in set (0.00 sec)

獲取天:DAYOFYEAR()

MariaDB [lyshark]> select dayofyear('2018-10-10');
+-------------------------+
| dayofyear('2018-10-10') |
+-------------------------+
|                     283 |
+-------------------------+
1 row in set (0.00 sec)

條件判斷函數(shù)

IF(expr,v1,v2): 如果表達(dá)式expr是true(expr<>0 and expr<> NULL),則if()函數(shù)返回為v1,否則返回v2,if()語(yǔ)句返回值為數(shù)字或字符串,具體情況視其所在語(yǔ)境而定.

MariaDB [lyshark]> select if(1>2,2,3),
    -> if(1<2,'yes','no'),
    -> if(strcmp('test','test1'),'no','yes');
+-------------+--------------------+---------------------------------------+
| if(1>2,2,3) | if(1<2,'yes','no') | if(strcmp('test','test1'),'no','yes') |
+-------------+--------------------+---------------------------------------+
|           3 | yes                | no                                    |
+-------------+--------------------+---------------------------------------+
1 row in set (0.00 sec)

IFNULL(v1,v2): 假如v1不為null,則ifnull()返回值為v1,否則其返回值為v2.

MariaDB [lyshark]> select ifnull(1,2) , ifnull(null,10) , ifnull(1/0,'wrong');
+-------------+-----------------+---------------------+
| ifnull(1,2) | ifnull(null,10) | ifnull(1/0,'wrong') |
+-------------+-----------------+---------------------+
|           1 |              10 | wrong               |
+-------------+-----------------+---------------------+
1 row in set (0.00 sec)

case expr when v1 then r1 [when v2 then r2][else rn] end: 如果expr等價(jià)于某個(gè)vn,則返回對(duì)應(yīng)位置then后面的結(jié)果.如果所用值都不相等,則返回else后面的rn.

MariaDB [lyshark]> select case 2 when 1 then 'one' when 2 then 'two' else 'more' end;
+------------------------------------------------------------+
| case 2 when 1 then 'one' when 2 then 'two' else 'more' end |
+------------------------------------------------------------+
| two                                                        |
+------------------------------------------------------------+
1 row in set (0.00 sec)
MariaDB [lyshark]> select case when 1<0 then 'true' else 'false' end;
+--------------------------------------------+
| case when 1<0 then 'true' else 'false' end |
+--------------------------------------------+
| false                                      |
+--------------------------------------------+
1 row in set (0.00 sec)

系統(tǒng)信息函數(shù)

獲取版本:version()

MariaDB [lyshark]> select version();
+----------------+
| version()      |
+----------------+
| 5.5.60-MariaDB |
+----------------+
1 row in set (0.04 sec)

查看當(dāng)前用戶連接數(shù):connection_id()

MariaDB [lyshark]> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
|               2 |
+-----------------+
1 row in set (0.00 sec)

輸出當(dāng)前用戶的連接信息:show processlist

MariaDB [lyshark]> show processlist;
+----+------+-----------+---------+---------+------+-------+------------------+----------+
| Id | User | Host      | db      | Command | Time | State | Info             | Progress |
+----+------+-----------+---------+---------+------+-------+------------------+----------+
|  2 | root | localhost | lyshark | Query   |    0 | NULL  | show processlist |    0.000 |
+----+------+-----------+---------+---------+------+-------+------------------+----------+
1 row in set (0.01 sec)
MariaDB [lyshark]>
MariaDB [lyshark]> show full processlist;
+----+------+-----------+---------+---------+------+-------+-----------------------+----------+
| Id | User | Host      | db      | Command | Time | State | Info                  | Progress |
+----+------+-----------+---------+---------+------+-------+-----------------------+----------+
|  2 | root | localhost | lyshark | Query   |    0 | NULL  | show full processlist |    0.000 |
+----+------+-----------+---------+---------+------+-------+-----------------------+----------+
1 row in set (0.00 sec)

返回當(dāng)前使用的數(shù)據(jù)庫(kù):schema()

MariaDB [lyshark]> select database(),schema();
+------------+----------+
| database() | schema() |
+------------+----------+
| lyshark    | lyshark  |
+------------+----------+
1 row in set (0.00 sec)

加密解密函數(shù)

加密函數(shù):password(str)

MariaDB [lyshark]> select password('newpass');
+-------------------------------------------+
| password('newpass')                       |
+-------------------------------------------+
| *D8DECEC305209EEFEC43008E1D420E1AA06B19E0 |
+-------------------------------------------+
1 row in set (0.00 sec)

加密函數(shù):MD5(str)

MariaDB [lyshark]> select MD5('mypass');
+----------------------------------+
| MD5('mypass')                    |
+----------------------------------+
| a029d0df84eb5549c641e04a9ef389e5 |
+----------------------------------+
1 row in set (0.00 sec)

加密函數(shù):encode(str,pswd_str)

MariaDB [lyshark]> select encode('secret','cry'),length(encode('secret','cry'));
+------------------------+--------------------------------+
| encode('secret','cry') | length(encode('secret','cry')) |
+------------------------+--------------------------------+
| ?h? ?                  |                              6 |
+------------------------+--------------------------------+
1 row in set (0.00 sec)

解密函數(shù):decode(crypt_str,pswd_str)

MariaDB [lyshark]> select decode(encode('secret','cry'),'cry');
+--------------------------------------+
| decode(encode('secret','cry'),'cry') |
+--------------------------------------+
| secret                               |
+--------------------------------------+
1 row in set (0.00 sec)

其他通用函數(shù)

格式化函數(shù):ormat(x,n)

MariaDB [lyshark]> select format(123.123,2);
+-------------------+
| format(123.123,2) |
+-------------------+
| 123.12            |
+-------------------+
1 row in set (0.00 sec)

IP地址轉(zhuǎn)為數(shù)字:inet_aton(ipaddr)

MariaDB [lyshark]> select inet_aton("192.168.1.1");
+--------------------------+
| inet_aton("192.168.1.1") |
+--------------------------+
|               3232235777 |
+--------------------------+
1 row in set (0.00 sec)

數(shù)字轉(zhuǎn)為IP地址:inet_ntoa

MariaDB [lyshark]> select inet_ntoa(3232235777);
+-----------------------+
| inet_ntoa(3232235777) |
+-----------------------+
| 192.168.1.1           |
+-----------------------+
1 row in set (0.00 sec)