【免责声明】文章仅供学习交流,观点代表个人,与任何公司无关。
编辑|SQL和数据库技术(ID:SQLplusDB)
在MySQL数据库中,可以使用LOAD DATA INFILE命令将数据从 CSV 文件导入已创建的表中。
LOAD DATA INFILE的语法详细如下:
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
具体步骤如下。
创建测试数据库testdb和相关的表
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)
LOAD DATA INFILE 'students.csv' INTO TABLE student FIELDS TERMINATED BY ',' -- 字段分隔符 ENCLOSED BY '"' -- 字段使用的引号 ESCAPED BY '\' -- 转义符 LINES TERMINATED BY '\n' -- 行分隔符 IGNORE 1 ROWS; -- 忽略 CSV 文件第一行(表头)
执行例:
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 '"' -- 字段使用的引号 -> ESCAPED BY '\\' -- 转义符 -> 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)
在将数据加载到MySQL数据库时发生如下错误。
mysql> LOAD DATA INFILE 'students.csv' -> INTO TABLE student -> FIELDS TERMINATED BY ',' -- 字段分隔符 -> ENCLOSED BY '"' -- 字段使用的引号 -> ESCAPED BY '\\' -- 转义符 -> 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>
这个错误表示由于 MySQL中 --secure-file-priv 选项的设置,限制了用户从本地文件系统加载数据到服务器中。
修改secure_file_priv参数为文件所在目录 或者 指定为空。
secure_file_priv是一个MySQL 全局(Global)系统变量,用于限制数据导入和导出操作,例如LOAD DATA和SELECT … INTO OUTFILE语句以及LOAD_FILE()函数的操作。只有拥有FILE权限的用户才能执行这些操作。
secure_file_priv的设置包括三种情况:
- 如果为空(secure-file-priv=“”),表示没有任何限制。这不是一个安全的设置。 - 如果设置为目录的名称,表示服务器限制导入和导出操作仅使用该目录中的文件。服务器不会创建该目录,所以必须为存在的路径。 - 如果设置为NULL(secure-file-priv=null),表示服务器禁用导入和导出操作。
secure_file_priv的默认值根据安装的平台有所不同。
secure_file_priv参数内容如下:
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 的当前设置值:
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是一个非持久化只读变量,不能通过SET命令进行修改。
所以需要在MySQL配置文件my.cnf (Mac, Linux) 或者 my.ini (Windows) 中设置secure-file-priv参数。具体操作方法如下:
打开MySQL配置文件my.cnf或my.ini。 添加或修改secure-file-priv参数,例如:secure-file-priv=/var/lib/mysql-files。 保存并关闭文件。 重启MySQL服务器,使配置生效。 net stop mysql net start mysql
例:(Windows)
、
注意:确保设置的路径存在,并且MySQL用户有访问该路径的权限。
如果secure_file_priv设置不正确,可能导致MySQL进程启动失败。
使用 LOAD DATA LOCAL INFILE,从客户端加载数据到数据库的时候,可能发生如下错误。
--服务器端未启动LOAD DATA LOCAL INFILE功能 ERROR 3948 (42000): Loading local data is disabled; this must be enabled on both the client and server sides --客户端未启动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 '"' -- 字段使用的引号 -> ESCAPED BY '\\' -- 转义符 -> 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>
这个错误是因为默认情况下,MySQL服务器禁止使用LOAD DATA LOCAL INFILE命令从客户端读取本地文件。
1.服务器端设置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服务器时添加–local-infile选项,启用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 '"' -- 字段使用的引号 -> ESCAPED BY '\\' -- 转义符 -> 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)
local_infile参数是一个MySQL系统变量,用于控制服务器端LOAD DATA语句的LOCAL功能。
根据local_infile设置,服务器会拒绝或允许启用客户端LOCAL功能的客户端加载本地数据。
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参数可以动态修改。
参考:
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_local_infile
6.1.6 Security Considerations for LOAD DATA LOCAL
https://dev.mysql.com/doc/refman/8.0/en/load-data-local-security.html