解决方法:
先导出为txt文件,其内容是以逗号“,”分隔的,得到txt文件后,再自行处理为.csv或者.xls文件。
参数说明:
-t, --no-create-info Don't write table creation info.
-T, --tab=name Create tab-separated textfile for each table to given path. (Create .sql and .txt files.) NOTE: This only works if mysqldump is run on the same machine as the mysqld server.
--fields-terminated-by=name Fields in the output file are terminated by the given string.
--fields-enclosed-by=name Fields in the output file are enclosed by the given character.
1. 先查看可导出位置(不然会涉及到权限问题)
mysql> show variables like 'secure_file_priv'; +------------------+-----------------------+ | Variable_name | Value | +------------------+-----------------------+ | secure_file_priv | /var/lib/mysql-files/ | //如果出现权限问题,尝试把该目录所有者改成mysql +------------------+-----------------------+ 1 row in set (0.00 sec)
2. 执行导出命令
[root@server-10 ~]# mysqldump -uroot -p -t -T /var/lib/mysql-files/ mydb customers --fields-terminated-by=',' --fields-enclosed-by='"'
3. 查看一下
[root@server-10 ~]# ls -l /var/lib/mysql-files/ total 4 -rw-r--r-- 1 root root 0 Aug 28 15:35 customers.sql //会同步生成同名sql文件,内容为空 -rw-rw-rw- 1 mysql mysql 458 Aug 28 15:35 customers.txt [root@server-10 ~]# cat /var/lib/mysql-files/customers.txt "10001","Coyote Inc.","200 Maple Lane","Detroit","MI","44444","USA","Y Lee","ylee@coyote.com" "10002","Mouse House","333 Fromage Lane","Columbus","OH","43333","USA","Jerry Mouse",N "10003","Wascals","1 Sunny Place","Muncie","IN","42222","USA","Jim Jones","rabbit@wascally.com" "10004","Yosemite Place","829 Riverside Drive","Phoenix","AZ","88888","USA","Y Sam","sam@yosemite.com" "10005","E Fudd","4545 53rd Street","Chicago","IL","54545","USA","E Fudd",N
4. 如果不想每个字段带冒号,则省略--fields-enclosed-by='"'即可
[root@server-10 ~]# mysqldump -uroot -p -t -T /var/lib/mysql-files/ mydb customers --fields-terminated-by=','
5. 再查看一下输出的变化
[root@server-10 ~]# ls -l /var/lib/mysql-files/ total 4 -rw-r--r-- 1 root root 0 Aug 28 15:35 customers.sql -rw-rw-rw- 1 mysql mysql 372 Aug 28 15:36 customers.txt [root@server-10 ~]# cat /var/lib/mysql-files/customers.txt 10001,Coyote Inc.,200 Maple Lane,Detroit,MI,44444,USA,Y Lee,ylee@coyote.com 10002,Mouse House,333 Fromage Lane,Columbus,OH,43333,USA,Jerry Mouse,N 10003,Wascals,1 Sunny Place,Muncie,IN,42222,USA,Jim Jones,rabbit@wascally.com 10004,Yosemite Place,829 Riverside Drive,Phoenix,AZ,88888,USA,Y Sam,sam@yosemite.com 10005,E Fudd,4545 53rd Street,Chicago,IL,54545,USA,E Fudd,N
结束.