因为有个需要,需要迁移一个表中数据重组到另一张表中!单独走程序又不想麻烦,就想着省事,用mysql存储过程走了一遍,发现还有些小的问题,整理了下来,整个过程如下:
DROP PROCEDURE IF EXISTS `move_filter_word`;
CREATE DEFINER = `root`@`localhost` PROCEDURE `move_filter_word`()
BEGIN
SET @wordOrg = '';
SET @statusOrg = '';
SET @i = 0;
SET @perOff = 1000;
WHILE @i < 19720
#19720
DO
SET @selectOrg = CONCAT('SELECT `word`,`status` into @wordOrg, @statusOrg',
SET @i = @i+1;
PREPARE selectO FROM @selectOrg;
EXECUTE selectO;
IF @wordOrg != ''
THEN
SET @wordOrg = REPLACE(@wordOrg,"'","\\'");#文本里的单引号转义下
#SELECT @i,@wordOrg;
#msyql的语法很弱,只能一条条的输出处理了,整体来说省事但是不省时
SET @insertCopy = CONCAT('INSERT INTO lcd_filter_word ( `word`, `status`) VALUES (\'',@wordOrg,'\',
PREPARE insertC FROM @insertCopy;
EXECUTE insertC;
END IF;
END WHILE;
END;