Two ways to export csv in win10 mysql

There are two ways to export csv in win10. The first is to use tools. Navicat for Mysql is a very easy-to-use mysql visualization tool that can export data in multiple formats, but it also has its limitations. The second is to export raw SQL statements.

Navicat For Mysql

Install the software, there is a green free version here, which is very easy to use, link: https://pan.baidu.com/s/1HZNzq_16M5yrq9hAMfNOmA extraction code: 848h. Open the software, establish a database connection, then select the corresponding table, right-click -> Export Wizard, and select the format.

SQL Export

There are several places where things can go wrong, so I'll list them here first.

1. The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

2. SELECT list is not in GROUP BY clause

3. Exporting does not include headers

operate

1. In the MySQL installation directory, find my.ini. If it does not exist, create this file. The mysql5.7 installed on my computer did not have this file, so I created this file and installed it in the mysql directory on drive D.

2. Modify the my.ini file. If the file exists, modify it. If it does not exist, add it directly. Add secure_file_priv='' under [mysqld]

The above two steps are to solve the problem of secure-file-priv. When you reach this point, you need to restart the database service. This computer -> right-click -> Manage -> Services and Applications -> Services -> Mysql -> right-click -> Restart.

3. In Navicat For Mysql, create a query under the corresponding database (this is not necessary, it is just for the convenience of writing SQL queries). Since Mysql5.7 default ONLY_FULL_GROUP_BY semantics were introduced.

implement

SELECT @@global.sql_mode

Results will appear

Then you can see ONLY_FULL_GROUP_BY. Copy the result and remove the ONLY_FULL_GROUP_BY field. Then run SQL

SET sql_mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"
The content of sql_mode is the content left after removing ONLY_FULL_GROUP_BY.

4. Export data

SELECT 'id','name','color' FROM product UNION
SELECT `id`,`name`,`color` FROM product
 INTO OUTFILE 'product.csv' 
 FIELDS TERMINATED BY ';' 
 OPTIONALLY ENCLOSED BY '"' 
 LINES TERMINATED BY '\n';

The first line indicates that a table header needs to be added, and is enclosed in quotation marks. The second line is the corresponding field of the query. The third line is the exported file name. What symbol is used to separate the fourth line? The fifth line sets the field enclosed by symbols. The sixth line is the division of each line.

5. The data will eventually be exported to the data->databaaseName directory under the MySQL installation directory.

Summarize

The above are two ways to export csv from win10 mysql that I introduced to you. I hope it will be helpful to you. If you have any questions, please leave me a message and I will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!
If you find this article helpful, please feel free to reprint it and please indicate the source. Thank you!