Mysql data import and export, data table optimization, mysql Import and Export

Mysql data import and export, data table optimization, mysql Import and Export

I. Data Import

Mysqlimport-uroot oa d:/aa.txt -- fields-terminated-by =, -- fields-optionally-enclosed-by = -- lines-terminated-by = \ r \ n

Or

Load data infile 'd:/students.txt 'into table stu fields terminated by '\, 'Lines terminated by' \ N'

Aa indicates the name of the data table to be imported, fields-terminated-by indicates the field separator, and fields-optionally-enclosed-by indicates the delimiters of each field, lines-terminated-by indicates the end mark of a record.

Ii. Data Export

Seect * into outfile 'd:/all.txt 'from students;

Or

Seect * into outfile 'd:/all.csv 'character uft8 fields terminated by', 'Lines terminated by' \ r \ n' from students;

Iii. Optimize and repair tables

With the long-term use of mysql, some problems will certainly occur. For example, if the table cannot be accessed, you can fix the table and reduce disk space usage during optimization.

1. Optimization

Optimize is used to recycle idle database space. When the data rows in the table are deleted, the occupied disk space is not immediately recycled. After the optimize command is used, the space will be recycled, in addition, the data rows on the disk are rearranged (note that they are on the disk rather than the database ). Optimize does not need to be run most of the time. You only need to perform a data table optimization operation once a week or once a month after batch deletion of data rows.

Optimize table table_name1, table_name2

2. Repair the table

Repair table table_name, table_name2