刪除重複紀錄

1. 查詢資料表 animals，其中 cat 重複兩筆。

   mariadb root@localhost:dyw> select * from animals;                              
   +----+---------+------+
   | id | name    | foot |
   +----+---------+------+
   | 1  | dog     | 4    |
   | 2  | cat     | 4    |
   | 3  | chicken | 2    |
   | 15 | bird    | 2    |
   | 16 | cat     | 4    |
   +----+---------+------+
   
   5 rows in set
   Time: 0.031s
   

2. 產生一個名為 tmp 的暫時資料表，其欄位格式來自資料表 animals。

   mariadb root@localhost:dyw> create table tmp like animals;                      
   Query OK, 0 rows affected
   Time: 0.053s
   

3. 由資料表 animals 取得 name,foot 不重複的紀錄，寫入 tmp 資料表。

   mariadb root@localhost:dyw> insert into tmp(id,name,foot) select id,name,foot fr
                               om animals group by name,foot;                      
   Query OK, 4 rows affected
   Time: 0.024s
   

4. 查看 tmp 資料表紀錄，name,foot 沒有重複的紀錄。

   mariadb root@localhost:dyw> select * from tmp;                                  
   +----+---------+------+
   | id | name    | foot |
   +----+---------+------+
   | 1  | dog     | 4    |
   | 2  | cat     | 4    |
   | 3  | chicken | 2    |
   | 15 | bird    | 2    |
   +----+---------+------+
   
   4 rows in set
   Time: 0.024s
   

5. 查看 tmp 資料表結構，與 animals 一樣。

   mariadb root@localhost:dyw> show create table tmp\G                             
   ***************************[ 1. row ]***************************
   Table        | tmp
   Create Table | CREATE TABLE `tmp` (
     `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
     `name` varchar(20) NOT NULL,
     `foot` tinyint(3) unsigned NOT NULL,
     PRIMARY KEY (`id`)
   ) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8
   
   1 row in set
   Time: 0.018s
   

6. 刪除原始資料表 animals。

   mariadb root@localhost:dyw> drop table animals;                                 
   You're about to run a destructive command.
   Do you want to proceed? (y/n): y
   Your call!
   Query OK, 0 rows affected
   Time: 0.014s
   

7. 重新名命資料表 tmp 為 animals。

   mariadb root@localhost:dyw> alter table tmp rename to animals;                  
   You're about to run a destructive command.
   Do you want to proceed? (y/n): y
   Your call!
   Query OK, 0 rows affected
   Time: 0.011s
   

8. 查詢新的資料表 animals 已沒有重複紀錄。

   mariadb root@localhost:dyw> select * from animals;                              
   +----+---------+------+
   | id | name    | foot |
   +----+---------+------+
   | 1  | dog     | 4    |
   | 2  | cat     | 4    |
   | 3  | chicken | 2    |
   | 15 | bird    | 2    |
   +----+---------+------+
   
   4 rows in set
   Time: 0.026s