Differences between DATA INFILE and LOAD DATA LOCAL INFILE

Question

What are the differences between LOAD DATA INFILE and LOAD DATA LOCAL INFILE?

Answer 1

From the MySQL documentation:

If LOCAL is specified, the file is read by the client program on the client host and sent to the server.

If LOCAL is not specified, the file must be located on the server host and is read directly by the server.

If you include the LOCAL keyword, MySQL will look for the file to load locally, and if you omit LOCAL then it will attempt to find it on the server.

Answer 2

LOAD DATA INFILE gets the file from the database server's local filesystem. The file has to be located in the database directory or have world read permissions, and the client username must have the FILE privilege.

LOAD DATA LOCAL INFILE reads the file on the client, and sends the contents to the server.

You can find more details in the documentation.