Read Data and Output to a File Msql

March 05, 2022 Post a Comment

Do you need to save your results from a MySQL query to a CSV or text file?

It'due south easy to practice in MySQL. Y'all tin can do this using an IDE or the command line, using a born MySQL command.

Let's take a look.

Basic Query

Let's utilise a simple case of a SELECT statement for this.

                SELECT id, first_name, last_name FROM client;

Hither are the results:

id	first_name	last_name
1	John	Smith
ii	Mary	McAdams
3	Steve	Pitt
four	Mark	Cousins
five	Shaun	Jones
vii	Amy	McDonald
8	Brad	Swan
10	Wendy	Johnson

We may run across these results in the output of our command line or in the IDE, such equally MySQL Workbench.

How can we save them into a text file?

We could copy and paste them, simply that's slow and manual.

Save MySQL Results to a File

There's a built-in MySQL output to file feature every bit part of the SELECT statement.

We merely add together the words INTO OUTFILE, followed by a filename, to the end of the SELECT statement.

For instance:

                SELECT id, first_name, last_name FROM client INTO OUTFILE '/temp/myoutput.txt';

This will create a new file called myoutput.txt that contains the results of this query, in a folder chosen temp.

What happens if you get this fault (like I did)?

Error Code: 1290. The MySQL server is running with the –secure-file-priv option so it cannot execute this argument

I'll explain more than nigh resolving this afterward in this guide.

For now, assuming yous can run the statement to generate the file, it will look similar this:

The text in the file is:

1 John Smith 2 Mary McAdams three Steve Pitt 4 Mark Cousins five Shaun Jones vii Amy McDonald 8 Brad Swan x Wendy Johnson

As you tin can run into, the fields are separated past tabs. This is the default behaviour, but it can be changed.

Changing Parameters to Gear up Comma Separated Values

You tin change the parameters of this INTO OUTFILE keyword to modify how the file is written.

At that place are several extra parameters. These are some of the virtually common:

FIELDS TERMINATED BY: this indicates the graphic symbol(s) that are used to stop a field.
ENCLOSED BY: this indicates the grapheme(s) that will be used to surround each field.
LINES TERMINATED BY: this indicates the character(s) that are used to end a line and start a new line.

For example, to select the information to a CSV file and enclose each field in a double quote:

                SELECT id, first_name, last_name FROM customer INTO OUTFILE '/temp/myoutput.txt' FIELDS TERMINATED BY ',' ENCLOSED By '"' LINES TERMINATED BY '\northward';

If we run this statement, we can check the file, which will expect similar this:

The text in the file is:

"1","John","Smith" "ii","Mary","McAdams" "3","Steve","Pitt" "4","Mark","Cousins" "five","Shaun","Jones" "7","Amy","McDonald" "8","Brad","Swan" "10","Wendy","Johnson"

So that's how you can generate a CSV or text file in MySQL. You merely add the INTO OUTFILE keyword to the end of a SELECT query and specify some parameters.

Include Headings in Output File

You might have noticed that there are no column headings in the output file.

How tin can you get column headings to display? Unfortunately, there's no easy option you tin enable.

1 commonly-mentioned manner is to utilize a UNION ALL to select the column headings and the data.

So, your query would await like this:

                SELECT 'id', 'first_name', 'last_name' Wedlock ALL SELECT id, first_name, last_name FROM customer INTO OUTFILE '/temp/myoutput.txt' FIELDS TERMINATED By ',' ENCLOSED BY '"' LINES TERMINATED Past '\due north';

This would mean your cavalcade headings would be shown in the file:

"id","first_name","last_name" "1","John","Smith" "2","Mary","McAdams" "3","Steve","Pitt" "4","Mark","Cousins" "five","Shaun","Jones" "7","Amy","McDonald" "8","Brad","Swan" "10","Wendy","Johnson"

However, in that location are a few bug with this:

Data Types

This merely works if the data types are characters. If you have any other types (such as numbers or dates) in your data, so you'll get issues with your query.

This is because the data type of the column is determined by the beginning query in the UNION. All of the column headers are text values, so when the 2d part of the UNION query is run, it may try to add a date into a character cavalcade and cause an fault.

