MySQL SQL001- 重复记录的处理

一、建立测试环境

1.1. 建立表格

CREATE TABLE books (  book_id INT AUTO_INCREMENT PRIMARY KEY,  title VARCHAR(50),  pub_year VARCHAR(4),  author VARCHAR(50));

1.2. 建立测试数据文件,books.txt，其内容如下

LOAD DATA INFILE 'c:/books.txt' INTO TABLE books FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';

1.4. 查询结果

?二、查询重复（只有重复）的记录

2.1 方法一

mysql> SELECT m.*        FROM books m ,           (SELECT MIN(book_id) book_id                            FROM books                        GROUP BY title                         HAVING count(1) > 1)n      WHERE m.book_id = n.book_id

?

?

三、删除重复的记录

3.1 方法一

DELETE books as m FROM books m ,         (SELECT title,MIN(book_id) book_id                          FROM books                      GROUP BY title                       HAVING count(1) > 1)n    WHERE m.book_id > n.book_id and m.title = n.title

?

3.2 方法二

-- 创建数据未重复的临时表 CREATE TEMPORARY TABLE tmp_books select * from books group by title having count(1) >= 1;-- 清除原有的数据资料 TRUNCATE TABLE books;-- 将临时表的数据插入正式表中 INSERT INTO books SELECT * FROM tmp_books;-- 删除临时表 DROP TABLE tmp_books

?

?

?

?