Mysql导出/导入数据

导出数据

**直接在系统执行命令:

导出为cvs格式:

[root@slave ~]# mysql -u root -p密码 -e "select * from mydb.name into outfile '/tmp/12.cvs' character set utf8" --character set utf8 指定相应编码规则,不然可能乱码

导出为txt格式:

[root@slave ~]# mysql -u root -p密码 -e "select * from mydb.name into outfile '/tmp/12.txt' character set utf8 lines terminated by '\r\n' " --导出为txt文件时,不加lines terminated by '\r\n' (结尾符)在windows下会像一团麻一样.....

在mysql内:

mysql>selec t * from mydb.name into outfile '/tmp/12.cvs' character set utf8; - --character set utf8 指定相应编码规则,不然可能乱码

mysql> select * from mydb.name into outfile '/tmp/12.txt' character set utf8 lines terminated by '\r\n'; --导出为txt文件时,不加 lines terminated by '\r\n' (结尾符)在windows下会像一团麻一样.....

导出为cvs格式后,用WPS打开,相当的完美!

导入数据

mysql内导入:

mysql>load data infile '/tmp/12.txt' into table name character set uft8; --character set utf8 指定相应编码规则,不然可能乱码

直接在系统执行命令:

[root@slave ~]# mysql -u root -p密码 -e load data infile '/tmp/12.txt' into table name character set uft8; --character set utf8 指定相应编码规则,不然可能乱码

遇到一个问题:emun类型的字段,导不入数据 ???

解决方法:

导入,导出时添加两个参数: fields terminated by 和 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 '\"' ;

      本文转自笔下生辉  51CTO博客，原文链接：http://blog.51cto.com/752030200/2073539，如需转载请自行联系原作者