MySQL insert into select 主键冲突解决方案

场景

项目中，一张表里的基础数据，ID 是以 varchar 类型存入的，由于每年都会存在变动，在不改变历史数据的情况下，每年生产一份当年使用的数据，如果当年基础数据有变动，则改动当年的数据，往年历史数据不做变更，通过逻辑进行数据分隔。

方案

1、小批量数据

小批量数据，由于数据量少，通过 复制数据库中原数据生成 insert 脚本，使用 UUID 来处理了主键问题，如下：

INSERT INTO test_category (id, create_date, update_date, label, sort, type, value, year) VALUES (replace(uuid(),'-',''), now(), now(), '汽车', 1, 'category', 'automobile', '2021');

INSERT INTO test_category (id, create_date, update_date, label, sort, type, value, year) VALUES (replace(uuid(),'-',''), now(), now(), '手机', 2, 'category', 'phone', '2021');

...

因为数据量少，自己修改脚本也花不了多长时间，那么，如果数据量大，再这么写就不行了，需要处理成百上千，甚至更多的时候，要怎么做呢？

2、大批量数据

这里，我通过 UUID + 自增来实现，具体 SQL 如下：

INSERT INTO test_category (id, name, kind_id, sort, type, year)
select concat(left(replace(uuid(),'-',''),28),(@i:=@i+1)) as id, name, kind_id, sort, type,'2022' as year
from exhibits_dict_new edn
left join (select @i:=1000) as t on 1 = 1
where year = '2021';

        需要注意，这里关联了一张临时自增表，即：(select @i:=1000) as t  这里定义的 1000 为从 1000 开始，每一条记录，自增 1，为了避免与原 ID 出现重复现象，使用了 UUID 函数生成新记录，并截取后重新进行自增拼接，这样，就完美的解决了使用 insert into select 在同一张表里进行数据插入时的 主键冲突问题。

3、备份表常用 SQL

        

-- 创建一张表结构、索引信息一模一样的  空表
create table test_category_bak like test_category;

-- 往新备份的表中，插入需要备份的数据  全量备份，也可以在后面加上 where 条件进行条件备份
insert into test_category_bak select * from test_category;


-- 复制表，包含表中的数据
create table table_name2 as select * from table_name1;

-- 只复制表，不包含数据内容
create table table_name2 as select * from table_name1 where 1=2;