LOAD DATA

1. 登入 mariadb。

   [root@kvm3 ~]# mycli -uroot -p123qwe
   Connecting to socket /var/lib/mysql/mysql.sock, owned by user mysql
   MariaDB 10.11.11
   mycli 1.40.0
   Home: http://mycli.net
   Bug tracker: https://github.com/dbcli/mycli/issues
   Thanks to the contributor - Anonymous
   

2. 使用 dyw 資料庫。

   MariaDB root@(none):(none)> use dyw;
   You are now connected to database "dyw" as user "root"
   Time: 0.001s
   

3. 查看目前資料表 animals 紀錄有 4 筆。

   MariaDB root@(none):dyw> select * from animals;
   +----+---------+------+
   | id | name    | foot |
   +----+---------+------+
   | 1  | dog     | 4    |
   | 2  | cat     | 4    |
   | 3  | bird    | 2    |
   | 4  | chicken | 2    |
   +----+---------+------+
   
   4 rows in set
   Time: 0.048s
   

4. 將資料表 animals 匯出到檔案 /tmp/dump1.txt。

   MariaDB root@(none):dyw> select * from animals into outfile '/tmp/dump1.txt';
   Query OK, 4 rows affected
   Time: 0.004s
   

5. MariaDB 預設不能使用語法 LOAD DATA 讀取檔案資料，匯入資料到資料庫。若要啟用，必須設定 AllowLoadLocalInfile=true。

   MariaDB root@(none):dyw> load data local infile '/tmp/dump1.txt' into table animals;
   (4166, 'The used command is not allowed because the MariaDB server or client has
   disabled the local infile capability')
   

6. 退出 mariadb。

   MariaDB root@(none):dyw> quit;
   Goodbye!
   

7. 找到並顯示路徑 /tmp 中的 dump1.txt 內容。

   [root@kvm3 ~]# find /tmp -name dump1.txt -exec cat {} \;
   1	dog	4
   2	cat	4
   3	bird	2
   4	chicken	2