重複匯入紀錄

1. 刪除 Primary key 或移除 auto_increment 特徵，兩者都無法單獨刪除或變更，必須於刪除 primary key 時，同時變更 id 的欄位特徵。

   mariadb root@localhost:dyw> alter table animals drop primary key, change id id i
                               nt(10) unsigned;                                    
   You're about to run a destructive command.
   Do you want to proceed? (y/n): y
   Your call!
   Query OK, 4 rows affected
   Time: 0.249s
   

2. 再查看資料表 animals 的欄位 id 設定，沒有 key 且沒有 auto_increment 特徵。

   mariadb root@localhost:dyw> desc animals;                                       
   +-------+---------------------+------+-----+---------+-------+
   | Field | Type                | Null | Key | Default | Extra |
   +-------+---------------------+------+-----+---------+-------+
   | id    | int(10) unsigned    | YES  |     | <null>  |       |
   | name  | varchar(20)         | NO   |     | <null>  |       |
   | foot  | tinyint(3) unsigned | NO   |     | <null>  |       |
   +-------+---------------------+------+-----+---------+-------+
   
   3 rows in set
   Time: 0.026s
   mariadb root@localhost:dyw>
   

3. 再用 mysqlimport 指令，將檔案 animals.txt 的紀錄匯入到資料庫 dyw，回應有 4 筆紀錄。 --local 是從 client 讀取檔案。

   [root@kvm3 ~]# mysqlimport -uroot -p123qwe --local dyw animals.txt 
   dyw.animals: Records: 4  Deleted: 0  Skipped: 0  Warnings: 0
   

4. 登入 mariaDB 查看，資料表 animals 新增 4 筆重複紀錄。

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