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 目标表名 GROUP BY uuid having COUNT(uuid)>1 ORDER BY id ) as tmp )

4、删掉临时主键
ALTER table 目标表名 DROP column id;

5、添加主键
alter table `表名` add primary key(主键列名);

 

ps：

oracle处理重复数据：

--查询重复数据
SELECT cuid FROM eam_address GROUP BY cuid HAVING count(cuid)>1

SELECT count(*) FROM eam_address

---查询
SELECT * FROM eam_address e WHERE e.rowid IN (SELECT id FROM (SELECT min(rowid)AS id,cuid FROM eam_address GROUP BY cuid HAVING count(cuid)>1));

---删除重复数据中rowid最小的数据
DELETE FROM eam_address e WHERE e.rowid IN (SELECT id FROM (SELECT min(rowid)AS id,cuid FROM eam_address GROUP BY cuid HAVING count(cuid)>1));