Using SELECT INTO OUTFILE in MySQL

This article discusses using the SELECT INTO OUTFILE statement in MySQL.

About SELECT INTO OUTFILE

The SELECT INTO OUTFILE statement writes the results of a query to a file. You can also specify custom column and row terminators to format the output.

Support for SELECT INTO OUTFILE

SELECT INTO OUTFILE statements are supported on all unmanaged hosting packages, where you have complete control over the environment, including MySQL user privileges and configuration.

Shared and reseller hosting packages, however, do not allow you to run SELECT INTO OUTFILE statements. For security reasons, users are not granted the FILE privilege, which is necessary to run SELECT INTO OUTFILE statements.

Alternative to SELECT INTO OUTFILE

If your hosting package does not allow you to run SELECT INTO OUTFILE statements, you can still create files based on MySQL queries. To do this, simply redirect the SQL query output to a file from the command line. The following example demonstrates how to do this:

echo "sql_query;" | mysql --user=mysql_username --password=mysql_password mysql_database > /home/username/query.txt

In this example, query output is redirected to the query.txt file. Make sure you use the following parameters correctly in your own commands:

• sql_query: This is the actual SQL query, such as “SELECT * FROM employees;”.
• mysql_username: This is the MySQL username for the database that you want to access.
• mysql_password: This is the password for the MySQL username you are using above.
• mysql_database: This is the name of the MySQL database that you want to access.
• username: This is your A2 Hosting account username.

You can modify these values, including the path for the output file, to meet your own requirements.

More Information

For more information about SELECT INTO OUTFILE, please see the official documentation at https://mariadb.com/kb/en/select-into-outfile.