Shulou(Shulou.com)05/31 Report--

This article introduces you to Mysql which export data, the content is very detailed, interested friends can refer to, I hope to help you.

The syntax is as follows:

[select statement] into [export file name][export parameters]

[select statement] is a classic query SQL, which can specify columns, where conditions, group, order, limit, etc.

Export File Name is the full path to the destination file. Due to permissions issues with mysql accounts, we usually export files to temporary directories such as/tmp/mysql/user/201810.csv

[Export Parameters]

fields terminated by 'str': Sets the delimiter between fields, default is "\t".

fields enclosed by 'char': Sets the symbols that include the value of the field, such as single quotes, double quotes, etc., without using any symbols by default.

fields optionally enclosed by 'char': Sets delimiters to enclose character-type fields such as CHAR, VARCHAR, and TEXT without using any symbols by default.

fields escaped by 'char': Sets escape characters, default is'\'.

lines starting by 'str': Sets the character at the beginning of each line of data, either single or multiple characters. No characters are used by default.

lines terminated by 'char': Sets the character at the end of each line of data, either single or multiple characters. The default value is "\n".

For example:

select * from platform_user into outfile '/tmp/mysql/user/201810.csv' fields terminated by ',' enclosed by '"'lines starting by '\r' terminated by '\n';

If the exported data involves Chinese, open csv and you may see garbled characters. To deal with garbled code, first make sure that the database supports Chinese (usually set UTF8 code)

vim /etc/my.cnf, add the following options:

[client]default-character-set=utf8[mysqld]character_set_server=utf8[mysql]default-character-set=utf8

Even if the database is utf8, the exported file download may still have garbled characters locally, and the results seen on the server are normal?

In the local environment (windows) with notepad open csv, save the encoding format ANSI can be, this is due to excel encoding format decision.

Part2 mysqldump export data

As developers or operations people, mysqldump is used more frequently because it can do more. mysqldump is a logical backup tool because it exports results, mostly in the form of SQL, and does not record the process of data change. For a detailed description of physical backups, refer to a book called MySQL Technology Insider.

The syntax of mysqldump is simple, namely

mysqldump 【options】> dump.sql

However, there are many optional parameters of options. The manual divides them into several categories, such as link option, file option, data definition DDL option, Debug option, internationalization option, cluster Replication option, format option, performance option, transaction option, etc. Interested students can refer to the definition of MYSQL official manual, here are only a few common application scenarios.

About Mysql, there are ways to export data to share here, I hope the above content can be of some help to everyone, you can learn more knowledge. If you think the article is good, you can share it so that more people can see it.

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.