mysql read local_mysql load data infile load data local infile区别

load data local infile 'D:/output.txt' into table testtable lines terminated by '\r\n' ;

命令：load data local infile “filename.txt” into table tbl_name;

说明：MySQl的版本不得低于3.22.15，否则load data local不起作用。

如果你没有给出local,则服务器按如下方法对其进行定位:

1)如果你的filename为绝对路径,则服务器从根目录开始查找该文件.

2)如果你的filename为相对路径,则服务器从数据库的数据目录中开始查找该文件.

如果你给出了local,则文件将按以下方式进行定位:

1)如果你的filename为绝对路径,则客户机从根目录开始查找该文件.

2)如果你的filename为相对路径,则客户机从当前目录开始查找该文件.

另外要注意：

In MySQL 3.23.49 and MySQL 4.0.2, we added some new options to deal with possible security issues when it comes to LOAD DATA LOCAL.

There are two possible problems with supporting this command:

As the reading of the file is initiated from the server, one could theoretically create a patched MySQL server that could read any file on the client machine that the current user has read accessto, when the client issues a query against the table.

In a web environment where the clients are connecting from a web server, a user could use LOAD DATA LOCAL to read any files that the web server process has read access to (assuming a user could run any command against the SQL server).

There are two separate fixes for this:

If you don't configure MySQL with --enable-local-infile, then LOAD DATA LOCAL will be disabled by all clients, unless one calls mysql_options(... MYSQL_OPT_LOCAL_INFILE, 0) in the client. See section 8.1.3.163 mysql_options().

For the mysql command-line client, LOAD DATA LOCAL can be enabled by specifying the option --local-infile[=1], or disabled with --local-infile=0.

By default, all MySQL clients and libraries are compiled with --enable-local-infile, to be compatible with MySQL 3.23.48 and before.

One can disable all LOAD DATA LOCAL commands in the MySQL server by starting mysqld with --local-infile=0.

In the case that LOAD DATA LOCAL INFILE is disabled in the server or the client, you will get the error message (1148):

The used command is not allowed with this MySQL version