MySQL存储过程删除包含目标字符串所有相关数据

需求目标：将t_class 表numbers 字段中含有 tom, tony, jack 的行记录都删除掉
切割字符串部分引自mysql 通过存储过程分割字符串为数组 

CREATE TABLE t_class(
	t_id INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
	t_name VARCHAR(50),
	t_number TEXT
);
INSERT INTO t_class(t_name, t_number) VALUES('English Class', '{"s1":"tom"}');
INSERT INTO t_class(t_name, t_number) VALUES('English Class', '{"s1":"jerry"}');
INSERT INTO t_class(t_name, t_number) VALUES('Maths Class', '{"s1":"tony"}');
INSERT INTO t_class(t_name, t_number) VALUES('Maths Class', '{"s1":"jack"}');
INSERT INTO t_class(t_name, t_number) VALUES('history Class', '{"s1":"tom"}');
INSERT INTO t_class(t_name, t_number) VALUES('history Class', '{"s1":"tony"}');
INSERT INTO t_class(t_name, t_number) VALUES('English Class', '{"s1":"damao"}');
INSERT INTO t_class(t_name, t_number) VALUES('English Class', '{"s1":"ermao"}');
INSERT INTO t_class(t_name, t_number) VALUES('English Class', '{"s1":"sanmao"}');
INSERT INTO t_class(t_name, t_number) VALUES('English Class', '{"s1":"simao"}');

SELECT * FROM t_class;

处理思路：①写一个存储过程用来处理单个的行记录，比如 把含有tom 的行找出来删掉。
                  ②下一步就是一步一步的遍历，直到含有jack的行也被删除，结束。 （这一块也写一个存储过程）
                  ③遍历的话，就涉及到分割字符串了（SUBSTRING_INDEX，REVERSE这一套）
代码如下：

-- 因为开启了bin_log，为了能够随意的创建函数，可以阅读https://www.cnblogs.com/flying607/p/5576584.html
SET GLOBAL log_bin_trust_function_creators = 1;

-- 获得所有以“某个符号“分割的字符串的个数
DELIMITER //
DROP FUNCTION IF EXISTS func_get_split_string_total //
CREATE FUNCTION func_get_split_string_total(f_string TEXT, f_delimiter VARCHAR(5)) RETURNS INT(11)
BEGIN
  -- Get the total number of given string.
  -- 被替换掉了几个字符，那么数组的长度就是 (替换掉的字符数 + 1)
  RETURN 1+(LENGTH(f_string) - LENGTH(REPLACE(f_string,f_delimiter,'')));
END //

DELIMITER ;

-- 按分割取出字符串
DELIMITER //
DROP FUNCTION IF EXISTS func_get_split_string //
CREATE FUNCTION func_get_split_string(f_string TEXT,f_delimiter VARCHAR(5),f_order INT) RETURNS VARCHAR(255) CHARSET utf8
BEGIN
  -- 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 ;

/*
    关于REVERSE和SUBSTRING_INDEX(str,delim,count)
    REVERSE: 反转字符串
    SUBSTRING_INDEX： 如果count是正数从左边数，得到第 count 个分隔符左边那一部分， count是负数的话就是从右边数，得到第count个分隔符右边的部分。
    正向截一次，反向截一次，正好得到第 count个字符串
*/

-- 删除一个的行记录
DELIMITER //
CREATE PROCEDURE dropSomeStatus(IN candidateId VARCHAR(20))
BEGIN
	DECLARE done BOOLEAN DEFAULT 0;
	DECLARE tmp_id INT DEFAULT 0;
    --  注意：这里完全可以 直接删除，我只是想用下游标（^-^） 
 	DECLARE t_index CURSOR FOR SELECT t.t_id FROM t_class t WHERE t.t_number LIKE CONCAT("%", candidateId, "%");
	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

	OPEN t_index;
	REPEAT
		FETCH t_index INTO tmp_id;
		IF done!=1 THEN
			delte FROM t_class  WHERE t_id = tmp_id;
		END IF;
	UNTIL done END REPEAT;
	CLOSE t_index;
END //
DELIMITER ;

/*
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
    02000 状态码意义，发生下述异常之一：
        ①SELECT INTO 语句或 INSERT 语句的子查询的结果为空表。
        ②在搜索的 UPDATE 或 DELETE 语句内标识的行数为零。
        ③在 FETCH 语句中引用的游标位置处于结果表最后一行之后。
        可以阅读 https://blog.csdn.net/cangyingaoyou/article/details/7402243
    这里我觉得用EXIT也行。
    fetch 游标名称 into 变量列表; 其中的变量列表数目要和上面select 的到结果集中列的数目一样。
*/

-- 输入需要分割的字符串和分隔符，循环调用dropSomeStatus，实现需求目标
DELIMITER //

CREATE PROCEDURE sp_print_result(IN f_string TEXT,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);
  WHILE i < cnt
  DO
    SET i = i + 1;
    -- 开始逐个筛选，删除
    CALL dropSomeStatus(func_get_split_string(f_string,f_delimiter,i));
  END WHILE;

END //

DELIMITER ;

CALL sp_print_result("tom,tony,jack", ",");
-- 最终结果如下图所示， tom,tony,jack相关数据都没有了。

-- 怎么开的怎么关回去
SET GLOBAL log_bin_trust_function_creators = 0;