我有一个表名"tmp1",它有字段名称id和逗号。逗号包含文本数据类型的1,3,2,4,5值。
我希望在存储过程的循环中有单独的值。比如1,然后3,然后2等等。所以,我在下面创建了给定的程序。
BEGIN
DECLARE my_delimiter CHAR(1);
DECLARE split_comma text;
DECLARE done INT;
DECLARE occurance INT;
DECLARE i INT;
DECLARE id INT;
DECLARE sel_query VARCHAR(500);
DECLARE splitter_cur CURSOR FOR SELECT id,comma from tmp1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
OPEN splitter_cur;
splitter_loop:LOOP
FETCH splitter_cur INTO id,split_comma;
SET occurance = LENGTH(split_comma) - LENGTH(REPLACE(split_comma,',',''))+1;
SET my_delimiter=',';
IF done = 1 THEN
LEAVE splitter_loop;
END IF;
IF occurance > 0 THEN
SET i = 1;
WHILE i <= occurance DO
SET sel_query = "SELECT SUBSTRING_INDEX(comma,',',i) as abc from tmp1";
SET @sel_query = sel_query;
PREPARE sel_query FROM @sel_query;
EXECUTE sel_query;
SET i = i + 1;
END WHILE;
END IF;
SET occurance = 0;
END LOOP;
CLOSE splitter_cur;
END;
但当我执行这个过程时,错误发生了"MySQL说:#1054-‘字段列表’中的未知列‘I’"
但这里我是一个用来旋转循环的变量。
有什么解决办法吗?请帮帮我。。。提前谢谢。。。
您必须在查询中嵌入变量i
的值,而您并没有这样做。
更改:
SET sel_query = "SELECT SUBSTRING_INDEX(comma,',',i) as abc from tmp1";
SET @sel_query = sel_query;
到:
SET @sel_query = CONCAT( "SELECT SUBSTRING_INDEX(comma,',',", i, ") as abc from tmp1 )";