MySQL 教程

38、MySQL 处理重复数据

MySQL 数据表中可能存在重复的记录，它们的差别可能只是自增 id 不一样

有时候我们允许重复数据的存在，但有时候也需要删除这些重复的数据

防止表中出现重复数据

可以在MySQL 数据表中设置指定的字段为 PRIMARY KEY（主键） 或者 UNIQUE（唯一） 索引来保证数据的唯一性

比如我们创建一个没有主键也没有唯一索引的表 tbl_language，那么该表就会允许出现多条重复记录

CREATE TABLE IF NOT EXISTS tbl_language(
id INT UNSIGNED,
name VARCHAR(64) NOT NULL,
url VARCHAR(128) NOT NULL,
founded_at DATE
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

然后我们就可以重复的插入 N 多条一样的数据

MariaDB [souyunku]> DROP TABLE tbl_language;
Query OK, 0 rows affected (0.01 sec)

MariaDB [souyunku]>` CREATE TABLE IF NOT EXISTS tbl_language(
->`    id INT UNSIGNED,
->`    name VARCHAR(64) NOT NULL,
->`    url VARCHAR(128) NOT NULL,
->`    founded_at DATE
-> )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.04 sec)

MariaDB [souyunku]>` INSERT INTO tbl_language (id,name,url,founded_at) VALUES (1,'Ruby','https://www.ruby-lang.org/','1996-12-25');
Query OK, 1 row affected (0.01 sec)

MariaDB [souyunku]>` INSERT INTO tbl_language (id,name,url,founded_at) VALUES (1,'Ruby','https://www.ruby-lang.org/','1996-12-25');
Query OK, 1 row affected (0.01 sec)

MariaDB [souyunku]>` INSERT INTO tbl_language (id,name,url,founded_at) VALUES (1,'Ruby','https://www.ruby-lang.org/','1996-12-25');
Query OK, 1 row affected (0.01 sec)

MariaDB [souyunku]> SELECT * FROM tbl_language;
+------+------+----------------------------+------------+
|id   | name | url                        | founded_at |
+------+------+----------------------------+------------+
| 1 | Ruby | https://www.ruby-lang.org/ | 1996-12-25 |
| 1 | Ruby | https://www.ruby-lang.org/ | 1996-12-25 |
| 1 | Ruby | https://www.ruby-lang.org/ | 1996-12-25 |
+------+------+----------------------------+------------+
3rows in set (0.00 sec)

如果我们创建表时设置字段 id 为主键或者唯一索引，那么尝试多次插入同一 id 就会报错了

CREATE TABLE IF NOT EXISTS tbl_language(
id INT UNSIGNED AUTO_INCREMENT,
name VARCHAR(64) NOT NULL,
url VARCHAR(128) NOT NULL,
founded_at DATE,
PRIMARY KEY ( id )
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

运行演示

MariaDB [souyunku]> DROP TABLE tbl_language;
Query OK, 0 rows affected (0.01 sec)

MariaDB [souyunku]>` CREATE TABLE IF NOT EXISTS tbl_language(
->`    id INT UNSIGNED AUTO_INCREMENT,
->`    name VARCHAR(64) NOT NULL,
->`    url VARCHAR(128) NOT NULL,
->`    founded_at DATE,
->`    PRIMARY KEY ( id )
-> )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.04 sec)

MariaDB [souyunku]>` INSERT INTO tbl_language (id,name,url,founded_at) VALUES (1,'Ruby','https://www.ruby-lang.org/','1996-12-25');
Query OK, 1 row affected (0.00 sec)

MariaDB [souyunku]>` INSERT INTO tbl_language (id,name,url,founded_at) VALUES (1,'Ruby','https://www.ruby-lang.org/','1996-12-25');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
MariaDB [souyunku]>` INSERT INTO tbl_language (id,name,url,founded_at) VALUES (2,'Ruby','https://www.ruby-lang.org/','1996-12-25');
Query OK, 1 row affected (0.01 sec)

MariaDB [souyunku]>` INSERT INTO tbl_language (id,name,url,founded_at) VALUES (3,'Ruby','https://www.ruby-lang.org/','1996-12-25');
Query OK, 1 row affected (0.01 sec)

MariaDB [souyunku]> SELECT * FROM tbl_language;
+----+------+----------------------------+------------+
|id | name | url                        | founded_at |
+----+------+----------------------------+------------+
|1 | Ruby | https://www.ruby-lang.org/ | 1996-12-25 |
|2 | Ruby | https://www.ruby-lang.org/ | 1996-12-25 |
|3 | Ruby | https://www.ruby-lang.org/ | 1996-12-25 |
+----+------+----------------------------+------------+
3rows in set (0.00 sec)

从上面可以看出，设置 id 为主键后，插入相同的 id 就会报错，提示 id 已经存在，但是，我们可以改一下 id 插入相同的数据

这有时候时可以允许的，但是有时候，这也可以看成是重复数据，因为这张表，我们要求的是 name 字段唯一，所以，需要对 name 字段添加唯一索引 (主键索引只有一个，所以已经不能加了)

