MySQL 数据库中删除重复记录的方法总结

演示数据

表结构:

mysql> desc demo;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
| site  | varchar(100)     | NO   | MUL |         |                |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

数据:

mysql> select * from demo order by id;
+----+------------------------+
| id | site                   |
+----+------------------------+
|  1 | http://www.CodeBit.cn  |
|  2 | http://YITU.org        |
|  3 | http://www.ShuoWen.org |
|  4 | http://www.CodeBit.cn  |
|  5 | http://www.ShuoWen.org |
+----+------------------------+
5 rows in set (0.00 sec)

当没有创建表或创建索引权限的时候,可以用下面的方法:

如果你要删除较旧的重复记录,可以使用下面的语句:

mysql> delete from a 
    -> using demo as a, demo as b
    -> where (a.id > b.id)
    -> and (a.site = b.site);
Query OK, 2 rows affected (0.12 sec)

mysql> select * from demo order by id;
+----+------------------------+
| id | site                   |
+----+------------------------+
|  1 | http://www.CodeBit.cn  |
|  2 | http://YITU.org        |
|  3 | http://www.ShuoWen.org |
+----+------------------------+
3 rows in set (0.00 sec)

如果你要删除较新的重复记录,可以使用下面的语句:

mysql> delete from a 
    -> using demo as a, demo as b
    -> where (a.id < b.id)
    -> and (a.site = b.site);
Query OK, 2 rows affected (0.12 sec)

mysql> select * from demo order by id;
+----+------------------------+
| id | site                   |
+----+------------------------+
|  2 | http://YITU.org        |
|  4 | http://www.CodeBit.cn  |
|  5 | http://www.ShuoWen.org |
+----+------------------------+
3 rows in set (0.00 sec)

你可以用下面的语句先确认将被删除的重复记录:

mysql> SELECT a.* 
    -> FROM demo a, demo b
    -> WHERE a.id > b.id
    -> AND (a.site = b.site);
+----+------------------------+
| id | site                   |
+----+------------------------+
|  1 | http://www.CodeBit.cn  |
|  3 | http://www.ShuoWen.org |
+----+------------------------+
2 rows in set (0.00 sec)

如果有创建索引的权限,可以用下面的方法:

在表上创建唯一键索引:

mysql> alter ignore table demo add unique index ukey (site);
Query OK, 5 rows affected (0.46 sec)
Records: 5  Duplicates: 2  Warnings: 0

mysql> select * from demo order by id;
+----+------------------------+
| id | site                   |
+----+------------------------+
|  1 | http://www.CodeBit.cn  |
|  2 | http://YITU.org        |
|  3 | http://www.ShuoWen.org |
+----+------------------------+
3 rows in set (0.00 sec)

重复记录被删除后,如果需要,可以删除索引:

mysql> alter table demo drop index ukey;
Query OK, 3 rows affected (0.37 sec)
Records: 3  Duplicates: 0  Warnings: 0

如果有创建表的权限,可以用下面的方法:

创建一个新表,然后将原表中不重复的数据插入新表:

