有没有split 或是有什么办法可以取代split 呢?我要传入存储过程一个 (subject1,subject2,subject3,subject4......)
类似这样的一个串,然后在过程里面用(,)给分开有几个元素就分别去执行一个sql语句。INSERT INTO cm_sys_student(studentid,syscategoryid,enroldate)SELECT uid,id,SYSDATE()
FROM cm_sys_cocategory WHERE CATEGORYNUM = 红色区域,用","分开的每一个字符;
红色区域有几个值就执行几次sql语句.如何实现呼?
类似这样的一个串,然后在过程里面用(,)给分开有几个元素就分别去执行一个sql语句。INSERT INTO cm_sys_student(studentid,syscategoryid,enroldate)SELECT uid,id,SYSDATE()
FROM cm_sys_cocategory WHERE CATEGORYNUM = 红色区域,用","分开的每一个字符;
红色区域有几个值就执行几次sql语句.如何实现呼?
http://bbs.51cto.com/thread-484729-1.html
要把它按照逗号分割成:
1
2
44
5
666
29232
而且还要求它的总数。这个我以前写过。不过今天规范一下。1、具体函数
SQL code
DELIMITER $$CREATE DEFINER=`root`@`%` FUNCTION `func_get_split_string_total`(f_string varchar(1000),f_delimiter varchar(5)) RETURNS int(11)BEGIN
-- Get the total number of given string. return 1+(length(f_string) - length(replace(f_string,f_delimiter,'')));END$$DELIMITER ;
SQL code
DELIMITER $$CREATE DEFINER=`root`@`%` FUNCTION `func_get_split_string`(f_string varchar(1000),f_delimiter varchar(5),f_order int) RETURNS varchar(255) CHARSET utf8BEGIN
-- Get the separated number of given string. declare result varchar(255) default ''; set result = reverse(substring_index(reverse(substring_index(f_string,f_delimiter,f_order)),f_delimiter,1)); return result;END$$DELIMITER ;
测试的存储过程:
DELIMITER $$CREATE PROCEDURE `sp_print_result`(
IN f_string varchar(1000),IN f_delimiter varchar(5)
)
BEGIN
-- Get the separated string.
declare cnt int default 0;
declare i int default 0;
set cnt = func_get_split_string_total(f_string,f_delimiter);
drop table if exists tmp_print;
create temporary table tmp_print (num int not null);
while i < cnt
do
set i = i + 1;
insert into tmp_print(num) values (func_get_split_string(f_string,f_delimiter,i));
end while;
select * from tmp_print;
END$$DELIMITER ;
2、来做一下测试
mysql> call sp_print_result('1,2,44,5,666,29232',',');以上是二楼给的! 不错! 参考一下!
+-------+
| num |
+-------+
| 1 |
| 2 |
| 44 |
| 5 |
| 666 |
| 29232 |
+-------+
6 rows in set (0.01 sec)Query OK, 0 rows affected (0.01 sec)