Introduction to the method of exporting and importing data by Mysql

This article mainly introduces "Mysql export import data method introduction", in the daily operation, I believe many people in Mysql export import data method introduction problem there are doubts, Xiaobian consulted all kinds of information, sorted out simple and easy to use operation method, hope to answer "Mysql export import data method introduction" doubts helpful! Next, please follow the small series to learn together!

export data

** Execute commands directly in the system:

Export to cvs format:

[root@slave ~]# mysql -u root -p Password-e "select * from mydb.name into outfile '/tmp/12.cvs' character set utf8" --character set utf8 Specify the corresponding encoding rules, otherwise it may be garbled

Export to txt format:

[root@slave ~]# mysql -u root -p password-e "select * from mydb.name into outfile '/tmp/12.txt' character set utf8 lines terminated by '\r\n' "--When exported to txt file, without lines terminated by '\r\n' (ending character) will be like a ball of hemp under windows...

In mysql:

Export to cvs format:

mysql> select * from mydb.name into outfile '/tmp/12.cvs' character set utf8; - --character set utf8 Specify the corresponding encoding rule, otherwise it may be garbled

Export to txt format:

mysql> select * from mydb.name into outfile '/tmp/12.txt' character set utf8 lines terminated by '\r\n'; --When exporting to txt file, without lines terminated by '\r\n' (terminator) Under windows it will look like a ball of hemp...

Export to cvs format, open with WPS, quite perfect!

import data

mysql import:

mysql>load data infile '/tmp/12.txt' into table name character set uft8; --character set utf8 Specify the corresponding encoding rule, otherwise it may be garbled

Execute commands directly in the system:

[root@slave ~]# mysql -u root -p password-e load data infile '/tmp/12.txt' into table name character set uft8; --character set utf8 Specify the corresponding encoding rule, otherwise it may be garbled

Encountered a problem:emun type field, does not import data???

Solution:

Import, export add two parameters: fields terminated by and optionally enclosed by

mysql> select * from name into outfile '/tmp/11.txt' character set utf8 fields terminated by '\、' optionally enclosed by '\"' ;

mysql> load data infile '/tmp/11.txt' into table nn character set utf8 fields terminated by '\、' optionally enclosed by '\"' ;

At this point, the study on "Mysql export import data method introduction" is over, I hope to solve everyone's doubts. Theory and practice can better match to help you learn, go and try it! If you want to continue learning more relevant knowledge, please continue to pay attention to the website, Xiaobian will continue to strive to bring more practical articles for everyone!