next up previous contents
Next: 匯出 Raw Data Up: Export 匯出 Previous: Export 匯出   Contents   DYWANG_HOME

匯出到檔案

  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 - bjarnagin
    
  2. 使用 dyw 資料庫。
    mariadb root@localhost:(none)> use dyw;                                         
    You are now connected to database "dyw" as user "root"
    Time: 0.002s
    
  3. 使用語法 SELECT ... INTO OUTFILE 匯出資料到檔案。
    mariadb root@localhost:dyw> select * from animals into outfile '/tmp/animals.txt
                                ';                                                  
    Query OK, 4 rows affected
    Time: 0.002s
    
  4. 再執行一次,出現檔案已經存在的錯誤訊息,也就是要存入的檔案不能已存在,這是為了避免使用者製造嚴重的損壞。
    mariadb root@localhost:dyw> select * from animals into outfile '/tmp/animals.txt
                                ';                                                  
    (1086, "File '/tmp/animals.txt' already exists")
    
  5. 為了安全起見 php-fpm 服務將 into outfile 存檔 /tmp/animals.txt,實際存在路徑是 /tmp/systemd-private-xxxx-php-fpm.service-xxxx/tmp/animals.txt。
  6. 在路徑 /tmp/systemd-private-* 中找到 animals.txt。
    [root@kvm8 ~]# find /tmp/systemd-private-* -name animals.txt
    /tmp/systemd-private-5703fd24db4b4a06810bcbdcbf47c485-mariadb.service-3apdPV/tmp/animals.txt
    
  7. 將路徑 /tmp/systemd-private-* 中找到的 animals.txt,管線處理由 cat 顯示內容。
    [root@kvm8 ~]# find /tmp/systemd-private-* -name animals.txt | xargs cat -
    1	dog	4
    2	cat	4
    3	chicken	2
    15	bird	2
    
  8. 使用語法 SELECT ... INTO OUTFILE 並指定格式匯出資料到檔案。
    mariadb root@localhost:dyw> select * from animals into outfile '/tmp/animals1.tx
                                t' fields terminated by ',' enclosed by '"' lines te
                                rminated by '\r\n';                                 
    Query OK, 4 rows affected
    Time: 0.003s
    
  9. 再到終端機查看匯出的檔案 animals1.txt。
    [root@kvm8 ~]# find /tmp/systemd-private-* -name animals1.txt | xargs cat -
    "1","dog","4"
    "2","cat","4"
    "3","chicken","2"
    "15","bird","2"
    



De-Yu Wang 2020-04-07