MySQL存储过程和存储函数（二）

一、流程控制的使用

在存储过程中可以定义流程控制语句，流程控制语句用来根据条件控制语句的执行，MySQL中的用来构造控制流程的语句有：IF语句、CASE语句、LOOP语句、WHILE语句、LEAVE语句、ITERATE语句和REPEAT语句。每个流程中可能包含一个单独语句，或者是使用BEGIN…END构造的复合语句，构造可以被嵌套。下面介绍这些流程控制语句

IF语句

IF语句包含多个条件判断，根据判断的结果为TRUE或FALSE执行相应的语句，语法格式如下：

IF condition THEN
  Statement
ELSELF condition THEN
  statement
ELSE
  statement
END IF

IF condition THEN

Statement

ELSELF condition THEN

statement

ELSE

statement

END IF

IF实现了一个基本的条件构造，如果condition求值为真，响应的SQL语句列表被执行；如果没有condition匹配，则ELSE字句里的语句列表被执行。Statement可以包括一个或多个语句。如果非常熟悉Shell变量应该很好理解这些语句，另外MySQL中还有一个IF()函数，它不同于这里描述的IF语句。

示例

delimiter $
create procedure pif(n int,j char(1))
begin
    if j='gao' then
        select * from testdb.info where salary &gt; n;
    else
        select * from testdb.info where salary &lt; n;
    end if;
end$
delimiter ;

delimiter $

create procedure pif(n int,j char(1))

begin

if j='gao' then

select * from testdb.info where salary > n;

else

select * from testdb.info where salary < n;

end if;

end$

delimiter ;

该示例中，create procedure pif(n int,j char(1))语句怎么使用在创建函数中有说明，IF主要是判断j参数的值，如果为“gao”那么执行第一条SQL语句，如果j的值不等“gao”那么就执行第二条SQL语句，IF语句都需要使用END IF来结束。

mysql> call pif(5000,'gao');

1	mysql> call pif(5000,'gao');

调用存储过程pif并输入参数n和j的值。

CASE语句

CASE是另一个进行条件判断的语句，该语句有2中语句格式，第1种格式如下：

CASE case_expr
  WHEN when_value THEN statement
  [WHEN when_value THEN statement]
[ELSE statement]
END CASE

CASE case_expr

WHEN when_value THEN statement

[WHEN when_value THEN statement]

[ELSE statement]

END CASE

其中，case_expr参数表示条件判断的表达式，决定了那一个WHEN子句会被执行；when_value参数表示表达式可能的值，如果某个when_value表达式与case_expr表达式结果相同，则执行对应THEN关键字后的statement中的语句；statement参数表示不同when_value值的执行语句。

示例

delimiter $
create procedure caseone()
begin
     declare val INT;
     set val = 1;
     case val
       when 1 then select 'val is 1';
       when 2 then select 'val is 2';
       else select 'val is not 1 or 2';
     end case;
end$
delimiter ;

delimiter $

create procedure caseone()

begin

declare val INT;

set val = 1;

case val

when 1 then select 'val is 1';

when 2 then select 'val is 2';

else select 'val is not 1 or 2';

end case;

end$

delimiter ;

mysql> call caseone()\G
*************************** 1. row ***************************
val is 1: val is 1

mysql> call caseone()\G

*************************** 1. row ***************************

val is 1: val is 1

使用CASE判断当val值为1时，输出字符串“val is 1”；当val值为2时，输出字符串“val is 2”；否则输出字符串“val is not 1 or 2”。

CASE语句的第二种格式如下：

CASE
  WHEN condition THEN statement
  [WHEN condition THEN statement]
  [ELSE statement]
END CASE

CASE

WHEN condition THEN statement

[WHEN condition THEN statement]

[ELSE statement]

END CASE

其中，condition参数表示条件判断语句；statement参数表示不同条件的执行语句。该语句中，WHEN语句将被逐个执行，直到某个condition表达式为真，则执行对应THEN关键字后面的statement语句。如果没有条件匹配，ELSE字句里的语句被执行。

