values01.txt<\/strong> file:<\/p>\nSELECT 'abc', 123, 'def', 456\nINTO OUTFILE 'values01.txt';<\/pre>\nThe SELECT<\/code> clause defines the four values, and the INTO<\/code> OUTFILE<\/code> clause specifies that name of the new file. The file cannot already exist when you run this statement. It if does, you will receive an error. If it does not exist, MySQL creates the file and inserts the values returned by the SELECT<\/code> statement. The following figure shows the file opened in BBEdit.<\/p>\n![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/07\/word-image-97336-1.png\")
<\/p>\n
\nNote: depending on your configuration, you may receive an error such as:<\/p>\n
INTO OUTFILE 'values01.txt'\t<\/code><\/p>\nError Code: 1290. The MySQL server is running with the --secure-file-priv option so it cannot execute this statement\t<\/code><\/p>\nwhen you execute this statement. You can see this security setting using:<\/p>\n
SHOW VARIABLES LIKE \"secure_file_priv\";<\/pre>\nThis will show you a path that has been set up for you to securely use from the server. You may need to prefix your .txt file names with the path that is returned. Or you can change the setting in your config file to be \"\"<\/code> and your file will be output to any location on the server where it has rights to.<\/p>\n
\nWhen you use the INTO<\/code> OUTFILE<\/code> clause, MySQL creates the file on the computer where MySQL is running. There is no way to save the file to another computer unless the remote computer can be accessed via a network-mapped path on the host system.<\/p>\nYou might have noticed in the preceding example that the filename is specified without the full pathname. When you don\u2019t specify a pathname, MySQL creates the file in the database directory, which in this case, is the travel<\/strong> directory because you\u2019re working in the travel<\/code> database. The travel<\/strong> directory stores the data files for the airplanes<\/code> and manufacturers<\/code> tables. (Note: on the editor’s Windows machine the file was located in: C:\\ProgramData\\MySQL\\MySQL Server 8.0\\Data\\travel<\/code>).<\/p>\nIf you\u2019re using a MySQL instance that is not under your control, you might find that database administrators or other stakeholders do not want you to save files to the database directory, in which case, you\u2019ll need to use a different folder. To save the output file elsewhere, you must specify the full pathname in the INTO<\/code> OUTFILE<\/code> clause. In addition, the target directory must be configured with the proper write permissions. If you receive an \u201cOS errno 13 – Permission denied\u201d error message when you try to run your statement, you\u2019ll know that something is wrong with the directory permissions.<\/p>\nAnother issue that you might have to contend with is that the account you use to log into MySQL must be granted the FILE<\/code> privilege in order to read and write files on the MySQL host. Without this privilege, you\u2019ll receive an access denied error message when attempting to run your statements. If you\u2019re trying out the examples in this article, chances are you\u2019re running them against a MySQL host that you control, so you can set up the necessary permissions yourself. If you don\u2019t control the host, you\u2019ll need to work with a database or system administrator to get you set up.<\/p>\nWith all that in mind, the rest of examples assume that you\u2019ll be targeting a location other than the database directory when defining your INTO<\/code> OUTFILE<\/code> clause and that you have all the necessary permissions in place. The next example is similar to the previous one, but it now specifies the full directory path:<\/p>\nSELECT 'abc', 123, 'def', 456\nINTO OUTFILE '\/Users\/user1\/data\/values02.txt';<\/pre>\nThe INTO<\/code> OUTFILE<\/code> clause now points the \/Users\/user1\/data\/<\/strong> directory instead of the database folder. However, you can specify whatever directory works for you. (You can also use whatever filename you like.) If you\u2019re working on a Windows system, you should specify pathname backslashes as either forward slashes or double backslashes.<\/p>\nMost SELECT<\/code> statements include a FROM<\/code> clause that retrieves data from a table or another type of table structure, such as a VALUES<\/code> clause or table subquery. For example, the FROM<\/code> clause in following SELECT<\/code> statement include a VALUES<\/code> clause that creates a table with two columns and three rows, which are outputted to the values03.txt<\/strong> file:<\/p>\nSELECT *\nFROM (VALUES \n ROW('abc', 123), \n ROW('def', 456), \n ROW('ghi', 789)) AS tbl\nINTO OUTFILE '\/Users\/user1\/data\/values03.txt';<\/pre>\nWhen you run the statement, MySQL creates the file and populates it with the returned values, as shown in the following figure:<\/p>\n
![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/07\/word-image-97336-2.png\")
<\/p>\n
Of course, you can also retrieve data from a regular table and output it to a text file, which is how you\u2019ll most likely be using the INTO<\/code> OUTFILE<\/code> clause. For example, the following SELECT<\/code> statement retrieves data from the airplanes<\/code> table and outputs those results to the airplanes01.txt<\/strong> file:<\/p>\nSELECT * FROM airplanes\nORDER BY plane\nLIMIT 10\nINTO OUTFILE '\/Users\/user1\/data\/airplanes01.txt';<\/pre>\nThe SELECT<\/code> statement sorts the data by the plane<\/code> column and limits the results to the first 10 rows. Because the SELECT<\/code> clause includes only an asterisk (*), the statement returns all of the table\u2019s columns and saves them to the airplanes01.txt<\/strong> file, as shown in the following figure.<\/p>\n![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/07\/word-image-97336-3.png\")
<\/p>\n
The MySQL TABLE<\/code> statement also supports the INTO<\/code> OUTFILE<\/code> clause. Because of this, you can recast the preceding SELECT<\/code> statement as the following TABLE<\/code> statement, which returns that same results as the previous example but saves them to the airplanes02.txt<\/strong> file:<\/p>\nTABLE airplanes\nORDER BY plane\nLIMIT 10\nINTO OUTFILE '\/Users\/user1\/data\/airplanes02.txt';<\/pre>\nThe TABLE<\/code> statement is fairly limited in scope and supports only a few clauses, including ORDER<\/code> BY<\/code> and LIMIT<\/code>. The statement can be useful when you want to return all columns and either all rows or a certain number of rows. That said, you\u2019ll usually want more control over your queries, which is why you\u2019ll likely be using the SELECT<\/code> statement. The statement lets you specify which columns to return, and it provides additional clauses, such as WHERE<\/code> and GROUP<\/code> BY<\/code>. For example, the following SELECT<\/code> statement limits the results to specific columns and types of planes:<\/p>\nSELECT plane, engine_type, engine_count, max_weight, plane_length\nFROM airplanes\nWHERE engine_type = 'jet'\nORDER BY plane\nINTO OUTFILE '\/Users\/user1\/data\/jets01.txt';<\/pre>\nNow the results are much more targeted, as reflected in the jets01.txt<\/strong> file, which is shown in the following figure.<\/p>\n![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/07\/word-image-97336-4.png\")
<\/p>\n
As expected, the file contains only those rows whose engine_type<\/code> value equals jet<\/code>. The data is also limited to the five specified columns: plane<\/code>, engine_type<\/code>, engine_count<\/code>, max_weight<\/code> and plane_length<\/code>.<\/p>\nWorking with the INTO OUTFILE<\/strong> export options<\/h2>\nAs mentioned earlier, the INTO<\/code> OUTFILE<\/code> clause supports multiple export options. The options let you better control how the returned data is saved to the output file. If you don\u2019t specify any export options, MySQL uses the default options, such as using tabs to separate values and not enclosing the values in quotes or other characters.<\/p>\nIf the previous statement were redefined to include the export options and their default values, it would look like the following SELECT<\/code> statement:<\/p>\nSELECT plane, engine_type, engine_count, max_weight, plane_length\nFROM airplanes\nWHERE engine_type = 'jet'\nORDER BY plane\nINTO OUTFILE '\/Users\/user1\/data\/jets02.txt'\n FIELDS TERMINATED BY '\\t' ENCLOSED BY '' ESCAPED BY '\\\\'\n LINES TERMINATED BY '\\n' STARTING BY '';<\/pre>\nThe statement returns the same results as the previous one. The only difference is that the INTO<\/code> OUTFILE<\/code> clause explicitly defines the export options, using their default values. However, suppose you want to use a comma instead of a tab as the field terminator, and you want to enclose string values in double quotes. To do this, you can modify the export options as follows:<\/p>\nSELECT plane, engine_type, engine_count, max_weight, plane_length\nFROM airplanes\nWHERE engine_type = 'jet'\nORDER BY plane\nINTO OUTFILE '\/Users\/user1\/data\/jets03.txt'\n FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' ESCAPED BY '\\\\'\n LINES TERMINATED BY '\\n' STARTING BY '';<\/pre>\nThe FIELDS<\/code> TERMINATED<\/code> BY<\/code> option now specifies a comma as its value, and the ENCLOSED<\/code> BY<\/code> option specifies a double quote as it value. The clause also includes the OPTIONALLY<\/code> keyword, which limits its application to string values. Although this SELECT<\/code> statement and the preceding one return the same data, the output file now looks much different, as shown in the following figure.<\/p>\n![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/07\/word-image-97336-5.png\")
<\/p>\n
Each string value is enclosed in double quotes, and the fields are separated by commas. If you want, you can use the .csv extension for the file, rather than .txt. The results would be the same. You should use whichever file extension best supports your requirements.<\/p>\n
Now suppose you want to add a prefix to the beginning of each row. You can easily do this by changing the value of the STARTING<\/code> BY<\/code> option. For example, the following SELECT<\/code> statement replaces the empty string with xxx<\/code> :<\/code> (including the trailing space):<\/p>\nSELECT plane, engine_type, engine_count, max_weight, plane_length\nFROM airplanes\nWHERE engine_type = 'jet'\nORDER BY plane\nINTO OUTFILE '\/Users\/user1\/data\/jets04.txt'\n FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' ESCAPED BY '\\\\'\n LINES TERMINATED BY '\\n' STARTING BY 'xxx : ';<\/pre>\n