指定格式

1. 找到並查看上一章指定格式匯出的檔案 /tmp/animals1.txt。

   [root@kvm3 ~]# find /tmp/systemd-private-* -name animals1.txt 
   /tmp/systemd-private-5703fd24db4b4a06810bcbdcbf47c485-mariadb.service-3apdPV/tmp/animals1.txt
   

2. 複製成 animals.1。

   [root@kvm3 ~]# cp /tmp/systemd-private-5703fd24db4b4a06810bcbdcbf47c485-\
   mariadb.service-3apdPV/tmp/animals1.txt animals.1
   

3. 查看 animals.1 檔案紀錄格式，分隔符號為逗號，字串以雙引號括起來，換行符號 ^M。

   [root@kvm3 ~]# 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@kvm3 ~]# 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