存储函数与Locate和Substr方法模拟数组参数

存储函数

我们先来看一个存储函数（在创建和使用存储函数时，一定要先使用数据库！）：

-- 非常重要 要先选择数据库
use test;

delimiter //
create function bb() RETURNS int
begin
    declare pos int;
    declare cid int;
    declare mystr varchar(60) default "1,4,6";

    SET pos = locate(",", mystr);
    SET cid = substr(mystr, 1, pos);

    return cid;
end;
//
delimiter ;

delimiter

delimiter的作用：

默认情况下，mysql使用分号确定一个语句何时结束。但是在创建存储例程和存储函数的时候，需要编写多个语句（在begin和end中的语句需要使用分号进行分割）, 所以必须将分隔符修改为另外一个字符串。所以在语句的开头要加上 delimiter // 意思为：将mysql执行分隔符改为// 

但是也要谨记，在end之后，一定要恢复执行分隔符为分号！

-- 修改默认的执行分隔符为 //
delimiter //
-- begin代码块开始
begin
....code
-- begin结束
end; 
-- 使用修改的执行分隔符结束
//
-- 恢复执行分隔符为分号
delimiter ;

注意！如果不设置delimiter可能在定义变量的时候，会出现如下错误：

create function bb() RETURNS int
begin
    declare pos int;
   ...
end;

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 3

有多少个DECLARE就会报多少次，然后后面就是取不到变量的报错。这个原因是没有定义delimiter。

这时就需要修改默认的分隔符后，再进行定义，问题就会解决：

delimiter //
create function bb() RETURNS int
begin
    declare pos int;
   ...
end;
//
delimiter ;

创建存储函数

语法： create func_name (参数) returns type 如：

create function bb() RETURNS int
begin
 ......
 return xx;
end;

需要注意的是 返回是returns！

声明和设置变量declare

变量声明语法： declare var_name type [default value]

必须声明一种mysql支持的类型。声明这些变量，必须注意如下事项：

• 必须在begin/end代码块中
• 且要在块中执行语句的前面
• 变量的作用域限制在变量声明所在的代码块内

begin
  declare pos decimal(8, 2);
end;

如果需要设置变量的值，就需要使用SET：

begin
  declare pos decimal(8, 2);
  set pos = 2.34;
end;

特别提醒

如果将一个变量的类型设置为int，然后对其赋值一个字符串，那么最终得到的结果就是一个整型。比如像文章开头的例子那样：

declare cid int; // 变量类型为Int
SET cid = substr("1,4,5", 1, 2); // 截取字符串，得到1,
return cid; // 返回的值是整型，最终结果是1

如果变量赋值的类型与变量定义时的类型不匹配，那么mysql会自动对类型进行转换，转换为变量定义时的类型。

locate返回字符串在另一个字符串中的位置

语法：locate(str1, str2); 函数返回str1在str2中出现的位置。

• 若str2中包含str1，则locate(str1, str2) > 0
• 若str2不包含str1，则locate(str1, str2) = 0

select locate(',', '1,2,3,4');
// 最终得到2

mysql中的起始位置是从1开始的！请务必牢记！

substr截取字符串

语法：substr(str, pos, len) 从pos位开始，截取len个字符（len省略的话则从pos位置到字符串最后）

• str：被截取的字符串
• pos：起始位置，mysql中的起始位置从1开始，正数是从字符串左侧向右截取；负数则是从右侧向左侧截取；
• len：截取字符的个数/长度

select substr('2021-06-17', 6); 
// 从第6位开始截取到字符串末尾，结果06-17

select substr('2021-06-17', 6, 2);
// 从第6位开始截取2个字符（的长度） 结果06

对存储函数的解析

-- 定义类型为整型的两个变量
declare pos int;
declare cid int;

-- 找到1,4,6中首个逗号的位置 得到位置是2
SET pos = locate(",", "1,4,6");
-- 从第字符串一个位置开始，截取pos（2）个字符，得到1,
SET cid = substr("1,4,6", 1, pos);
-- 由于cid定义的是整形，而得到的1,是字符串类型，所以会自动对类型转化
-- 得到cid的结果就是1
return cid;

所以最终运行存储函数bb() 得到的结果就是 1

参考《PHP与MYSQL程序设计（第五版）》 - P381页

相关资料