Can Have Issues with Ordering

If your query includes an ORDER Past clause, and so your column headings won't evidence correctly.

This is considering the ORDER BY clause goes at the end of the query, and will include your row of column headers in the ordering. This could mean that your cavalcade headers will end up at a place in the results that is non the meridian.

Y'all could go effectually this past putting your principal query in a subquery and so using Wedlock on that.

For example:

                SELECT * FROM  (   SELECT 'id', 'first_name', 'last_name'   UNION ALL   (     SELECT id, first_name, last_name     FROM customer     Club BY first_name ASC   ) ) sub INTO OUTFILE '/temp/myoutput.txt';

This should ensure that your cavalcade headers are shown at the top.

Possible Performance Issues

If you try to use a Marriage or UNION ALL, the MySQL database may try to use a different execution plan to brandish the data, even though you're only calculation a unmarried row.

This could mean the runtime is a lot slower.

So, information technology's something yous should examination before you lot kickoff using this as a permanent solution.

What if the File Already Exists?

So, what happens if you try this command and the file already exists?

For example, bold the myoutput.txt file exists, you run this command:

                SELECT id, first_name, last_name FROM customer INTO OUTFILE '/temp/myoutput.txt' FIELDS TERMINATED BY ',' ENCLOSED By '"' LINES TERMINATED By '\n';

Yous'll become this message:

Mistake Code: 1086. File '/temp/myoutput.txt' already exists

This is pretty clear. The file already exists. Then you need to use a filename that does not exist.

Error With Secure-File-Priv

Are you running this SELECT INTO OUTFILE and getting an error near "secure-file-priv"?

Error Code: 1290. The MySQL server is running with the –secure-file-priv pick then information technology cannot execute this statement

This substantially means that MySQL has a specific set of directories that can be used for input and output, and the file nosotros're attempting to write is not in that directory.

To resolve this, we need to:

Find out what the specific directories are
Add this directory path to our OUTFILE parameter and so our file is generated there.

To find out where the directories are, we can run either of these 2 commands:

                SELECT @@GLOBAL.secure_file_priv;

@@GLOBAL.secure_file_priv

/usr/files/

                SHOW VARIABLES LIKE "secure_file_priv";

Variable_name	Value
secure_file_priv	/usr/files/

The output in this instance shows the value to be '/usr/files/'.

So, all you demand to do is add together this path to the start of the OUTFILE path.

Your SELECT statement would then await like this:

                SELECT id, first_name, last_name FROM client INTO OUTFILE '/usr/files/temp/client.txt'

Secure-File-Priv is NULL

If you have the mistake above, and you look for the secure_file_priv variable, yous may detect it is Cipher. This happens most often on MacOS with MAMP.

                Testify VARIABLES LIKE "secure_file_priv";

Variable_name	Value
secure_file_priv	Zilch

To resolve this, nosotros need to add this variable to the my.cnf file.

Beginning, stop your MAMP server past opening MAMP and clicking End.

Open a Terminal window.

Enter in this command:

                vi ~/.my.cnf

Your screen should look similar this.

Press Enter to run the command. This opens up an editor for the file my.cnf.

Copy the post-obit lines into the Terminal window:

                [mysqld_safe]                [mysqld]                secure_file_priv="/Users/BB/"

(Replace the letters BB with any your user folder is)

Press the : key and the cursor will be moved to the bottom.

Enter wq later on the : which will write to the file and quit.

Press Enter, and you will return to the normal Last window.

Now, start MAMP over again.

You can test the variable was set up by running one of the commands from earlier:

                SHOW VARIABLES Similar "secure_file_priv";

Variable_name	Value
secure_file_priv	/Users/BB/

At present, update your SELECT INTO statement to add the value hither to the path of the OUTFILE:

                SELECT id, first_name, last_name FROM customer INTO OUTFILE '/Users/BB/customer.txt';

The file will now be generated.

Conclusion

And so that's how you can generate a text or CSV file from the results of a MySQL query. You lot add an INTO OUTFILE parameter to the stop of your SELECT query.

If there are whatever problems with error messages, they are normally permission-related.

hayesthiskence.blogspot.com

Source: https://www.databasestar.com/mysql-output-file/