MySQL concat函数：批量修改SQL脚本

由于需求和设计原因，整个人比较方，不过还是抽空整理一个获取批量修改SQL脚本的方法（ps:另一个同期上线项目引起的恐慌）；
线上反馈是这样的，客户那边导入的基础资料-部品番号（简称部番），需要做临时调整。
调整内容：当部番长度等于10，部番需要补充00补满12位，由于数据太多不方便人工核对做重复导入操作，所以就需要批量执行的SQL脚本；

这里推荐一种稳妥的方式（供参考）：

介绍一下MySQL函数CONCAT，煮个栗子：
　　CONCAT('A','B','C')意思就是把字符串ABC拼接起来，贼简单是不是，后面我们要做的就是把SQL写好、拆开、再堆起来，就可以收工了

新建表mytest，表字段id和code（部番）

/*
Navicat MySQL Data Transfer

Source Server         : 172.16.1.83 出货集货-local
Source Server Version : 50620
Source Host           : 172.16.1.83:3306
Source Database       : mytest

Target Server Type    : MYSQL
Target Server Version : 50620
File Encoding         : 65001

Date: 2021-08-24 19:31:05
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for mytest
-- ----------------------------
DROP TABLE IF EXISTS `mytest`;
CREATE TABLE `mytest` (
  `id` int(11) DEFAULT NULL,
  `code` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of mytest
-- ----------------------------
INSERT INTO `mytest` VALUES ('1', 'AAAAAaaaaa');

思路：

# 第一步：我要改的数据
SELECT * FROM `mytest` WHERE LENGTH(`code`) = 10;

# 第二步：改数据的话需要执行的SQL
UPDATE mytest SET `code` = 'AAAAAaaaaa00' WHERE id = 1 AND `code` = 'AAAAAaaaaa';

# 第三步：根据要改的数据条件，拼接SQL
SELECT CONCAT('UPDATE mytest SET `code` = \'',CONCAT(`code`,'00',''),CONCAT('\' WHERE id = ',id, CONCAT(' AND code = \'',`code`,'\';'))) updateSql FROM mytest WHERE LENGTH(`code`) = 10;

执行第三步获取SQL的SELECT语句，结果：

 

 一键执行：

# 第四步：复制查询结果（SQL批量更新脚本）并执行

 搞定！下班！请回答1988！

 

 补充一：根据主键ID替换，避免update语句覆盖执行

SELECT
    CONCAT(
        'UPDATE task SET managerNo =\'',
        CONCAT( managerNo, '', LPAD( @i := @i + 1, 4, 0 ) ),# 计数，并将数字格式化补零，重新生成自定义编号
        CONCAT( '\' WHERE type = 2 AND STATUS < 3 AND delMark = 0 AND managerNo = \'', managerNo, CONCAT( '\' AND id = \'', id, '\';' ) ) 
) 
FROM
    task,
    ( SELECT @i := 0 ) AS init 
WHERE
    type = 2 
    AND STATUS < 3 
    AND delMark = 0;

批量SQL：

UPDATE task SET managerNo ='20211210101308000001' WHERE type = 2 AND STATUS < 3 AND delMark = 0 AND managerNo = '2021121010130800' AND id = '1639102388983';
UPDATE task SET managerNo ='2021121406393400002' WHERE type = 2 AND STATUS < 3 AND delMark = 0 AND managerNo = '202112140639340' AND id = '1639435174910';
UPDATE task SET managerNo ='2021121406393400003' WHERE type = 2 AND STATUS < 3 AND delMark = 0 AND managerNo = '202112140639340' AND id = '1639435174927';
UPDATE task SET managerNo ='2021121406393400004' WHERE type = 2 AND STATUS < 3 AND delMark = 0 AND managerNo = '202112140639340' AND id = '1639435174949';
UPDATE task SET managerNo ='2021121406393400005' WHERE type = 2 AND STATUS < 3 AND delMark = 0 AND managerNo = '202112140639340' AND id = '1639435174966';
UPDATE task SET managerNo ='2021121406393500006' WHERE type = 2 AND STATUS < 3 AND delMark = 0 AND managerNo = '202112140639350' AND id = '1639435175046';
UPDATE task SET managerNo ='20211214064330000007' WHERE type = 2 AND STATUS < 3 AND delMark = 0 AND managerNo = '2021121406433000' AND id = '1639435410167';