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

LOAD DATA

  1. 登入 mariadb。
    [root@kvm8 ~]# mycli -S /var/lib/mysql/mysql.sock -uroot -p123qwe 
    mariadb 10.3.11-MariaDB
    mycli 1.20.1
    Chat: https://gitter.im/dbcli/mycli
    Mail: https://groups.google.com/forum/#!forum/mycli-users
    Home: http://mycli.net
    Thanks to the contributor - Anonymous
    
  2. 使用 dyw 資料庫。
    mariadb root@localhost:(none)> use dyw;                                         
    You are now connected to database "dyw" as user "root"
    Time: 0.001s
    
  3. 查看目前資料表 animals 紀錄有三筆。
    mariadb root@localhost:dyw> select * from animals;                              
    +----+---------+------+
    | id | name    | foot |
    +----+---------+------+
    | 1  | dog     | 4    |
    | 2  | cat     | 4    |
    | 3  | chicken | 2    |
    | 15 | bird    | 2    |
    +----+---------+------+
    
    4 rows in set
    Time: 0.023s
    
  4. 將資料表 animals 匯出到檔案 /tmp/dump1.txt。
    mariadb root@localhost:dyw> select * from animals into outfile '/tmp/dump1.txt'; 
    Query OK, 4 rows affected
    Time: 0.006s
    
  5. 找到並顯示路徑 /tmp/systemd-private-* 中的 dump1.txt 內容。
    [root@kvm8 ~]# find /tmp/systemd-private-* -name dump1.txt | xargs cat -
    1	dog	4
    2	cat	4
    3	chicken	2
    15	bird	2
    
  6. MariaDB 預設不能使用語法 LOAD DATA 讀取檔案資料,匯入資料到資料庫。若要啟用,必須設定 AllowLoadLocalInfile=true。
    mariadb root@localhost:dyw> load data local infile '/tmp/dump1.txt' into table an
                                imals;                                              
    (1148, 'The used command is not allowed with this MariaDB version')
    



De-Yu Wang 2020-04-07