文章目錄

• 關于參數(shù) secure_file_priv
• 按默認參數(shù)設定導出表數(shù)據(jù)到文本文件中
• 自定義分隔符，將表數(shù)據(jù)導出到 txt 文件中
• 自定義分隔符，將表數(shù)據(jù)導出到 csv 文件中
• 導出數(shù)據(jù)時，提示“拒絕訪問”
• 總結
• • 確保 Linux 用戶 mysql 對導出的目標目錄擁有寫入和執(zhí)行兩種權限
  • 將數(shù)據(jù)導出到 /tmp 目錄下
  • 在導出數(shù)據(jù)時不指定目標文件的絕對路徑

操作系統(tǒng)的版本是：CentOS Linux release 8.2.2004 (Core)
數(shù)據(jù)庫的版本是：mysql Ver 14.14 Distrib 5.7.31, for Linux (x86_64) using EditLine wrapper

關于參數(shù) secure_file_priv

在文件 /etc/my.cnf 中，有一個參數(shù)叫 secure_file_priv，這個參數(shù)的主要目的就是限制 LOAD DATA INFILE 和 SELECT INTO OUTFILE語句的導入導出的目錄位置。登錄數(shù)據(jù)庫后，可以執(zhí)行下面的命令語句查看該參數(shù)的值：

mysql> select @@secure_file_priv; +--------------------+ | @@secure_file_priv | +--------------------+ | / | +--------------------+ 1 row in set (0.00 sec)

從結果可以看到參數(shù) secure_file_priv 的值是 /，表示可以在根目錄下，即可以在任何目錄下導入和導出，實際測試中發(fā)現(xiàn)可以在任何目錄導入數(shù)據(jù)，但是根本無法在任何目錄下導出數(shù)據(jù)，只能在目錄 /tmp 下導出數(shù)據(jù)，其它目錄則不行。

按默認參數(shù)設定導出表數(shù)據(jù)到文本文件中

數(shù)據(jù)表 student 存儲的數(shù)據(jù)如下：

mysql> select * from student; +----------------------+--------------+------+-------+----------+---------------------+ | id | name | age | score | birthday | insert_time | +----------------------+--------------+------+-------+----------+---------------------+ | 00000000000000000001 | liaowenxiong | 18 | NULL | NULL | 2021-09-25 10:40:51 | | 00000000000000000002 | liudehua | 28 | NULL | NULL | 2021-09-25 10:40:51 | | 00000000000000000003 | zhangxueyou | 38 | NULL | NULL | 2021-09-25 10:40:51 | +----------------------+--------------+------+-------+----------+---------------------+ 3 rows in set (0.00 sec)

將數(shù)據(jù)表 student 的數(shù)據(jù)導出到文件 /tmp/student.txt 中：

mysql> select * from student into outfile '/tmp/student.txt';

在命令終端中查看該文件的內(nèi)容如下：

[root@htlwk0001host ~]# cat /tmp/student.txt 00000000000000000001 liaowenxiong 18 \N \N 2021-09-25 10:40:51 00000000000000000002 liudehua 28 \N \N 2021-09-25 10:40:51 00000000000000000003 zhangxueyou 38 \N \N 2021-09-25 10:40:51

注：\N 表示空值。

使用圖形化編輯器打開文件 /tmp/student.txt 看下：

自定義分隔符，將表數(shù)據(jù)導出到 txt 文件中

用以下命令把 student 表的數(shù)據(jù)導出到 /tmp/student.txt：

mysql> select * from student into outfile '/tmp/student.txt' fields terminated by ',' optionally enclosed by '"' lines terminated by '\n'; Query OK, 3 rows affected (0.00 sec)

參數(shù)說明：

into outfile – 指定導出的目錄和文件名
fields terminated by – 指定字段間分隔符，即定義字段間的分隔符
optionally enclosed by – 指定字段包圍符，即定義包圍字段的字符，而參數(shù)optionally表示數(shù)值型字段無效，即數(shù)值類型的值不要加包圍符
lines terminated by – 指定行間分隔符，即定義每行的分隔符

使用命令查看文件 /tmp/student.txt 的內(nèi)容：

[root@htlwk0001host ~]# cat /tmp/student.txt; 00000000000000000001,"liaowenxiong",18,\N,\N,"2021-09-25 10:40:51" 00000000000000000002,"liudehua",28,\N,\N,"2021-09-25 10:40:51" 00000000000000000003,"zhangxueyou",38,\N,\N,"2021-09-25 10:40:51"

查看輸出結果可以看到，數(shù)據(jù)類型沒有添加雙引號，字符串和日期類型的數(shù)據(jù)有加雙引號。

使用圖形化編輯器打開文件 student.txt：

如果把參數(shù) optionally 去掉再導出數(shù)據(jù)：

mysql> select * from student into outfile '/tmp/student.txt' fields terminated by ',' enclosed by '"' lines terminated by '\n'; Query OK, 3 rows affected (0.00 sec)

你可以看到如下的導出結果：

[root@htlwk0001host ~]# cat /tmp/student.txt "00000000000000000001","liaowenxiong","18",\N,\N,"2021-09-25 10:40:51" "00000000000000000002","liudehua","28",\N,\N,"2021-09-25 10:40:51" "00000000000000000003","zhangxueyou","38",\N,\N,"2021-09-25 10:40:51"

不加參數(shù) optionally，那么所有的字段值都會加上雙引號。

自定義分隔符，將表數(shù)據(jù)導出到 csv 文件中