mysql> create table demo_new as select * from demo group by site;
Query OK, 3 rows affected (0.19 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| demo           |
| demo_new       |
+----------------+
2 rows in set (0.00 sec)

mysql> select * from demo order by id;
+----+------------------------+
| id | site                   |
+----+------------------------+
|  1 | http://www.CodeBit.cn  |
|  2 | http://YITU.org        |
|  3 | http://www.ShuoWen.org |
|  4 | http://www.CodeBit.cn  |
|  5 | http://www.ShuoWen.org |
+----+------------------------+
5 rows in set (0.00 sec)

mysql> select * from demo_new order by id;
+----+------------------------+
| id | site                   |
+----+------------------------+
|  1 | http://www.CodeBit.cn  |
|  2 | http://YITU.org        |
|  3 | http://www.ShuoWen.org |
+----+------------------------+
3 rows in set (0.00 sec)

然后将原表备份,将新表重命名为当前表:

mysql> rename table demo to demo_old, demo_new to demo;
Query OK, 0 rows affected (0.04 sec)

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| demo           |
| demo_old       |
+----------------+
2 rows in set (0.00 sec)

mysql> select * from demo order by id;
+----+------------------------+
| id | site                   |
+----+------------------------+
|  1 | http://www.CodeBit.cn  |
|  2 | http://YITU.org        |
|  3 | http://www.ShuoWen.org |
+----+------------------------+
3 rows in set (0.00 sec)

注意:使用这种方式创建的表会丢失原表的索引信息!

mysql> desc demo;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| id    | int(11) unsigned | NO   |     | 0       |       |
| site  | varchar(100)     | NO   |     |         |       |
+-------+------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

如果要保持和原表信息一致,你可以使用 show create table demo; 来查看原表的创建语句,然后使用原表的创建语句创建新表,接着使用 insert … select 语句插入数据,再重命名表即可。

当然,如果要避免重复记录,最好的办法还是不要插入重复数据,可以参考本站另外一篇文章:MySQL 当记录不存在时插入(insert if not exists)

MySQL 中将一个表的数据插入另外一个表的方法

开发中,我们经常需要将一个表的数据插入到另外一个表,有时还需要指定导入字段,虽然这个实现起来非常简单,但是还是会困扰许多新手,因此专门发一篇文章备查。

如果2张表的字段一致,并且希望插入全部数据,可以用这种方法:

 
INSERT INTO 目标表 SELECT  * FROM 来源表;

比如要将 articles 表插入到 newArticles 表中,则是:

 
INSERT INTO newArticles SELECT  * FROM articles;

如果只希望导入指定字段,可以用这种方法:

 
INSERT INTO 目标表 (字段1, 字段2, ...) SELECT  字段1, 字段2, ...  FROM 来源表;

注意字段的顺序必须一致。

如果您需要只导入目标表中不存在的记录,可以参考另外一篇文章

MySQL 当记录不存在时插入(insert if not exists)

批量替换 MySQL 指定字段中的字符串

批量替换 MySQL 指定字段中的字符串是数据库应用中很常见的需求,但是有很多初学者在遇到这种需求时,通常都是用脚本来实现;其实,MySQL 内置的有批量替换语法,效率也会高很多;想了解具体方法,继续阅读本文吧 :)

批量替换的具体语法是:

 
UPDATE 表名 SET
指定字段 = replace(指定字段, ’要替换的字符串’, ’想要的字符串’) 
WHERE 条件;

如果你想把 article 表中 ID 小于5000的记录,content 字段中“解决”替换成“解放”,那么语法就是:

 
UPDATE article SET
content = replace(content, ’解决’, ’解放’) 
WHERE ID<5000;

是不是很方便 :)

MySQL 常见乱码问题及其解决办法系列文章

常见容易导致页面乱码的几个情况说明

如果你遇到乱码问题,可以从下面几个问题逐步检查:

(1)检查你的文件存储编码是否和 meta 声明的一致,假如你的文件是按照 utf-8 编码存储,但是 meta 却声明为 gb2312 ,将导致乱码。

解决办法:将页面存储编码和 meta 声明保持一致。用记事本打开文件,“另存为”,下面有个选项是“编码”,选择和 meta 声明一致的编码方式。(提示:meta 的编码声明要放在 title 标签前面,否则容易导致页面空白。)

(2)检查你的数据库连接有没有设置连接方式(SET NAMES),MySQL 4.1 版本以后,如果你没有设置数据库连接方式,那么插入或者查询将出现乱码情况。

解决办法:在你的数据库连接后面(通常是在 mysql_connect 后面,执行任何查询之前)加上一句 :

 
mysql_query("SET NAMES utf8");

其中 utf8 需要根据你需要设置。

(3)检查你的页面编码、meta 声明、数据库连接方式(SET NAMES)是否一致,如果你的页面和meta设置为gb2312,但是却在程序中设置 SET NAMES utf8,同样会导致乱码。原因是如果页面设置为 gb2312 ,那么表单中的数据也是按照 gb2312 提交,但是程序却要求按照 utf8 去处理,结果自然是乱码。

解决办法:将页面编码、meta 声明、数据库连接方式(SET NAMES)保持一致,当然这里说的一致不是绝对的,如 MySQL 的 utf8 和页面中的 utf-8 是不一样的,具体要根据你的实际情况设置。

不同字符集数据表转换出现乱码的解决办法

前段时间帮客户将 phpbb 2 迁移到 Discuz! 5,客户要求论坛数据不能有太大损失,本来已经找到了一个转换程序,可以将原有数据迁移到新的数据表中,结果转换后发现页面上全是乱码。仔细研究发现:phpbb 安装是按照默认的字符编码 latin1 ,而 Discuz! 用的是 gbk ,也就是标题说的“不同字符集数据表转换出现乱码”。