示例

delimiter $
create procedure casetwo ()
begin
     declare val INT;
     case
       when val IS NULL then select 'val is null';
       when val < 0 then select 'val is less than 0';
       when val > 0 then select 'val is greater than 0';
       else select 'val is 0';
     end case;
end$
delimiter ;

delimiter $

create procedure casetwo ()

begin

declare val INT;

case

when val IS NULL then select 'val is null';

when val < 0 then select 'val is less than 0';

when val > 0 then select 'val is greater than 0';

else select 'val is 0';

end case;

end$

delimiter ;

mysql> call casetwo()\G
*************************** 1. row ***************************
val is null: val is null

mysql> call casetwo()\G

*************************** 1. row ***************************

val is null: val is null

当val值为空，输出字符串“val is NULL”；当val值小于0时，输出字符串“val is less than 0”；当val值大于0时，输出字符串“val is greater than 0”；否则输出字符串“val is 0”。

LOOP语句

LOOP循环语句用来重复执行某些语句，与IF和CASE语句相比，LOOP只是创建一个循环操作的过程，并不进行条件判断。LOOP内的语句一直重复执行直到循环被退出，跳出循环过程，使用LEAVE子句，LOOP语句的基本格式如下：

[loop_label:] LOOP
  Statement
END LOOP [loop_label]

[loop_label:] LOOP

Statement

END LOOP [loop_label]

其中，loop_label表示LOOP语句的标注名称，该参数可以省略；statement参数表示需要循环执行的语句。

示例

create procedure loopone()
 begin
     declare id int default 0;
     add_loop:loop
       set id = id+1;
       if id >= 10 then leave add_loop;
       end if;
     end loop add_loop;
end$
delimiter ;

create procedure loopone()

begin

declare id int default 0;

add_loop:loop

set id = id+1;

if id >= 10 then leave add_loop;

end if;

end loop add_loop;

end$

delimiter ;

LEAVE语句

LEAVE语句用来退出任何被标注的流程控制构造，LEAVE语句基本格式如下：

LEAVE label

1	LEAVE label

其中，label参数表示循环的标志，LEAVE和BEGIN….END或循环一起使用。使用LEAVE语句退出循环，代码如下：

Add_num:loop
  Set @count=@count+1;
  If @count=50 then leave add_num;
End loop add_num;

Add_num:loop

Set @count=@count+1;

If @count=50 then leave add_num;

End loop add_num;

该循环执行count加1的操作，当count的值等于50时，使用LEAVE语句跳出循环。

ITERATE语句

ITERATE语句将执行顺序转到语句段开头处，语句基本格式如下：

ITERATE label

1	ITERATE label

Iterate只可以出现在LOOP、REPEAT和WHILE语句内。Iterate的意思为“在此循环”，label参数表示循环的标志。Iterate语句必须跟在循环标志前面。

示例

delimiter $
create procedure iterateone()
begin
    declare var int default 0;
    my_loop:loop
      set var=var+1;
       if var < 10 then iterate my_loop;
       elseif var > 20 then leave my_loop;
      end if;
      select 'var is between 10 and 20';
    end loop my_loop;
end$
delimiter ;

delimiter $

create procedure iterateone()

begin

declare var int default 0;

my_loop:loop

set var=var+1;

if var < 10 then iterate my_loop;

elseif var > 20 then leave my_loop;

end if;

select 'var is between 10 and 20';

end loop my_loop;

end$

delimiter ;

var等于0，如果var的值小于10时，重复执行var加1操作；当var大于等于10并且小于20时，打印消息“var is between 10 and 20”；当var大于20时，退出循环。

REPEAT语句

REPEAT语句创建一个带条件判断的循环过程，每次语句执行完毕之后，会对条件表达式进行判断，如果表达式为真，则循环结束；否则重复执行循环中的语句。Repeat语句的基本格式如下：

