Why is \'SELECT * INTO OUTFILE LOCAL\' missing in MySQL, and how can I achieve the same result?

MySQL - SELECT * INTO OUTFILE LOCAL?

In a recent server migration, a database was moved to a different server, causing the previous command "SELECT * INTO OUTFILE ..." to fail. While "LOAD DATA INFILE ..." remained functional, the lack of a corresponding "SELECT INTO OUTFILE LOCAL" option perplexed the user.

Explanation for Lack of "SELECT INTO OUTFILE LOCAL"

Unlike "LOAD DATA INFILE" which reads from a local file on the server, "SELECT INTO OUTFILE" is intended to generate a text file on the server itself. Due to security concerns, the ability to create files on external clients is restricted, hence the inability of "SELECT INTO OUTFILE LOCAL" to function.

Alternative Solution

The manual recommends using mysql in combination with a redirect to generate the file on the client host, as seen in the example below:

mysql -h my.db.com -u usrname--password=pass db_name -e 'SELECT foo FROM bar' > /tmp/myfile.txt

MariaDB's Capability

While the article does not explicitly mention MariaDB, the issue pertains to the behavior of MySQL and does not directly affect MariaDB's functionality.

The above is the detailed content of Why is 'SELECT * INTO OUTFILE LOCAL' missing in MySQL, and how can I achieve the same result?. For more information, please follow other related articles on the PHP Chinese website!