简介
这是最近工作中遇到的一个业务场景，需要按字符将字符串拆分成多个子字符串，这就类似编程语言中的split函数。
详解
业务场景
需要从表中查询一个字段，这个字段是由多个或者一个字符串拼接而成，中间由,隔开。
所以需要拆分这个字段，然后依次用拆分后字符串做为条件从另一个表中查询一个字段，最后再用,拼接查询结果。
看一下示例：
select aa from table_a;
--先假设查询结果aa的一个为 111,222
select bb from table_b where bb2='111';
--假设查询结果bb为1212
select bb from table_b where bb2='222';
--假设查询结果bb为1313
而我最终要获取到得结果是 1212,1313SQL
SQL server实现
SQL server中有一个分割字符串的函数parsename，直接看实例如何使用。
select parsename('11.22',1);
select parsename('11.22',2);
select parsename('11.22',3);
select parsename('11,22',3);
--查询结果依次是
22
11
null
11,22SQL
这个函数会根据.拆分字符串，且会根据第二个参数返回指定的子字符串，1则为倒数第一个，2则为倒数第二个。
有了这个函数就可以像split函数一样获取到每个子字符串。再配合replace函数可以实现指定字符拆分。看实例：
select parsename(replace('11,22',',','.'),1);
select parsename(replace('11,22',',','.'),2);
--查询结果依次是
22
11SQL
通过创建函数来实现业务场景功能
代码语言：javascript复制CREATE FUNCTION dbo.fn_split_str(@in varchar(128))
RETURNS VARCHAR(128)
AS
BEGIN
DECLARE @out varchar(128)
DECLARE @num INT
SET @out = ''
SET @num = 1
set @in=replace(@in,',','.')
WHILE PARSENAME(@in, @num) IS NOT NULL
BEGIN
SELECT @out=@out column_name ',' FROM table_name WHERE code=PARSENAME(@in, @num)
set @num=@num 1
END
SET @out=SUBSTRING(@out,1,LEN(@out)-1)
RETURN @out
ENDSQL
注意替换table_name和column_name为自己的表名和列名。
MySQL实现
主要使用SUBSTRING_INDEX函数实现，直接看实例如何使用。
SELECT SUBSTRING_INDEX('11.22.33','.',1) UNION ALL
SELECT SUBSTRING_INDEX('11.22.33','.',2) UNION ALL
SELECT SUBSTRING_INDEX('11.22.33','.',3) UNION ALL
SELECT SUBSTRING_INDEX('11.22.33','.',4) UNION ALL
SELECT SUBSTRING_INDEX('11.22.33','.',-1);
--查询结果依次是
11
11.22
11.22.33
11.22.33
33SQL
与SQL server的PARSENAME有些差别，一可以指定分割的字符，二是正序返回结果，三是第三个参数代表返回前几个拆分的子字符串而不是第几个。
创建函数实现业务场景，配合SUBSTR截取字符串函数和INSTR返回字符在字符串的位置函数实现
DELIMITER $$
CREATE FUNCTION fn_split_str (input VARCHAR(128))
RETURNS VARCHAR(128)
DETERMINISTIC
NO SQL
BEGIN
DECLARE output VARCHAR(128);
SET output = '';
WHILE SUBSTRING_INDEX(input,',',1)!=0 DO
SELECT CONCAT(output, column_name, ',') INTO output FROM table_name WHERE mid = SUBSTRING_INDEX(input,',',1);
IF INSTR(input,',')!=0 THEN
SET input=SUBSTR(input,INSTR(input,',') 1);
ELSE
SET input='';
END IF;
END WHILE;
RETURN TRIM(TRAILING ',' FROM output);
END $$
DELIMITER ;SQL
注意替换table_name和column_name为自己的表名和列名。
定义函数首部位置的DELIMITER 是给MySQL解释器声明了一个结束符''，END
期间遇到了如下报错信息：
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)
解决办法：
这个警告是因为您的函数没有指定 DETERMINISTIC、NO SQL 或 READS SQL DATA 中的任何一个，而且二进制日志记录已启用。这意味着您的函数可能会对数据进行更改，但 MySQL 不知道它是否会更改数据，因此会发出警告。
为了解决这个问题，您可以在函数声明中添加 DETERMINISTIC、NO SQL 或 READS SQL DATA 中的一个或多个。这些关键字告诉 MySQL 函数的行为方式，从而使其能够更好地优化查询和避免不必要的警告。
如果您的函数不会更改数据，则可以将其声明为 NO SQL。如果您的函数会更改数据，则应该将其声明为 READS SQL DATA 或 MODIFIES SQL DATA，具体取决于函数的行为。
如果您不想看到这个警告，可以将 log_bin_trust_function_creators 变量设置为 1，这将允许您创建没有 DETERMINISTIC、NO SQL 或 READS SQL DATA 的函数，但这可能会降低 MySQL 的安全性。