[repeat_label]:REPEAT
  Statement
UNTIL condition
End REPEAT [repeat_label]

[repeat_label]:REPEAT

Statement

UNTIL condition

End REPEAT [repeat_label]

示例：id值小于等于0之前，将重复执行循环过程

delimiter $
create procedure repeatone()
begin
    declare id int default 0;
    repeat
      set id = id+1;
    until id &gt;= 10 end repeat;
    select id;
end $
delimiter ;

delimiter $

create procedure repeatone()

begin

declare id int default 0;

repeat

set id = id+1;

until id >= 10 end repeat;

select id;

end $

delimiter ;

mysql&gt; call repeatone()\G
*************************** 1. row ***************************
id: 10

mysql> call repeatone()\G

*************************** 1. row ***************************

id: 10

如果写成 until 0 的话，那么就是一直循环执行。

delimiter $
create procedure repeatone3()
begin
    declare id int default 0;
    repeat
        set id = id+1;
    until 0 end repeat;
    select id;
end $
delimiter ;

delimiter $

create procedure repeatone3()

begin

declare id int default 0;

repeat

set id = id+1;

until 0 end repeat;

select id;

end $

delimiter ;

WHILE语句

WHILE语句创建一个带条件判断的循环过程，与REPEAT不同，WHILE在执行语句时，先对指定的表达式进行判断，如果为真，则执行循环内的语句，否则退出循环。WHILE语句的基本格式如下：

[while_label:]WHILE condition DO
  Statement
END WHILE [while_label]

[while_label:]WHILE condition DO

Statement

END WHILE [while_label]

while_label为WHILE语句的标注名称，condition为进行判断的表达式，如果表达式结果为真，WHILE语句内的语句或语句群被执行，直至condition为假，退出循环。

示例：使用WHILE计算1+n的和

delimiter $
create procedure whileone(n smallint)
begin
  declare i int;
  declare s int;
  set i = 1;
  set s = 0;
  while i <= n do
    set s = s + i;
    set i = i + 1;
  end while;
  select s;
end$
delimiter ;

delimiter $

create procedure whileone(n smallint)

begin

declare i int;

declare s int;

set i = 1;

set s = 0;

while i <= n do

set s = s + i;

set i = i + 1;

end while;

select s;

end$

delimiter ;

mysql> call whileone(10)\G
*************************** 1. row ***************************
s: 55

mysql> call whileone(10)\G

*************************** 1. row ***************************

s: 55

二、游标的使用

查询语句可能返回多条记录，如果数据量非常大，需要在存储过程和存储函数中使用游标来逐条读取查询结果集中的记录。在存储过程中可以使用游标对结果集进行循环处理，应用程序可以根据需要滚动或浏览其中的数据。游标必须在声明处理程序之前被声明，并且变量和条件还必须在声明游标或处理程序之前被声明。

声明游标

MySQL中使用DECLARE关键字来声明游标，其语法的基本形式如下：

DECLARE cursor_name CURSOR FOR select_statement

1	DECLARE cursor_name CURSOR FOR select_statement

其中，cursor_name参数表示游标的名称；select_statement参数表示SELECT语句的内容，返回一个用于创建游标的结果集。

声明名称为cursor_first的游标，代码如下：

DECLARE cursor_first CURSOR FOR select * from testdb.info;

1	DECLARE cursor_first CURSOR FOR select * from testdb.info;

打开游标

OPEN cursor_first;

1	OPEN cursor_first;

读取游标

FETCH cursor_name INTO var_name [,var_name]…{参数名称}

1	FETCH cursor_name INTO var_name [,var_name]…{参数名称}

这个语句用指定的打开游标读取下一行，并且前进游标指针。其中，cursor_name参数表示游标的名称；var_name参数表示将游标中的SELECT语句查询出来的信息存入该参数中，var_name必须在声明游标之前就定义好。

