next up previous contents
Next: 還原資料表 Up: Import 匯入 Previous: 指定欄位   Contents   DYWANG_HOME

指定格式

  1. 找到並查看上一章指定格式匯出的檔案 /tmp/animals1.txt。
    [root@kvm8 ~]# find /tmp/systemd-private-* -name animals1.txt 
    /tmp/systemd-private-5703fd24db4b4a06810bcbdcbf47c485-mariadb.service-3apdPV/tmp/animals1.txt
    
  2. 複製成 animals.1。
    [root@kvm8 ~]# cp /tmp/systemd-private-5703fd24db4b4a06810bcbdcbf47c485-\
    mariadb.service-3apdPV/tmp/animals1.txt animals.1
    
  3. 查看 animals.1 檔案紀錄格式,分隔符號為逗號,字串以雙引號括起來,換行符號 ^M
    [root@kvm8 ~]# cat -EvT animals.1
    "1","dog","4"^M$
    "2","cat","4"^M$
    "3","chicken","2"^M$
    "15","bird","2"^M$
    
  4. 依照檔案 /tmp/animals1.txt 的格式匯入,欄位分隔符號為逗號,字串以雙引號括起來,換行符號 ^M,成功匯入 4 筆紀錄。
    [root@kvm8 ~]# mysqlimport -uroot -p123qwe --local \
    --fields-terminated-by=',' --fields-enclosed-by='"' \
    --lines-terminated-by='\r\n' dyw animals.1
    dyw.animals: Records: 4  Deleted: 0  Skipped: 0  Warnings: 0
    
  5. 再查看資料表 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    |
    | 4  | dog     | 1    |
    | 4  | cat     | 2    |
    | 2  | chicken | 3    |
    | 2  | bird    | 15   |
    | 1  | dog     | 4    |
    | 2  | cat     | 4    |
    | 3  | chicken | 2    |
    | 15 | bird    | 2    |
    +----+---------+------+
    16 rows in set
    Time: 0.031s
    



De-Yu Wang 2020-04-07