公司项目有特殊的要求,需求根据一批指定数据去修改一大批数据,同库不同表 多个数据表需要同时修改
这里使用的技术点是 存储过程,WHILE循环,if判断,update等语句,以下是模拟版 场景基本可以适应,根据做自己的情况来改就可以了
DELIMITER $$
DROP PROCEDURE IF EXISTS `array`$$
-- 创建过程
CREATE PROCEDURE `array`(array_content varchar(100),array_content2 varchar(100),array_content3 varchar(100))
BEGIN
-- 内部创建数组
-- SET @array_content="www mysql com";
SET @i=1;
SET @count=CHAR_LENGTH(array_content)-CHAR_LENGTH(REPLACE(array_content,',','')) + 1;
select @count;
-- 得出数组成员总数
-- CREATE TABLE test.tmp(field1 VARCHAR(100));
-- 循环
WHILE @i <= @count
DO
-- select SUBSTRING_INDEX(SUBSTRING_INDEX(@array_content,' ',@i),' ',-1);
-- INSERT INTO test.tmp VALUES
-- (SUBSTRING_INDEX(SUBSTRING_INDEX(@array_content,' ',@i),' ',-1));
-- 依次插入每个成员
-- SET @su=SUBSTRING_INDEX(SUBSTRING_INDEX(@array_content,' ',@i),' ',-1);
-- 判断语句
IF (SUBSTRING_INDEX(SUBSTRING_INDEX(array_content,',',@i),',',-1) <> 'com') THEN
-- 修改指定
UPDATE book SET name = SUBSTRING_INDEX(SUBSTRING_INDEX(array_content,',',@i),',',-1) where bid= 14;
ELSE
UPDATE book SET name = "node" where bid= 13;
END IF;
SET @i=@i+1;
END WHILE;
END$$
DELIMITER ;
-- 调用存储
call `array`('payload,backresult,dataset','payload,backresult,dataset','payload,backresult,dataset');
案例
DELIMITER $$
DROP PROCEDURE IF EXISTS `array`$$
CREATE PROCEDURE `array`()
BEGIN
SET @array_content="www|mysql|com";
SET @array_content2="www|mysql|com";
SET @array_content3="www|mysql|com";
SET @i=1;
SET @count=CHAR_LENGTH(@array_content)-CHAR_LENGTH(REPLACE(@array_content,'|','')) + 1;
SET @count2=CHAR_LENGTH(@array_content2)-CHAR_LENGTH(REPLACE(@array_content2,'|','')) + 1;
SET @count3=CHAR_LENGTH(@array_content3)-CHAR_LENGTH(REPLACE(@array_content3,'|','')) + 1;
IF (@count = @count2 && @count3 = @count2) THEN
WHILE @i <= @count
DO
IF (SUBSTRING_INDEX(SUBSTRING_INDEX(@array_content,' ',@i),' ',-1) <> 'com') THEN
UPDATE book SET name = SUBSTRING_INDEX(SUBSTRING_INDEX(@array_content,' ',@i),' ',-1) where bid= 14;
ELSE
UPDATE book SET name = "node2" where bid= 13;
END IF;
SET @i=@i+1;
END WHILE;
ELSE
SELECT "参数错误";
END IF;
END$$
DELIMITER ;
call `array`();
有问题加群会马上帮你解决 278947305