使用名称为cursor_first的游标，将查询出来的数据存入one_name和two_name这两个变量中，代码如下：

FETCH cursor_first INTO one_name,two_name;

1	FETCH cursor_first INTO one_name,two_name;

关闭游标

CLOSE cursor_name

1	CLOSE cursor_name

这个语句关闭先前打开的游标，如果未被明确地关闭，游标在它被声明的复合语句的末尾被关闭。

PS：MySQL中游标只能在存储过程和存储函数中使用。

示例1：把testdb.info中ID字段的值赋给游标mycursor，然后把mycursor读取到变量a上，最后使用LOOP循环显示a变量

delimiter $
CREATE PROCEDURE curdemo()
BEGIN
DECLARE a INT;
DECLARE mycursor CURSOR FOR SELECT ID FROM testdb.info;
OPEN mycursor;
LOOP
  FETCH mycursor INTO a;
  SELECT a;
END LOOP;
CLOSE mycursor;
END $
delimiter ;

delimiter $

CREATE PROCEDURE curdemo()

BEGIN

DECLARE a INT;

DECLARE mycursor CURSOR FOR SELECT ID FROM testdb.info;

OPEN mycursor;

LOOP

FETCH mycursor INTO a;

SELECT a;

END LOOP;

CLOSE mycursor;

END $

delimiter ;

三、调用存储过程

存储过程是通过CALL语句进行调用的，语法如下：

CALL procedure_name([parameter])

1	CALL procedure_name([parameter])

CALL语句调用一个先前用CREATE PROCEDURE创建的存储过程，其中procedure_name为存储过程的名称，[parameter]为存储过程的参数，可选。下面顶一个countproc的存储过程，然后调用这个存储过程，代码执行如下：

delimiter $
create procedure countproc(IN sid INT,OUT num INT)
       begin
         select count(*) into num from testdb.info where salary > 5000;
       end$
delimiter ;

delimiter $

create procedure countproc(IN sid INT,OUT num INT)

begin

select count(*) into num from testdb.info where salary > 5000;

end$

delimiter ;

调用存储过程

mysql> call countproc(5000,@num);

1	mysql> call countproc(5000,@num);

查看返回结果

mysql> select @num\G
*************************** 1. row ***************************
@num: 3

mysql> select @num\G

*************************** 1. row ***************************

@num: 3

该存储过程返回了info表中工资高于5000的人数，把人数返回值存储在num变量中，使用SELECT查看，返回结果为3.

四、查看存储过程和存储函数

SHOW STATUS语句可以查看存储过程和函数的状态，其基本语法结构如下：

SHOW {PROCEDURE | FUNCTION} STATUS [LIKE ‘pattern’]

1	SHOW {PROCEDURE \| FUNCTION} STATUS [LIKE ‘pattern’]

这个语句是一个MySQL的扩展，它返回子程序的特征，如数据库、名字、类型、创建者及创建和修改日期。如果没有指定样式，根据使用的语句，所有存储程序或存储函数的信息都被列出PROCEDURE和FUNCTION分别表示查看存储过程和函数；LIKE语句表示匹配存储过程或函数的名称。代码执行如下：

mysql> SHOW PROCEDURE STATUS LIKE 'var%'\G
*************************** 1. row ***************************
Db: testdb
Name: var
Type: PROCEDURE
Definer: root@localhost
Modified: 2015-11-17 22:40:58
Created: 2015-11-17 22:40:58
Security_type: DEFINER
Comment:
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci

mysql> SHOW PROCEDURE STATUS LIKE 'var%'\G

*************************** 1. row ***************************

Db: testdb

Name: var

Type: PROCEDURE

Definer: root@localhost

Modified: 2015-11-17 22:40:58

Created: 2015-11-17 22:40:58

Security_type: DEFINER

Comment:

character_set_client: utf8

collation_connection: utf8_general_ci

Database Collation: utf8_general_ci

