2015年07月16日

因为有个需要，需要迁移一个表中数据重组到另一张表中！单独走程序又不想麻烦，就想着省事，用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',
         ' FROM lcd_filter_word_copy where id=',
         @i);

SET @i = @i+1;
PREPARE selectO FROM @selectOrg;
EXECUTE selectO;
IF @wordOrg != ''
THEN
 #SELECT @i,@wordOrg;

 #INSERT INTO TABLE ( `name`, `finger`, `status`, `createTime`) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1;
SET @wordOrg = REPLACE(@wordOrg,"'","\\'");#文本里的单引号转义下
#SELECT @i,@wordOrg;

#msyql的语法很弱，只能一条条的输出处理了，整体来说省事但是不省时
SET @insertCopy = CONCAT('INSERT INTO lcd_filter_word ( `word`, `status`) VALUES (\'',@wordOrg,'\',
         ',@statusOrg,') ON DUPLICATE KEY UPDATE `word`=VALUES(word),`status`=VALUES(status)');
PREPARE insertC FROM @insertCopy;
EXECUTE insertC;

END IF;
END WHILE;
END;