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

This article mainly explains how to achieve the import and export of MySQL table data, the content is clear, interested friends can learn, I believe it will be helpful after reading.

Data export

1. Use SELECT... INTO OUTFILE... Command to export the data, the syntax is as follows.

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

Where the option parameter can be the following options:

FIELDS TEMINATED BY 'string' (character breaker)

FIELDS [OPTIONALLY] ENCLOSED BY 'CHAR' (field reference, if you add the OPTIONAL option, it is only used on char, varchar, and text character fields. Do not use references by default)

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

LINES STARTING BY 'string' (this string is preceded by each line, default')

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

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

Example: export all data from the test table mysql > select * from test into outfile'/ path/files.txt'

two。 Export data as text with mysqldump

Mysqldump-u username-T target_dir dbname tablename [option] where 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, which is only used on character-type fields such as char, varchar, and text);-- fields-escaped-by=name (escape character);-- lines-terminated-by=name (record Terminator). Example: export all data of custom table in test database mysqldump-uroot-T / tmp test custom

In addition to generating the data file custom.txt, it also generates a custom.sql file that records the script for creating the custom table.

Data import

Only talk about using SELECT... The import method of plain data text exported by INTO OUTFILE or mysqldump.

1. Use "LOAD DATA INFILE..."

Mysql > LOAD DATA [LOCAL] INFILE 'filename' into TABLE tablename [option] option can be the following options: "FIELDS TERMINATED BY' string' (field delimiter, default tab't');" FIELDS [OPTIONALLY] ENCLOSED BY 'char' (field reference, if you add the OPTIONALLY option, it is only used on character fields such as char, varchar, and text.) Do not use references by default); ESCAPED BY 'char' (escape characters, default is'\'); STARTING BY 'string' (preceded by this string before each line, default''); 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,...) (load data according to the order and number of fields listed); SET col_name = expr,... Convert the column to a certain number before loading. Where char means that the symbol can only be a single character, and string means it can be a string. FILELD and LINES and the front SELECT... INTO OUTFILE... The following example loads the data from 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 imported columns:

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

two。 It is implemented with mysqlimport. The specific commands are as follows:

Shell > mysqlimport-uroot-paired imports * [--LOCAL] dbname order_tab.txt [option] Example: import data into order table shell > mysqlimport-uroot test / tmp/emp.txt

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

Clear the table data

Delete from table name

Truncate table table name

A delete statement without a where parameter can delete everything in the mysql table, or you can use truncate table to empty everything in the mysql table.

Truncate is faster than delete in efficiency, but mysql logs are not recorded after truncate deletion, and data can not be recovered.

The effect of delete is a bit like deleting all the records in the mysql table until it is deleted.

Truncate, on the other hand, is equivalent to preserving the structure of the mysql table and recreating the table, with all the states equivalent to the new table.

After reading the above content, do you have a further understanding of how to achieve the import and export of MySQL data? if you want to learn more, welcome to follow the industry information channel.

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.