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，其内容如下

1,王国维词新释辑评,2006,叶嘉莹
2,王国维词新释辑评,2006,叶嘉莹
3,王国维词新释辑评,2006,叶嘉莹
4,南唐二主词新释辑评,2003,杨敏如
5,南唐二主词新释辑评,2003,杨敏如
6,朱自清说诗,1998,朱自清

1.3. 装入数据

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