十八、MySQL处理重复数据

有些 MySQL 数据表中可能存在重复的记录，有些情况我们允许重复数据的存在，但有时候我们也需要删除这些重复的数据。

 

一、防止出现重复数据（此时表中没有重复数据）

1. 创建一个person表（双主键约束方式）

CREATE TABLE person
(
   first_name CHAR(20) NOT NULL,
   last_name CHAR(20) NOT NULL,
   sex CHAR(10),
   PRIMARY KEY (last_name, first_name)
);

2. 插入数据，通过IGNORE来避免数据重复

-- 首次插入
INSERT IGNORE INTO person (first_name, last_name) VALUES( '张', '三');
-- Affected rows: 1

-- 二次插入
INSERT IGNORE INTO person (first_name, last_name) VALUES( '张', '三');
-- Affected rows: 0

 

二、查询出现重复数据（此时表中已有重复数据），此时的person表不可能是双主键约束，假设没有主键

以上查询语句将返回 person表中重复的记录数。 一般情况下，查询重复的值，请执行以下操作：

步骤：

• 确定哪一列包含的值可能会重复。
• 在列选择列表使用COUNT(*)列出的那些列。
• 在GROUP BY子句中列出的列。
• HAVING子句设置重复数大于1。

SELECT first_name, last_name, COUNT(*) as repetitions FROM person 
GROUP BY first_name ,last_name 
HAVING repetitions > 1;

 结果：

 

 三、过滤重复数据（续二），如果你需要读取不重复的数据可以在 SELECT 语句中使用 DISTINCT 或者 GROUP BY关键字来过滤重复数据。

SELECT DISTINCT first_name,last_name FROM person;
--或者
SELECT first_name,last_name FROM person GROUP BY (first_name, last_name);

 

四、删除重复数据（此时表中已有重复数据）

 步骤：

1. 复制表结构及数据到tmp表

2. 删除person表（DROP）

3. 修改tmp表名称（ALTER TABLE ...RENAME TO...）

 --注意：GROUP BY后的三个属性外不能加括号，否则报错 Operand should contain 1 column(s)
CREATE TABLE tmp SELECT first_name, last_name, sex FROM person GROUP BY first_name, last_name, sex;

DROP TABLE person;
ALTER TABLE tmp RENAME TO person;