Shulou(Shulou.com)06/01 Report--

There are two ways for win10 to export csv. The first is with the help of 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 the original SQL statement export.

Navicat For Mysql

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

SQL export

There are a few things that can go wrong. List them first.

1pm 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. It is difficult to export with the header.

Operation

1. Under the installation directory of mysql, find my.ini, and if not, create this file. The mysql5.7 installed on my computer doesn't have this file, so I created it, and I installed it in the mysql directory on disk D.

2. Modify the my.ini file. If this file exists, it will be modified. If it does not exist, it will be added directly. Add secure_file_priv='' under [mysqld]

The above two steps are to solve the problem of secure-file-priv. To execute to this place, you need to restart the database service. This computer-> right-click-> Administration-> Services and applications-> Services-> Mysql- > right-> restart.

3. In Navicat For Mysql, create a query under the corresponding database (this is not required, but it is convenient to write SQL queries). Due to the default ONLY_FULL_GROUP_BY semantic introduction of Mysql5.7.

Execution

SELECT @ @ global.sql_mode

There will be results.

Then you can see that there is ONLY_FULL_GROUP_BY, and we copy the result out and remove the ONLY_FULL_GROUP_BY field. Then run SQL

The content of the SET sql_mode= "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION" sql_mode is to remove the rest of the ONLY_FULL_GROUP_BY.

4, export data

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

The first line indicates that the header needs to be added, using quotation marks. The second row is the corresponding field for the query. The third line is the exported file name. The fourth line is divided by what symbol. The fifth line is the symbol surrounded by the setting field. 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.

Summary

The above is the editor to introduce to you the two ways of win10 mysql export csv, I hope to help you, if you have any questions, please leave me a message, the editor will reply to you in time. Thank you very much for your support to the website!

If you think this article is helpful to you, you are welcome to reprint it, please indicate the source, thank you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.