Mysql旧数据处理常用sql模板·持续更新...

给旧数据新增字段初始化唯一值

通过sql给clo为空的数据，clo字段初始化为一个随机数，且要求唯一

#下述语句将会将clo字段为空的记录的clo字段值初始化为一个随机数。随机数以"APC"开头，后面跟随5位随机数字。(LPAD函数用于补齐随机数字的位数，确保都是5位数)
UPDATE your_table
SET clo= CONCAT(‘APC’, LPAD(FLOOR(RAND() * 100000), 5, ‘0’))
WHERE cloI NULL

将旧数据新增日期字段，初始化为某字段的值加一段时间(DATE_ADD函数)

举例：通过sql 将新增字段invalid_time(datetime)初始化为创建时间(datetime)+配置表的有效月份数（int）
思路：关联查出数据，再通过DATE_ADD()函数将计算后的时间赋值给invalid_time

UPDATE participation p
#这里participation_code代表的是两表的关联字段，若是id关联的就换id吧
JOIN participation_config pc ON pc.participation_code = p.participation_code
SET p.invalid_time = DATE_ADD(p.create_time, INTERVAL pc.effective_time MONTH);
where p.create_time is not null
and xxx(先小范围试一下)

检查脚本:

#初始化报名过期时间 检查脚本,查询为空即为全部成功，失败返回失败id
SELECT
    GROUP_CONCAT(ep.id) AS failed_ids
FROM participation p
         JOIN participation_config pc ON pc.participation_code = p.participation_code
WHERE p.invalid_time IS NULL
  AND pc.effective_time IS NOT NULL;

旧数据新增两code字段，

要求：

1. code值为 PC+年月日+创建序号(按创建顺序)
2. code值唯一，包括与其他行数据的 code1,code2字段都要唯一
3. 字段二比字段一大1

思路：

1. 给同天的数据加行号
2. 先给code1字段更新 为固定前缀+2倍行号
3. 再给code2字段更新 为固定前缀+(2倍行号+1)

#先给code1字段更新 为固定前缀+2倍行号
update your_table ec
         join (
    SELECT id,
           create_time,
           (ROW_NUMBER() OVER (PARTITION BY DATE(create_time) ORDER BY create_time) - 1) * 2 AS create_index
    FROM your_table
) as tmp on tmp.id = ec.id
set ec.code1=CONCAT('PC', DATE_FORMAT(ec.create_time, '%y%m%d'), tmp.create_index)
#先小规模测试
where ec.id=1;

UPDATE your_table ec
    JOIN (
        SELECT id,
               create_time,
               CAST((ROW_NUMBER() OVER (PARTITION BY DATE(create_time) ORDER BY create_time) - 1) * 2 AS SIGNED) AS create_index
        FROM your_table
    ) AS tmp ON tmp.id = ec.id
SET ec.code2 = CONCAT('LN', DATE_FORMAT(ec.create_time, '%y%m%d'), tmp.create_index + 1)
#先小规模测试
where ec.id=1;