Examples of importing and exporting MySQL table data

This article describes the import and export operations of MySQL table data. Share with you for your reference, the details are as follows:

Data Export

1. Use the SELECT ... INTO OUTFILE ... command to export data. The specific syntax is as follows.

mysql> SELECT * FROM tablename INTO OUTFILE 'target_file' [option];

The option parameter can be the following options:

FIELDS TEMINATED BY 'string' (character delimiter)

FIELDS [OPTIONALLY] ENCLOSED BY 'CHAR' (field reference character. If the OPTIONAL option is added, it is only used for character fields such as char, varchar and text. The reference character is not used by default)

FIELDS EXCAPED BY 'CAHR' (escape character, default is '\')

LINES STARTING BY 'string' (add this string before each line, default '')

LINES TERMINATED BY 'string' (line terminator, default is '\n')

char means that the symbol can only be a single character, and string means that it can be a string.

Example: Export all data of the test tablemysql> select * from test into outfile '/path/files.txt'

2. Use mysqldump to export data as text

mysqldump -u username -T target_dir dbname tablename [option]

The option parameter can be the following options:
--fields-terminated-by=name(field separator);
--fields-enclosed-by=name(field reference);
--fields-optionally-enclosed-by=name (field reference symbol, only used on character fields such as char, varchar and text);
--fields-escaped-by=name(escape character);
--lines-terminated-by=name (record terminator).

Example: Export all data of the custom table in the test database mysqldump -uroot -T /tmp test custom

In addition to generating the data file custom.txt, a custom.sql file is also generated, which records the creation script of the custom table.

Data Import

Only the import method of plain data text exported by SELECT... INTO OUTFILE or mysqldump is discussed.

1. Use "LOAD DATA INFILE..."

mysql > LOAD DATA [LOCAL] INFILE 'filename' into TABLE tablename [option]

option can be one of the following options:
¡ FIELDS TERMINATED BY 'string' (field separator, default is tab '\t');
¡ FIELDS [OPTIONALLY] ENCLOSED BY 'char' (field reference character. If the OPTIONALLY option is added, it is only used on character fields such as char, varchar and text. The reference character is not used by default);
ESCAPED BY 'char' (escape character, default is '\'); STARTING BY 'string' (add this string before each line, default is ''); TERMINATED BY 'string' (line terminator, default is '\n');
¡ FIELDS
¡ LINES
¡ LINES
¡ IGNORE number LINES (ignore the first n lines of data in the input file);
¡ (col_name_or_user_var,...) (loads data in the order and number of fields listed); ¡ SET col_name = expr,... Perform certain numerical conversion on the column before loading it.
The char indicates that the symbol can only be a single character, and the string indicates that it can be a string.
FILELD and LINES have exactly the same meaning as the previous SELECT ...INTO OUTFILE..., except that there are several more options. The following example loads the data in the file "/tmp/emp.txt" into the table emp:

mysql > load data infile '/tmp/emp.txt' into table emp

If you do not want to load the first two lines in the file, you can do the following:

mysql> load data infile '/tmp/emp.txt' into table emp fields ignore 2 lines;

 Specify the columns to import:

 mysql > load data infile '/tmp/emp.txt' into table emp ignore 2 lines (id,content,name);

2. Use mysqlimport to implement it. The specific commands are as follows:

shell > mysqlimport -u root -p*** [--LOCAL] dbname order_tab.txt [option]

Example: Import data into the order tableshell > mysqlimport -uroot test /tmp/emp.txt

Note: If the import and export are cross-platform operations (Windows and Linux), then pay attention to setting the parameter line-terminated-by. Set it to line-terminated-by='\r\n' on Windows and line-terminated-by='\n' on Linux.

Clear table data

delete from table name;

truncate table table name;

The delete statement without the where parameter can delete all the contents in the MySQL table. Using truncate table can also clear all the contents in the MySQL table.

In terms of efficiency, truncate is faster than delete, but truncate does not record MySQL logs after deletion, and data cannot be recovered.

The effect of delete is a bit like deleting all the records in the MySQL table one by one until all are deleted.

Truncate is equivalent to retaining the structure of the MySQL table and recreating the table, and all states are equivalent to the new table.

Readers who are interested in more MySQL-related content can check out the following topics on this site: "MySQL query skills", "MySQL transaction operation skills", "MySQL stored procedure skills", "MySQL database lock related skills summary" and "MySQL common function summary"

I hope this article will be helpful to everyone's MySQL database design.