How to Export Data to an Outfile in MySQL

There are cases when a user wants operations captured in an output file or some local storage. The storage can be a CSV file or a notepad, where the contents from SQL can get put.

The file gets generated using the outfile command of MySQL. This command allows users to export and capture the SQL output into a particular file.

The select into outfile command allows the user to insert rows in a specific column, and the use of options allows to read the table and the type of formatting needed in the output file. It helps represent the table in a file in a user-defined format.

Syntax:

select * from stu into outfile "outfile.txt";
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option, so it cannot execute this statement

When a user tries to execute the command, the above error gets populated. The error population reason is the MySQL server installation with the default configuration as --secure-file-priv in the .ini file.

The option does not allow the import and export of libraries for security purposes. This variable is present under the sqld file restricting the user to share data to an external file.

The current path set for the variable --secure-file-priv can get seen using the command below:

SHOW VARIABLES LIKE "secure_file_priv";

Change the configuration variable value with the path present with the variable name. Traverse to the shown destination in the variable.

Find my.ini in the location. Search the secure_file_priv variable and replace the value with an empty value.

Below is the screenshot of how the image variable value is present by default.

Navigate to the path that is present with the variable. And the user will be able to find the my.ini file.

Change the configurations for the same variable and save the file again. Restart the server to see if the error has gone.

The variations present with the above command are as follows:

SELECT stu_id, stu_name, stu_age, stu_add INTO OUTFILE 'outfile.csv'
    FIELDS TERMINATED BY ','
    OPTIONALLY ENCLOSED BY '"'
    LINES TERMINATED BY '\n'
    FROM tablename;

The command permits users to specify the escape sequences present in the table. The column inside the table gets separated by a comma, , or pipe |, or a tab character.

It can get configured using the above command and the specific command at the insertion time.

The detailed elaboration of the above command is as follows.

1. The SELECT * command gets used to select all the records of the specific table.
2. The INTO OUTFILE keyword states to the server that the specific command will be doing output operation over a file.
3. The filename states the file, where output gets inserted.
4. The FIELDS TERMINATED BY option allows the user to specify the character needed for column and attribute separation. It increases the readability of the file. The keywords let the user escape the special characters in the CSV file.
5. The OPTIONALLY ENCLOSED BY option is not a mandatory option that one should provide. It allows users to provide a sequence that is in string format.
6. The LINES TERMINATED BY option allows treating the \n escape sequence as newlines in the output file. This option helps better understand of table and interpretation in the output file.
7. At the end, a table name can get provided, which wraps up the whole command. All the options given above are wrapped and bounded upon the table_name. The operations need to get performed on the following statement.

An image of the MySQL command prompt is provided below.

It shows the command executed in the local MySQL command prompt.

Now, the above output is the records present in the file that gets created using the outfile command.