CREATE TABLE IF NOT EXISTS tbl_language(
id INT UNSIGNED AUTO_INCREMENT,
name VARCHAR(64) NOT NULL,
url VARCHAR(128) NOT NULL,
founded_at DATE,
UNIQUE name (name(64)),
PRIMARY KEY ( id )
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

运行演示如下

MariaDB [souyunku]> DROP TABLE tbl_language;
Query OK, 0 rows affected (0.01 sec)

MariaDB [souyunku]>` CREATE TABLE IF NOT EXISTS tbl_language(
->`    id INT UNSIGNED AUTO_INCREMENT,
->`    name VARCHAR(64) NOT NULL,
->`    url VARCHAR(128) NOT NULL,
->`    founded_at DATE,
->`    UNIQUE name (name(64)),
->`    PRIMARY KEY ( id )
-> )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.04 sec)

MariaDB [souyunku]>` INSERT INTO tbl_language (id,name,url,founded_at) VALUES (1,'Ruby','https://www.ruby-lang.org/','1996-12-25');
Query OK, 1 row affected (0.01 sec)

MariaDB [souyunku]>` INSERT INTO tbl_language (id,name,url,founded_at) VALUES (1,'Ruby','https://www.ruby-lang.org/','1996-12-25');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
MariaDB [souyunku]>` INSERT INTO tbl_language (id,name,url,founded_at) VALUES (2,'Ruby','https://www.ruby-lang.org/','1996-12-25');
ERROR 1062 (23000): Duplicate entry 'Ruby' for key 'name'
MariaDB [souyunku]>` INSERT INTO tbl_language (id,name,url,founded_at) VALUES (2,'PHP','http://www.php.net/','1996-12-25');
Query OK, 1 row affected (0.01 sec)

MariaDB [souyunku]> SELECT * FROM tbl_language;
+----+------+----------------------------+------------+
|id | name | url                        | founded_at |
+----+------+----------------------------+------------+
|1 | Ruby | https://www.ruby-lang.org/ | 1996-12-25 |
|2 | PHP  | http://www.php.net/        | 1996-12-25 |
+----+------+----------------------------+------------+
2rows in set (0.00 sec)

给name 加上唯一所以后，终于也保证了 name 不会有重复的了，这才是我们想要的

INSERT IGNORE INTO

除了给关键字段添加主键或者唯一索引的方法来保证数据的唯一性外，还可以使用 INSERT IGNORE INTO 很少见，但是，它的确是有作用的，只是，在公司的项目中最好少用，因为其它少见，所以同事可能会迷茫

INSERT IGNORE INTO 与 INSERT INTO 的区别就是 INSERT IGNORE 会忽略数据库中已经存在的数据

如果数据库没有数据，就插入新的数据，如果有数据的话就跳过这条数据

这样就可以保留数据库中已经存在数据，达到在间隙中插入数据的目的

操作演示

下面的操作使用了 INSERT IGNORE INTO，执行后不会出错，也不会向数据表中插入重复数据

MariaDB [souyunku]> DROP TABLE tbl_language;
Query OK, 0 rows affected (0.01 sec)

MariaDB [souyunku]>` CREATE TABLE IF NOT EXISTS tbl_language(    id INT UNSIGNED AUTO_INCREMENT,    name VARCHAR(64) NOT NULL,    url VARCHAR(128) NOT NULL,    founded_at DATE,    UNIQUE name (name(64)),    PRIMARY KEY ( id ) )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.04 sec)

MariaDB [souyunku]>` INSERT INTO tbl_language (id,name,url,founded_at) VALUES (1,'Ruby','https://www.ruby-lang.org/','1996-12-25');
Query OK, 1 row affected (0.00 sec)

MariaDB [souyunku]>` INSERT IGNORE INTO tbl_language (id,name,url,founded_at) VALUES (1,'Ruby','https://www.ruby-lang.org/','1996-12-25');
Query OK, 0 rows affected, 1 warning (0.00 sec)

MariaDB [souyunku]> SELECT * FROM tbl_language;
+----+------+----------------------------+------------+
|id | name | url                        | founded_at |
+----+------+----------------------------+------------+
|1 | Ruby | https://www.ruby-lang.org/ | 1996-12-25 |
+----+------+----------------------------+------------+
1row in set (0.00 sec)

REPLACE INTO

设置了记录的主键或者唯一性后,

INSERT IGNORE INTO 插入数据时如果插入重复数据，将不返回错误，只以警告形式返回

REPLACE INTO 如果存在相同的记录，则先删除掉，再插入新记录

REPLACE INTO 很少见，但是，它的确是有作用的，只是，在公司的项目中最好少用，因为其它少见，所以同事可能会迷茫

统计重复数据

当表中存在重复数据时，可以使用 GROUP BY 子句对重复数据的关键字段做分组，然后使用 COUNT(*) 来统计重复了多少条

SELECT COUNT(*) as cnt, name FROM tbl_language GROUP BY name HAVING cnt > 1;

以上查询语句将返回 tbl_language 表中重复的记录数

一般情况下，查询重复的值，请执行以下操作

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

过滤重复数据

如果需要读取不重复的数据，可以在 SELECT 语句中使用 DISTINCT 关键字来过滤重复数据

SELECT DISTINCT name, url, founded_at FROM tbl_language;

也可以使用 GROUP BY 来读取数据表中不重复的数据，但一般不推荐这么做

SELECT name ,url, founded_at FROM tbl_language  GROUP BY name;

删除重复数据

如果想删除数据表中的重复数据，可以使用 GROUP BY 把唯一的数据导出到另一张表，然后在重命名表回去

导出数据到临时表

CREATE TABLE tmp SELECT * FROM tbl_language GROUP BY name;

删除原表

DROP TABLE tbl_language;

把临时表重名为 tbl_language

ALTER TABLE tmp RENAME TO tbl_language;

当然也可以在数据表中添加 UNIQUE（唯一索引）和 PRIMAY KEY（主键）来删除表中的重复记录

ALTER IGNORE TABLE tbl_language ADD PRIMARY KEY (name);

这种方法很危险，所以不要轻易在线上服务器尝试