解决方法是转换数据编码方式:

第一步:将数据导出成期望的字符编码:

 
mysqldump -u用户名 -p密码 --default-character-set=原字符集 --set-charset=期望字符集 --skip-opt 数据库名 > 导出的文件名.sql

在上文提到的例子中,用下面的命令:

 
mysqldump -uroot -p*** --default-character-set=latin1 --set-charset=gbk --skip-opt phpbb> phpbb_gbk.sql

第二步:建立一个期望字符集的临时数据库:

 
CREATE DATABASE `phpbb_gbk` DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;

注意:这个是上文提到的例子中所用的命令,具体到实际应用时,要根据需要设置。

第三步:将第一步备份的数据导入到这个新创建的临时数据库里面:

 
mysql -u用户名 -p密码 --default-character-set=期望字符集 -f 数据库名<第一步备份的文件名.sql

在我的例子中是用下面的命令:

 
mysql -uroot -p*** --default-character-set=gbk -f phpbb_gbk<phpbb_gbk.sql

然后就可以在这两个数据库之间执行转换程序了。

MSSQL 如何实现 MySQL 的 limit 查询方式

不知为何,MSSQL 中没有 limit 这个极为重要的查询方式,熟悉 MySQL 的朋友都知道,MySQL 的 limit 对于实现分页和一些限制结果集的应用中非常方便。没有不要紧,我们可以用其他方法达到同样的目的,自己动手,丰衣足食!

语法:

 
SELECT * FROM 
(
	SELECT TOP [每页记录] * FROM 
	(
		SELECT TOP [每页记录*当前页数] [字段1, 字段2, ...] 
		FROM [数据表]	
		ORDER BY [排序字段] DESC
	)
	[表别名1] 
	ORDER BY [排序字段] 
)
[表别名2] 
ORDER BY [排序字段] DESC

示例:

 
SELECT * FROM 
(
	SELECT TOP 5 * FROM 
	(
		SELECT TOP 10 * 
		FROM table 
		ORDER BY id DESC
	) t1 
	ORDER BY id
) t2 
ORDER BY id DESC

上面的代码表示从 table 表中获取记录,每页5条记录,当前是第二页5*2=10,结果按照 id 倒序排列。

MySQL 查询重复出现次数最多的记录

在有些应用里面,我们需要查询重复次数最多的一些记录,虽然这是一个很简单的查询语句,但是对许多初学者来说,仍然有些难度,特发此文章备查。

 
SELECT keyword, count( * ) AS count
FROM article_keyword
GROUP BY keyword
ORDER BY count DESC
LIMIT 20

此段查询语句返回 article_keyword 表中 keyword 重复次数(count) 最多的20条记录。

 
SELECT DISTINCT count( * ) AS count
FROM article_keyword
GROUP BY keyword
ORDER BY count DESC
LIMIT 6

此段查询语句返回 article_keyword 表中 keyword 的重复次数(count) 排名前 6 的数值。通过添加 DISTINCT 返回唯一记录。

MySQL 当记录不存在时插入(insert if not exists)

在 MySQL 中,插入(insert)一条记录很简单,但是一些特殊应用,在插入记录前,需要检查这条记录是否已经存在,只有当记录不存在时才执行插入操作,本文介绍的就是这个问题的解决方案。

问题:我创建了一个表来存放客户信息,我知道可以用 insert 语句插入信息到表中,但是怎么样才能保证不会插入重复的记录呢?

答案:可以通过使用 EXISTS 条件句防止插入重复记录。

示例一:插入多条记录

假设有一个主键为 client_id 的 clients 表,可以使用下面的语句:

 
INSERT INTO clients
(client_id, client_name, client_type)
SELECT supplier_id, supplier_name, 'advertising'
FROM suppliers
WHERE not exists (select * from clients
where clients.client_id = suppliers.supplier_id);

示例一:插入单条记录

 
INSERT INTO clients
(client_id, client_name, client_type)
SELECT 10345, 'IBM', 'advertising'
FROM dual
WHERE not exists (select * from clients
where clients.client_id = 10345);

使用 dual 做表名可以让你在 select 语句后面直接跟上要插入字段的值,即使这些值还不存在当前表中。