MySQL变量：local_infile

最新推荐文章于 2025-03-25 21:52:00 发布

转载最新推荐文章于 2025-03-25 21:52:00 发布 · 2.6w 阅读

文章标签：

#MySQL变量：local_infile

本文详细解析了MySQL中LOCAL_INFILE服务器变量的作用及其对LOAD DATA LOCAL INFILE命令的影响。当该变量设置为ON时，允许从本地文件系统加载数据到数据库表；反之则禁用此功能。文章通过实例展示了如何检查当前LOCAL_INFILE变量状态，以及如何更改其值以启用或禁用文件导入功能。

local_infile服务器变量指示能否使用load data local infile命令。该变量默认为ON。
该变量为OFF时，禁用客户端的load data local infile命令。

Sql代码

收藏代码

mysql> show create table test.t\G
*************************** 1. row ***************************
Table: t
Create Table: CREATE TABLE `t` (
`id` int(11) DEFAULT NULL,
`name` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> show variables like 'local_infile';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile | OFF |
+---------------+-------+
1 row in set (0.00 sec)
mysql> load data local infile 'D:\\ATMP\\mysql_repl\\5.6_local\\data.txt' into table test.t fields terminated by ',' OPTIONALLY ENCLOSED BY '\'' lines terminated by '\r\n';
ERROR 1148 (42000): The used command is not allowed with this MySQL version
mysql> set global local_infile=ON;
Query OK, 0 rows affected (0.00 sec)
mysql> load data local infile 'D:\\ATMP\\mysql_repl\\5.6_local\\data.txt' into table test.t fields terminated by ',' OPTIONALLY ENCLOSED BY '\'' lines terminated by '\r\n';
Query OK, 3 rows affected (0.06 sec)
Records: 3 Deleted: 0 Skipped: 0 Warnings: 0