next up previous contents
Next: 指定欄位 Up: Import 匯入 Previous: Mysqlimport 匯入紀錄   Contents   DYWANG_HOME

重複匯入紀錄

  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@kvm8 ~]# 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
    



De-Yu Wang 2020-04-07