用以下命令把 student 表的數(shù)據(jù)導出到 /tmp/student.csv：

select * from student into outfile '/tmp/student.csv' fields terminated by ',' optionally enclosed by '"' lines terminated by '\n';

使用命令查看文件 /tmp/student.csv 的內(nèi)容：

[root@htlwk0001host ~]# cat /tmp/student.csv; 00000000000000000001,"liaowenxiong",18,\N,\N,"2021-09-25 10:40:51" 00000000000000000002,"liudehua",28,\N,\N,"2021-09-25 10:40:51" 00000000000000000003,"zhangxueyou",38,\N,\N,"2021-09-25 10:40:51"

我們再使用圖形化編輯器打開 csv 文件看下：

導出數(shù)據(jù)時，提示“拒絕訪問”

以 root（這是Linux系統(tǒng)的用戶）登錄 Linux 系統(tǒng)，再以 root（這是數(shù)據(jù)庫的用戶） 登錄數(shù)據(jù)庫，再將數(shù)據(jù)庫 test 中的表 student 導出到目錄 /root/test 下的文件 student.txt 中

導出結果：

mysql> select * from student into outfile '/root/test/student.txt' fields terminated by ',' enclosed by '"' lines terminated by '\n'; ERROR 1 (HY000): Can't create/write to file '/root/test/student.txt' (Errcode: 13 - Permission denied)

把目錄 /root/test 的權限改成 777，再以導出到目錄 /root/test 下的文件 student.txt 中

導出結果：

mysql> select * from student into outfile '/root/test/student.txt' fields terminated by ',' enclosed by '"' lines terminated by '\n'; Query OK, 3 rows affected (0.00 sec)

居然導出成功了。

把 /root/test 目錄的權限該 744

切換成 mysql（這是Linux系統(tǒng)的用戶）登錄 Linux 系統(tǒng)，將 /root/test 的擁有者和所屬用戶組改成 mysql，并且把權限改成 755，再以 root（這是數(shù)據(jù)庫的用戶） 登錄數(shù)據(jù)庫，再將數(shù)據(jù)庫 test 中的表 student 導出到目錄 /root/test 下的文件 student.txt 中。

[root@htlwk0001host ~]# chown -R mysql:mysql /root/test # 將/root/test的擁有者和所屬用戶組改成mysql [root@htlwk0001host ~]# ls -l /root 總用量 1284288 ... drwxrwxrwx 3 mysql mysql 272 9月 29 11:02 test ... [root@htlwk0001host ~]# chmod 755 /root/test # 將/root/test的權限改成所有者7，所屬用戶組5，其它用戶5 [root@htlwk0001host ~]# ls -l /root 總用量 1284288 ... drwxr-xr-x 3 mysql mysql 272 9月 29 11:02 test ...

導出結果：

mysql> select * from student into outfile '/root/test/student.txt' fields terminated by ',' enclosed by '"' lines terminated by '\n'; Query OK, 3 rows affected (0.00 sec)

居然可以成功導出數(shù)據(jù)。

總結

確保 Linux 用戶 mysql 對導出的目標目錄擁有寫入和執(zhí)行兩種權限

1.以 mysql 身份登錄 Linux 系統(tǒng)，不論目標目錄的擁有者是不是用戶 mysql，只要用戶 mysql 對輸出的目標目錄有寫入和執(zhí)行的權限，就可以將數(shù)據(jù)庫的數(shù)據(jù)導出到目標目錄下的文件中。
2.以 root 身份登錄 Linux 系統(tǒng)，不論 root 對目標目標有沒有寫入和執(zhí)行的權限，只要目標目錄的 other 的權限沒有寫入和執(zhí)行，則無法將數(shù)據(jù)庫的數(shù)據(jù)導出到目標目錄下的文件中。

以上兩點證明了一件事，登錄 MySQL 數(shù)據(jù)庫使用 SELECT INTO OUTFILE 語句導出數(shù)據(jù)庫的數(shù)據(jù)，在往硬盤寫入數(shù)據(jù)時，使用的是 mysql 用戶身份來執(zhí)行底層的寫入數(shù)據(jù)的命令，所以只要確保 Linux 用戶 mysql 對目標目錄有寫入和執(zhí)行兩種權限就可以正常導出數(shù)據(jù)了。

而這個 mysql 用戶是在安裝數(shù)據(jù)庫時自動創(chuàng)建的一個 Linux 操作系統(tǒng)的用戶，默認沒有 shell 登錄權限，需要在文件 /etc/passwd 或者使用命令 usermod 改成允許登錄。

將數(shù)據(jù)導出到 /tmp 目錄下

mysql 用戶默認擁有 /tmp 目錄的寫入和執(zhí)行的權限，所以只要把數(shù)據(jù)導出在該目錄下即可。

在導出數(shù)據(jù)時不指定目標文件的絕對路徑

在寫入的時候不指定絕對路徑，這樣文件默認會寫入 /etc/my.cnf 中參數(shù) datadir 所指定的目錄下。默認目錄為 /var/lib/mysql。然后移動寫入的文件到指定目錄下即可。

看下文件 /etc/my.cnf 中的參數(shù) datadir 的值：

mysql> select @@datadir; +-----------------+ | @@datadir | +-----------------+ | /var/lib/mysql/ | +-----------------+ 1 row in set (0.00 sec)

總結

以上是生活随笔為你收集整理的Linux系统下MySQL的导出数据语句SELECT … INTO OUTFILE的用法的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。