mysql删除无主键表中重复记录(只保留一条记录)

最新推荐文章于 2022-11-10 17:06:48 发布

luffy5459

最新推荐文章于 2022-11-10 17:06:48 发布

阅读量3.9k

点赞数 1

CC 4.0 BY-SA版权

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.youkuaiyun.com/feinifi/article/details/52277492

mysql 专栏收录该内容

28 篇文章

订阅专栏

本文介绍了一种使用MySQL数据库去除重复数据并将其迁移至新表的方法，具体步骤包括创建新表存储唯一记录、清空原始表以及将新表数据重新写回原表。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

考虑多条语句变通的办法

mysql> select * from x_user;
+------+---------+-------------+
| id   | name    | mobile      |
+------+---------+-------------+
|    1 | yangfei | 15011186301 |
|    1 | yangfei | 15011186301 |
|    1 | yangfei | 15011186301 |
|    2 | majing  | 13720099441 |
|    2 | majing  | 13720099441 |
|    3 | yibao   | 15026418738 |
|    4 | feiy    | 13886604139 |
+------+---------+-------------+
7 rows in set (0.00 sec)

一、查询出不重复的记录存入新表

mysql> create table x_user2 as (select distinct id,name,mobile from x_
user);
Query OK, 4 rows affected (0.44 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from x_user2;
+------+---------+-------------+
| id   | name    | mobile      |
+------+---------+-------------+
|    1 | yangfei | 15011186301 |
|    2 | majing  | 13720099441 |
|    3 | yibao   | 15026418738 |
|    4 | feiy    | 13886604139 |
+------+---------+-------------+
4 rows in set (0.00 sec)

二、删除原表中所有数据

mysql> delete from x_user;
Query OK, 7 rows affected (0.12 sec)

三、把新表中的数据写回原表

mysql> select * from x_user;
Empty set (0.00 sec)

mysql> select * from x_user2;
+------+---------+-------------+
| id   | name    | mobile      |
+------+---------+-------------+
|    1 | yangfei | 15011186301 |
|    2 | majing  | 13720099441 |
|    3 | yibao   | 15026418738 |
|    4 | feiy    | 13886604139 |
+------+---------+-------------+
4 rows in set (0.00 sec)

mysql> insert into x_user select * from x_user2;
Query OK, 4 rows affected (0.04 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from x_user;
+------+---------+-------------+
| id   | name    | mobile      |
+------+---------+-------------+
|    1 | yangfei | 15011186301 |
|    2 | majing  | 13720099441 |
|    3 | yibao   | 15026418738 |
|    4 | feiy    | 13886604139 |
+------+---------+-------------+
4 rows in set (0.00 sec)