除了SHOW STATUS之外，MySQL还可以使用SHOW CREATE语句查看存储过程和函数的状态。

SHOW CREATE {PROCEDURE | FUNCTION} name

1	SHOW CREATE {PROCEDURE \| FUNCTION} name

这个语句时一个MySQL的扩展，类似于SHOW CREATE TABLE，它返回一个可用来重新创建已命名子程序的确切字符串。PROCEDURE和FUNCTION分别表示查看存储过程和函数；LIKE语句表示匹配存储过程或函数的名称。SHOW CREATE语句示例，代码如下：

mysql> show create procedure var \G
*************************** 1. row ***************************
Procedure: var
sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `var`()
begin
DECLARE var INT;
select count(*) into var from testdb.info;
end
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci

mysql> show create procedure var \G

*************************** 1. row ***************************

Procedure: var

sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `var`()

begin

DECLARE var INT;

select count(*) into var from testdb.info;

end

character_set_client: utf8

collation_connection: utf8_general_ci

Database Collation: utf8_general_ci

MySQL中存储过程和函数的信息存储在information_schema数据库下的Routines表中。可以通过查询该表的记录来查询存储过程和函数的信息。其基本语句形式如下：

select * from information_schema.Routines where ROUTINE_NAME='sp_name'\G

1	select * from information_schema.Routines where ROUTINE_NAME='sp_name'\G

其中，ROUTINE_NAME字段中存储的是存储过程和函数的名称；sp_name参数表示存储过程或函数的名称。从Routines表中查询名称为var的存储函数的信息，代码如下：

mysql> select * from information_schema.Routines where ROUTINE_NAME='var' AND ROUTINE_TYPE='PROCEDURE'\G
*************************** 1. row ***************************
SPECIFIC_NAME: var
ROUTINE_CATALOG: def
ROUTINE_SCHEMA: testdb
ROUTINE_NAME: var
ROUTINE_TYPE: PROCEDURE
DATA_TYPE:
CHARACTER_MAXIMUM_LENGTH: NULL
CHARACTER_OCTET_LENGTH: NULL
NUMERIC_PRECISION: NULL
NUMERIC_SCALE: NULL
DATETIME_PRECISION: NULL
CHARACTER_SET_NAME: NULL
COLLATION_NAME: NULL
DTD_IDENTIFIER: NULL
ROUTINE_BODY: SQL
ROUTINE_DEFINITION: begin
DECLARE var INT;
select count(*) into var from testdb.info;
end
EXTERNAL_NAME: NULL
EXTERNAL_LANGUAGE: NULL
PARAMETER_STYLE: SQL
IS_DETERMINISTIC: NO
SQL_DATA_ACCESS: MODIFIES SQL DATA
SQL_PATH: NULL
SECURITY_TYPE: INVOKER
CREATED: 2015-11-17 22:40:58
LAST_ALTERED: 2015-11-18 16:42:48
SQL_MODE: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
ROUTINE_COMMENT:
DEFINER: root@localhost
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
DATABASE_COLLATION: utf8_general_ci

mysql> select * from information_schema.Routines where ROUTINE_NAME='var' AND ROUTINE_TYPE='PROCEDURE'\G

*************************** 1. row ***************************

SPECIFIC_NAME: var

ROUTINE_CATALOG: def

ROUTINE_SCHEMA: testdb

ROUTINE_NAME: var

ROUTINE_TYPE: PROCEDURE

DATA_TYPE:

CHARACTER_MAXIMUM_LENGTH: NULL

CHARACTER_OCTET_LENGTH: NULL

NUMERIC_PRECISION: NULL

NUMERIC_SCALE: NULL

DATETIME_PRECISION: NULL

CHARACTER_SET_NAME: NULL

COLLATION_NAME: NULL

DTD_IDENTIFIER: NULL

ROUTINE_BODY: SQL

ROUTINE_DEFINITION: begin

DECLARE var INT;

select count(*) into var from testdb.info;

