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));