【免責(zé)聲明】文章僅供學(xué)習(xí)交流，觀點(diǎn)代表個(gè)人，與任何公司無關(guān)。
編輯|SQL和數(shù)據(jù)庫技術(shù)(ID:SQLplusDB)
【MySQL】使用LOAD DATA INFILE命令加載數(shù)據(jù)文件到MySQL數(shù)據(jù)庫的方法和常見錯(cuò)誤及解決方法
在MySQL數(shù)據(jù)庫中，可以使用LOAD DATA INFILE命令將數(shù)據(jù)從 CSV 文件導(dǎo)入已創(chuàng)建的表中。
LOAD DATA INFILE的語法詳細(xì)
LOAD DATA INFILE的語法詳細(xì)如下：
LOAD DATA
[LOW_PRIORITY | CONCURRENT] [LOCAL]
INFILE 'file_name'
[REPLACE | IGNORE]
INTO TABLE tbl_name
[PARTITION (partition_name [, partition_name] ...)]
[CHARACTER SET charset_name]
[{FIELDS | COLUMNS}
[TERMINATED BY 'string']
[[OPTIONALLY] ENCLOSED BY 'char']
[ESCAPED BY 'char']
]
[LINES
[STARTING BY 'string']
[TERMINATED BY 'string']
]
[IGNORE number {LINES | ROWS}]
[(col_name_or_user_var
[, col_name_or_user_var] ...)]
[SET col_name={expr | DEFAULT}
[, col_name={expr | DEFAULT}] ...]
參考：
3.3.3 Loading Data into a Table
https://dev.mysql.com/doc/refman/8.0/en/load-data.html
6.1.6 Security Considerations for LOAD DATA LOCAL
https://dev.mysql.com/doc/refman/8.0/en/load-data-local-security.html文章來源地址http://www.zghlxwxcb.cn/news/detail-487665.html
具體步驟如下。
1.創(chuàng)建（選擇）目標(biāo)數(shù)據(jù)庫和表
創(chuàng)建測(cè)試數(shù)據(jù)庫testdb和相關(guān)的表
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sakila |
| sys |
+--------------------+
5 rows in set (0.05 sec)
mysql> CREATE DATABASE testdb;
Query OK, 1 row affected (0.10 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sakila |
| sys |
| testdb |
+--------------------+
6 rows in set (0.00 sec)
mysql> use testdb;
Database changed
mysql> CREATE TABLE Student (
-> id INT PRIMARY KEY,
-> name VARCHAR(50)
-> );
Query OK, 0 rows affected (0.35 sec)
2.將數(shù)據(jù)從 CSV 文件導(dǎo)入已創(chuàng)建的表
LOAD DATA INFILE 'students.csv'
INTO TABLE student
FIELDS TERMINATED BY ',' -- 字段分隔符
ENCLOSED BY '"' -- 字段使用的引號(hào)
ESCAPED BY '\\' -- 轉(zhuǎn)義符
LINES TERMINATED BY '\n' -- 行分隔符
IGNORE 1 ROWS; -- 忽略 CSV 文件第一行（表頭）
執(zhí)行例：
mysql> show variables like 'secure_file_priv';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| secure_file_priv | F:\ |
+------------------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> LOAD DATA INFILE 'F:\\students.csv'
-> INTO TABLE student
-> FIELDS TERMINATED BY ',' -- 字段分隔符
-> ENCLOSED BY '"' -- 字段使用的引號(hào)
-> ESCAPED BY '\\' -- 轉(zhuǎn)義符
-> LINES TERMINATED BY '\n' -- 行分隔符
-> IGNORE 1 ROWS; -- 忽略 CSV 文件第一行（表頭）
Query OK, 100 rows affected (0.09 sec)
Records: 100 Deleted: 0 Skipped: 0 Warnings: 0
mysql> select count(*) from student;
+----------+
| count(*) |
+----------+
| 100 |
+----------+
1 row in set (0.00 sec)
常見錯(cuò)誤和解決方法
錯(cuò)誤1：ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
在將數(shù)據(jù)加載到MySQL數(shù)據(jù)庫時(shí)發(fā)生如下錯(cuò)誤。
mysql> LOAD DATA INFILE 'students.csv'
-> INTO TABLE student
-> FIELDS TERMINATED BY ',' -- 字段分隔符
-> ENCLOSED BY '"' -- 字段使用的引號(hào)
-> ESCAPED BY '\\' -- 轉(zhuǎn)義符
-> LINES TERMINATED BY '\n' -- 行分隔符
-> IGNORE 1 ROWS; -- 忽略 CSV 文件第一行（表頭）
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
mysql>
原因
這個(gè)錯(cuò)誤表示由于 MySQL中 --secure-file-priv 選項(xiàng)的設(shè)置，限制了用戶從本地文件系統(tǒng)加載數(shù)據(jù)到服務(wù)器中。
解決方法
修改secure_file_priv參數(shù)為文件所在目錄 或者 指定為空。
關(guān)于secure_file_priv參數(shù)
secure_file_priv是一個(gè)MySQL 全局（Global）系統(tǒng)變量，用于限制數(shù)據(jù)導(dǎo)入和導(dǎo)出操作，例如LOAD DATA和SELECT … INTO OUTFILE語句以及LOAD_FILE()函數(shù)的操作。只有擁有FILE權(quán)限的用戶才能執(zhí)行這些操作。
secure_file_priv的設(shè)置包括三種情況：
- 如果為空(secure-file-priv=“”)，表示沒有任何限制。這不是一個(gè)安全的設(shè)置。
- 如果設(shè)置為目錄的名稱，表示服務(wù)器限制導(dǎo)入和導(dǎo)出操作僅使用該目錄中的文件。服務(wù)器不會(huì)創(chuàng)建該目錄，所以必須為存在的路徑。
- 如果設(shè)置為NULL(secure-file-priv=null)，表示服務(wù)器禁用導(dǎo)入和導(dǎo)出操作。
secure_file_priv的默認(rèn)值根據(jù)安裝的平臺(tái)有所不同。
secure_file_priv參數(shù)內(nèi)容如下：
| Command-Line Format | –secure-file-priv=dir_name |
|---|---|
| System Variable | secure_file_priv |
| Scope | Global |
| Dynamic | No |
| SET_VAR Hint Applies | No |
| Type | String |
| Default Value | platform specific |
| Valid Values | empty string, dirname, NULL |
參考：
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_secure_file_priv
查看secure_file_priv參數(shù)設(shè)置
可以通過以下命令查看 secure_file_priv 的當(dāng)前設(shè)置值：
SHOW VARIABLES LIKE 'secure_file_priv';
例：
mysql> show variables like 'secure_file_priv';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| secure_file_priv | F:\ |
+------------------+-------+
1 row in set, 1 warning (0.00 sec)
修改secure_file_priv參數(shù)
因?yàn)閟ecure_file_priv是一個(gè)非持久化只讀變量，不能通過SET命令進(jìn)行修改。
所以需要在MySQL配置文件my.cnf (Mac, Linux) 或者 my.ini (Windows) 中設(shè)置secure-file-priv參數(shù)。具體操作方法如下：
打開MySQL配置文件my.cnf或my.ini。
添加或修改secure-file-priv參數(shù)，例如：secure-file-priv=/var/lib/mysql-files。
保存并關(guān)閉文件。
重啟MySQL服務(wù)器，使配置生效。
net stop mysql
net start mysql
例：（Windows）
、
注意：確保設(shè)置的路徑存在，并且MySQL用戶有訪問該路徑的權(quán)限。
如果secure_file_priv設(shè)置不正確，可能導(dǎo)致MySQL進(jìn)程啟動(dòng)失敗。
錯(cuò)誤2：ERROR 2068 (HY000): LOAD DATA LOCAL INFILE file request rejected due to restrictions on access.
使用 LOAD DATA LOCAL INFILE，從客戶端加載數(shù)據(jù)到數(shù)據(jù)庫的時(shí)候，可能發(fā)生如下錯(cuò)誤。
--服務(wù)器端未啟動(dòng)LOAD DATA LOCAL INFILE功能
ERROR 3948 (42000): Loading local data is disabled; this must be enabled on both the client and server sides
--客戶端未啟動(dòng)LOAD DATA LOCAL INFILE功能
ERROR 2068 (HY000): LOAD DATA LOCAL INFILE file request rejected due to restrictions on access.
例：
mysql> LOAD DATA LOCAL INFILE 'F:\\students.csv'
-> INTO TABLE student
-> FIELDS TERMINATED BY ',' -- 字段分隔符
-> ENCLOSED BY '"' -- 字段使用的引號(hào)
-> ESCAPED BY '\\' -- 轉(zhuǎn)義符
-> LINES TERMINATED BY '\n' -- 行分隔符
-> IGNORE 1 ROWS;
ERROR 3948 (42000): Loading local data is disabled; this must be enabled on both the client and server sides
mysql>
原因
這個(gè)錯(cuò)誤是因?yàn)槟J(rèn)情況下，MySQL服務(wù)器禁止使用LOAD DATA LOCAL INFILE命令從客戶端讀取本地文件。
解決方法
1.服務(wù)器端設(shè)置local_infile，啟用LOAD DATA LOCAL INFILE功能。
修改local_infile為啟用。
例：
mysql> show variables like 'local%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile | OFF |
+---------------+-------+
1 row in set, 1 warning (0.02 sec)
mysql> set global local_infile = 'ON';
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'local%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile | ON |
+---------------+-------+
1 row in set, 1 warning (0.00 sec)
2.客戶端在連接MySQL服務(wù)器時(shí)添加–local-infile選項(xiàng)，啟用LOAD DATA LOCAL INFILE功能。
例：
C:\Users\Administrator> mysql --local-infile=1 -u root -p
mysql> use testdb
Database changed
mysql> LOAD DATA LOCAL INFILE 'F:\\students.csv'
-> INTO TABLE student
-> FIELDS TERMINATED BY ',' -- 字段分隔符
-> ENCLOSED BY '"' -- 字段使用的引號(hào)
-> ESCAPED BY '\\' -- 轉(zhuǎn)義符
-> LINES TERMINATED BY '\n' -- 行分隔符
-> IGNORE 1 ROWS;
Query OK, 0 rows affected, 100 warnings (0.08 sec)
Records: 100 Deleted: 0 Skipped: 100 Warnings: 100
mysql> select count(*) from student;
+----------+
| count(*) |
+----------+
| 100 |
+----------+
1 row in set (0.05 sec)
關(guān)于local_infile參數(shù)
local_infile參數(shù)是一個(gè)MySQL系統(tǒng)變量，用于控制服務(wù)器端LOAD DATA語句的LOCAL功能。
根據(jù)local_infile設(shè)置，服務(wù)器會(huì)拒絕或允許啟用客戶端LOCAL功能的客戶端加載本地?cái)?shù)據(jù)。
| Command-Line Format | –local-infile |
|---|---|
| System Variable | local_infile |
| Scope | Global |
| Dynamic | Yes |
| SET_VAR Hint Applies | No |
| Type | Boolean |
| Default Value | OFF |
注意：local_infile參數(shù)可以動(dòng)態(tài)修改。
參考：
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_local_infile文章來源：http://www.zghlxwxcb.cn/news/detail-487665.html
6.1.6 Security Considerations for LOAD DATA LOCAL
https://dev.mysql.com/doc/refman/8.0/en/load-data-local-security.html
到了這里，關(guān)于使用LOAD DATA INFILE命令加載數(shù)據(jù)文件到MySQL數(shù)據(jù)庫的方法和常見錯(cuò)誤及解決方法的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