end

EXTERNAL_NAME: NULL

EXTERNAL_LANGUAGE: NULL

PARAMETER_STYLE: SQL

IS_DETERMINISTIC: NO

SQL_DATA_ACCESS: MODIFIES SQL DATA

SQL_PATH: NULL

SECURITY_TYPE: INVOKER

CREATED: 2015-11-17 22:40:58

LAST_ALTERED: 2015-11-18 16:42:48

SQL_MODE: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

ROUTINE_COMMENT:

DEFINER: root@localhost

CHARACTER_SET_CLIENT: utf8

COLLATION_CONNECTION: utf8_general_ci

DATABASE_COLLATION: utf8_general_ci

在information_schema数据库下的Routines表中，存储所有存储过程和函数的定义。使用SELECT语句查询Routines表中的存储过程和函数的定义时，一定要使用ROUTINE_NAME字段指定存储过程或函数的名称。否则，将查询出所有的存储过程或函数的定义。如果有存储过程和存储函数名称相同，则需要同时指定ROUTINE_TYPE字段表明查询的是哪种类型的存储程序。

五、修改存储过程和函数

使用ALTER语句可以修改存储过程或函数的特性，语法如下：

ALTER {PROCEDURE | FUNCTION} sp_name [characteristic…]

1	ALTER {PROCEDURE \| FUNCTION} sp_name [characteristic…]

其中，sp_name参数表示存储过程或函数的名称，characteristic参数指定存储函数的特性，可能的取值有：

CONTAINS SQL – 表示子程序包含SQL语句，但不包含读或写数据的语句。

NO SQL – 表示子程序中不包含SQL语句。

READS SQL DATA – 表示子程序中包含读数据的语句。

MODIFIES SQL DATA – 表示子程序中包含写数据的语句。

SQL SECURITY { DEFINER | INVOKER } – 指明谁有权限来执行。

DEFINER – 表示只有定义者自己才能够执行。

INVOKER – 表示调用者可以执行。

COMMENT ‘string’ – 表示注释信息。

下面修改存储过程

mysql> ALTER PROCEDURE countproc MODIFIES SQL DATA SQL SECURITY INVOKER

1	mysql> ALTER PROCEDURE countproc MODIFIES SQL DATA SQL SECURITY INVOKER

存储过程中的代码可以修改吗？MySQL目前还不支持对已存在的存储过程代码的更改。如果必须要更改存储过程，必须使用DROP语句删除之后，再重新写代码。

六、使用实例

不知道你的公司中是否有Json处理的需求，在MySQL 5.7虽然开始支持原生Json格式，但很多企业可能还是低于这个版本的。下面这个函数就是一个接受一个Json对象和Key，然后返回一个value的小程序。这样程序就可以在SQL语句中直接使用。

