mysql管理主键的表_mysql处理库中大量缺失主键表（添加主键）方案

背景：

从单节点mysql库将数据迁移至mysql集群库中，因为原单节点mysql数据库大量表缺失主键，而导致导入mysql集群(msyql集群要求每张表必须有主键)报错。

----查询无主键的表

select

table_schema,

table_name

from information_schema.tables

where (table_schema,table_name) not in(

select distinct table_schema,table_name from information_schema.columns where COLUMN_KEY='PRI'

)

and table_schema in (

'目标数据库'

);

----添加主键

通过excel拼接sql，再批量执行。

----删除重复数据保留一条

(针对重复数据完全一致的情况，其他具体情况具体分析)

1、查询重复uuid的数据

select uuid from 目标表名 GROUP BY uuid having COUNT(uuid)>1

2、添加临时主键

alter table 目标表名 add column id int(11) PRIMARY KEY AUTO_INCREMENT;

3、删除重复数据保留一条

DELETE from 目标表名where id in (select id from (select max(id) as id,uuid from interface_record GROUP BY uuid having COUNT(uuid)>1 ORDER BY id ) as tmp )

4、删掉临时主键

ALTER table 目标表名 DROP column id;

5、添加主键

alter table `表名` add primary key(主键列名);