Detailed explanation of several ways to export data in Mysql

There are many purposes for exporting MySQL data, such as database backup, table structure export, table data export, and data analysis.

Part1 select into outfile

Let's start with the shortest and most powerful select into outfile, which is the most commonly used data collection method for small database analysis. The specific syntax is as follows:
[select statement] into outfile [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 of the target file. Due to the permission problem of MySQL account, we usually export the file to a temporary directory, such as /tmp/mysql/user/201810.csv

【Export parameters】

• fields terminated by 'str': Set the separator between fields. The default value is "\t".
• fields enclosed by 'char': Set the symbols that enclose the field value, such as single quotes, double quotes, etc. By default, no symbols are used.
• fields optionally enclosed by 'char': Sets the delimiter for character fields such as CHAR, VARCHAR, and TEXT. By default, no symbol is used.
• fields escaped by 'char': Set the escape character, the default value is "\".
• lines starting by 'str': Set the character at the beginning of each line of data, which can be a single character or multiple characters. By default no characters are used.
• lines terminated by 'char': Set the character at the end of each line of data, which can be a single character 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 characters, you may see garbled characters when you open the csv file. To handle garbled characters, first make sure that the database supports Chinese (usually set UTF8 encoding)

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 already in utf8, the exported file may still contain garbled characters when downloaded locally. Are the results seen by less and tail on the server normal?
Open the csv file in the local environment (windows) with Notepad and save it in ANSI encoding format. This is determined by the encoding format of Excel.

Part 2 mysqldump export data

As a developer or operation and maintenance personnel, mysqldump is used more frequently because it can do more things. mysqldump is a logical backup tool because it exports results, mostly in the form of SQL, and does not record the data change process. Regarding physical backup, you can refer to a book called "MySQL Technology Insider", which has a detailed description.

The syntax of mysqldump is very simple, namely

mysqldump [options]> dump.sql

However, there are many optional parameters for options. The manual divides them into several categories, including 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 in the MYSQL official manual. Here we only introduce several common application scenarios.

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.