DELIMITER $$
CREATE FUNCTION GetJSONValue` (
    in_JSONObject VARCHAR(4096), #JSON数组字符串
    in_KeyName VARCHAR(64)       #键名
) RETURNS varchar(512) CHARSET utf8 READS SQL DATA 
BEGIN
    DECLARE vs_return VARCHAR(4096);
    DECLARE vs_JSONObject, vs_KeyName, vs_key VARCHAR(4096);
    DECLARE flag, pos_count, vi_last_right_pos, vi_last_left_pos, vi_left_count SMALLINT UNSIGNED;

    SET vs_JSONObject = TRIM(in_JSONObject);
    SET vs_KeyName = TRIM(in_KeyName);
    SET vs_key = vs_KeyName;

    IF vs_JSONObject = '' OR vs_JSONObject IS NULL  OR vs_KeyName = '' OR vs_KeyName IS NULL 
    THEN
        SET vs_return = NULL;
        RETURN(vs_return);
    END IF;


    SET flag = 1;
    WHILE flag > 0 DO
        IF LOCATE('.', vs_KeyName) > 0 THEN
            SET vs_key = SUBSTRING_INDEX(vs_KeyName, '.', 1);
            SET vs_KeyName = substring(vs_KeyName, length(vs_key) + 2, length(vs_KeyName));
        ELSE
            SET flag = 0;
        END IF;

        SET vs_JSONObject = substring(vs_JSONObject, 1, length(vs_JSONObject) - 1);
        IF LOCATE(CONCAT('"', vs_key, '":'), vs_JSONObject) = 0 THEN
            SET vs_return = '';
        ELSE
            SET vs_JSONObject = SUBSTRING_INDEX(vs_JSONObject, CONCAT('"', vs_key, '":'), -1);
            IF LOCATE('{', vs_JSONObject) != 1 THEN -- 是否不是{开头
                IF LOCATE('"', vs_JSONObject) != 1 THEN -- 是否不是双引号开头
                    SET vs_return = SUBSTRING_INDEX(vs_JSONObject, ',', 1);
                ELSE
                    SET vs_return = SUBSTRING_INDEX(vs_JSONObject, '"', 2);
                    SET vs_return = SUBSTRING_INDEX(vs_return, '"', -1);
                END IF;
            ELSE
                IF LOCATE('{', vs_JSONObject, 2) = 0 THEN
                    SET vs_return = substring(vs_JSONObject, 1, LOCATE('}', vs_JSONObject, 2));
                ELSE
                    SET vi_last_right_pos = LOCATE('}', vs_JSONObject, 2);
                    SET vi_last_left_pos = LOCATE('{', vs_JSONObject, 2);
                    IF vi_last_left_pos > vi_last_right_pos || vi_last_left_pos = 0 THEN
                        SET vs_return = substring(vs_JSONObject, 1, LOCATE('}', vs_JSONObject, 2));
                    ELSE
                        SET vi_left_count = 1;
                        SET pos_count = 1;
                        WHILE vi_left_count > 0 DO
                            IF vi_last_left_pos < vi_last_right_pos AND vi_last_left_pos > 0 THEN
                                SET vi_left_count = vi_left_count + 1;
                                SET pos_count = pos_count + 1;
                                SET vi_last_left_pos = LOCATE('{', vs_JSONObject, vi_last_left_pos + 1);
                            ELSE
                                SET vi_left_count = vi_left_count - 1;
                                SET vi_last_right_pos = LOCATE('}', vs_JSONObject, vi_last_right_pos + 1);
                            END IF;
                        END WHILE;
                        SET vs_return = concat(SUBSTRING_INDEX(vs_JSONObject, '}', pos_count), '}');
                    END IF;
                END IF;
            END IF;   
        END IF;
        SET vs_JSONObject = vs_return;
        SET vs_key = vs_KeyName;
    END WHILE;

    SET vs_return = vs_JSONObject;
    RETURN(vs_return);
END$$
DELIMITER ;

DELIMITER $$

CREATE FUNCTION GetJSONValue` (

in_JSONObject VARCHAR(4096), #JSON数组字符串

in_KeyName VARCHAR(64) #键名

) RETURNS varchar(512) CHARSET utf8 READS SQL DATA

BEGIN

DECLARE vs_return VARCHAR(4096);

DECLARE vs_JSONObject, vs_KeyName, vs_key VARCHAR(4096);

DECLARE flag, pos_count, vi_last_right_pos, vi_last_left_pos, vi_left_count SMALLINT UNSIGNED;

SET vs_JSONObject = TRIM(in_JSONObject);

SET vs_KeyName = TRIM(in_KeyName);

SET vs_key = vs_KeyName;

IF vs_JSONObject = '' OR vs_JSONObject IS NULL OR vs_KeyName = '' OR vs_KeyName IS NULL

THEN

SET vs_return = NULL;

RETURN(vs_return);

END IF;

SET flag = 1;

WHILE flag > 0 DO

IF LOCATE('.', vs_KeyName) > 0 THEN

