三十八、MySQL 处理重复数据

文章永久连接：https://tech.souyunku.com/4933

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 |
+------+------+----------------------------+------------+
3 rows 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 |
+----+------+----------------------------+------------+
3 rows 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 |
+----+------+----------------------------+------------+
2 rows 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 |
+----+------+----------------------------+------------+
1 row 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);

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

干货推荐

本站推荐：精选优质专栏

附录：MySQL 教程：系列文章

• 一、MySQLMariaDB 基础教程
• 二、MySQL 简介
• 三、MySQL MariaDB 安装
• 四、MySQL 管理
• 五、MySQL 日常管理
• 六、MySQL PHP 语法
• 七、MySQL 创建连接
• 八、MySQL 获取数据库列表
• 九、MySQL 创建数据库
• 十、MySQL 删除数据库
• 十一、MySQL 选择数据库
• 十二、MySQL 数据类型
• 十三、MySQL 列出数据表
• 十四、MySQL 创建数据表
• 十五、MySQL 删除表
• 十六、MySQL 插入数据
• 十七、MySQL 获取插入数据的 ID
• 十八、MySQL SELECT FROM 查询数据
• 十九、MySQL WHERE 子句有条件的查询数据
• 二十、MySQL UPDATE 更新数据
• 二十一、MySQL DELETE FROM 语句删除数据
• 二十二、MySQL 返回删改查受影响的行数
• 二十三、MySQL LIKE 子句模糊查询数据
• 二十四、MySQL UNION 操作符查询多张表
• 二十五、MySQL ORDER BY 排序
• 二十六、MySQL GROUP BY 分组查询数据
• 二十七、MySQL JOIN 进行多表查询
• 二十八、MySQL NULL 值处理
• 二十九、MySQL REGEXP 子句正则表达式查询
• 三十、MySQL 数据库事务
• 三十一、MySQL ALTER 命令
• 三十二、MySQL 索引
• 三十三、CREATE TEMPORARY TABLE 创建临时表
• 三十四、MySQL DROP TABLE 删除临时表
• 三十五、MySQL INSERT INTO SELECT 复制表
• 三十六、MySQL 获取服务器元数据
• 三十七、MySQL 自增序列 AUTO_INCREMENT
• 【当前读到】三十八、MySQL 处理重复数据
• 三十九、MySQL 安全及防止 SQL 注入攻击
• 四十、MySQL 导出数据
• 四十一、MySQL 导入数据