SET vs_key = SUBSTRING_INDEX(vs_KeyName, '.', 1);

SET vs_KeyName = substring(vs_KeyName, length(vs_key) + 2, length(vs_KeyName));

ELSE

SET flag = 0;

END IF;

SET vs_JSONObject = substring(vs_JSONObject, 1, length(vs_JSONObject) - 1);

IF LOCATE(CONCAT('"', vs_key, '":'), vs_JSONObject) = 0 THEN

SET vs_return = '';

ELSE

SET vs_JSONObject = SUBSTRING_INDEX(vs_JSONObject, CONCAT('"', vs_key, '":'), -1);

IF LOCATE('{', vs_JSONObject) != 1 THEN -- 是否不是{开头

IF LOCATE('"', vs_JSONObject) != 1 THEN -- 是否不是双引号开头

SET vs_return = SUBSTRING_INDEX(vs_JSONObject, ',', 1);

ELSE

SET vs_return = SUBSTRING_INDEX(vs_JSONObject, '"', 2);

SET vs_return = SUBSTRING_INDEX(vs_return, '"', -1);

END IF;

ELSE

IF LOCATE('{', vs_JSONObject, 2) = 0 THEN

SET vs_return = substring(vs_JSONObject, 1, LOCATE('}', vs_JSONObject, 2));

ELSE

SET vi_last_right_pos = LOCATE('}', vs_JSONObject, 2);

SET vi_last_left_pos = LOCATE('{', vs_JSONObject, 2);

IF vi_last_left_pos > vi_last_right_pos || vi_last_left_pos = 0 THEN

SET vs_return = substring(vs_JSONObject, 1, LOCATE('}', vs_JSONObject, 2));

ELSE

SET vi_left_count = 1;

SET pos_count = 1;

WHILE vi_left_count > 0 DO

IF vi_last_left_pos < vi_last_right_pos AND vi_last_left_pos > 0 THEN

SET vi_left_count = vi_left_count + 1;

SET pos_count = pos_count + 1;

SET vi_last_left_pos = LOCATE('{', vs_JSONObject, vi_last_left_pos + 1);

ELSE

SET vi_left_count = vi_left_count - 1;

SET vi_last_right_pos = LOCATE('}', vs_JSONObject, vi_last_right_pos + 1);

END IF;

END WHILE;

SET vs_return = concat(SUBSTRING_INDEX(vs_JSONObject, '}', pos_count), '}');

END IF;

SET vs_JSONObject = vs_return;

SET vs_key = vs_KeyName;

END WHILE;

SET vs_return = vs_JSONObject;

RETURN(vs_return);

END$$

DELIMITER ;

使用方式如下：

 mysql> select GetJSONValue('{"code":"C02","weight":"2","sort":"3"}', 'code') as value;
+-------+
| value |
+-------+
| C02   |
+-------+
1 row in set (0.00 sec)

mysql> select GetJSONValue('{"code":"C02","weight":"2","sort":"3"}', 'code') as value;

+-------+

| value |

+-------+

| C02 |

+-------+

1 row in set (0.00 sec)

也支持多级嵌套：

mysql> select GetJSONValue('{"code":{"a":{"a1":"xxx","a2":{"a3":"xxx1"}, "b1":{"b2":"xxx"}},"b":{"b1":"xxx"}} , "code1":{"a":"xxx"} , "code2":"xxx"}',  'code.a') as value;        
+----------------------------------------------------+
| value                                              |
+----------------------------------------------------+
| {"a1":"xxx","a2":{"a3":"xxx1"}, "b1":{"b2":"xxx"}} |
+----------------------------------------------------+
1 row in set (0.00 sec)

mysql> select GetJSONValue('{"code":{"a":{"a1":"xxx","a2":{"a3":"xxx1"}, "b1":{"b2":"xxx"}},"b":{"b1":"xxx"}} , "code1":{"a":"xxx"} , "code2":"xxx"}', 'code.a') as value;