mysql自带的导出功能:
-
select * from test
into outfile '/var/lib/mysql/t.csv'
-
fields terminated by ','
-
optionally enclosed by '"'
-
lines terminated by '\r\n';
要使用这个功能,数据库用户必须要赋予FILE权限,而且貌
似只能在数据库服务器上操作(远程我没有试验成功,不知道是否可以)
为了避开以上的限制,使用了变通的方法,就是使用mysql命令将待导出的数据select出来,再使用sed命令配合正则表达式,将结果集修改为需要的csv格式后,重定向到输出文件!
第一步,先测试输出结果的格式,测试表test,使用默认命令输出如下:
-
-bash-4.1$ mysql -uopenser -popenser -h192.168.21.56 --database test -e "select * from t;"
-
+-------+------+------+
-
| staff | good | num |
-
+-------+------+------+
-
| a | 甲 | 2 |
-
| b | 乙 | 4 |
-
| c | 丙 | 1 |
-
| a | 乙 | 2 |
-
| b | 丙 | 5 |
-
| f | a | 2 |
-
+-------+------+------+
可以发现mysql的默认输出格式,包含了列名和一些格式分隔符,要去掉表头,可以使用-N参数:
-
-bash-4.1$ mysql -uopenser -popenser -h192.168.21.56 --database test -N -e "select * from t;"
-
+------+------+------+
-
| a | 甲 | 2 |
-
| b | 乙 | 4 |
-
| c | 丙 | 1 |
-
| a | 乙 | 2 |
-
| b | 丙 | 5 |
-
| f | a | 2 |
-
+------+------+------+
表头已经去掉了,但是其它无用的字符如何处理呢,显然-N达不到目的了,这里使用-s参数,先试试
-
-bash-4.1$ mysql -uopenser -popenser -h192.168.21.56 --database test -s -e "select * from t;"
-
staff good num
-
a 甲 2
-
b 乙 4
-
c 丙 1
-
a 乙 2
-
b 丙 5
-
f a 2
呵呵,那些杂乱的字符不见了,但是表头又回来了,看到这里,我们就知道,再把刚才那个-N参数加上就行了
-
-bash-4.1$ mysql -uopenser -popenser -h192.168.21.56 --database test -s -N -e "select * from t;"
-
a 甲 2
-
b 乙 4
-
c 丙 1
-
a 乙 2
-
b 丙 5
-
f a 2
嗯,这样的数据基本差不多了,不过,要实现上一步,-s参数也能做到的,那就是再加一个"s",使用"-ss",也就是更精简的输出:
-
-bash-4.1$ mysql -uopenser -popenser -h192.168.21.56 --database test -ss -e "select * from t;"
-
a 甲 2
-
b 乙 4
-
c 丙 1
-
a 乙 2
-
b 丙 5
-
f a 2
三个s会怎样呢?呵呵,你试试看吧!这样,我们需要的数据已经出来了,这里说一个在测试过程中发现的意外的情况,还没想清楚是什么原因,就是分别使用"-N"和"-s"参数时,如果将结果集重定向到文件,得到的结果是一样的:
-
-bash-4.1$ mysql -uopenser -popenser -h192.168.21.56 --database test -N -e "select * from t;"
-
+------+------+------+
-
| a | 甲 | 2 |
-
| b | 乙 | 4 |
-
| c | 丙 | 1 |
-
| a | 乙 | 2 |
-
| b | 丙 | 5 |
-
| f | a | 2 |
-
+------+------+------+
-
-bash-4.1$ mysql -uopenser -popenser -h192.168.21.56 --database test -N -e "select * from t;" > t.csv
-
-bash-4.1$ cat t.csv
-
a 甲 2
-
b 乙 4
-
c 丙 1
-
a 乙 2
-
b 丙 5
-
f a 2
-
-bash-4.1$ mysql -uopenser -popenser -h192.168.21.56 --database test -s -e "select * from t;"
-
staff good num
-
a 甲 2
-
b 乙 4
-
c 丙 1
-
a 乙 2
-
b 丙 5
-
f a 2
-
-bash-4.1$ mysql -uopenser -openser -h192.168.21.56 --database test -s -e "select * from t;" > t.csv
-
-bash-4.1$ cat t.csv
-
a 甲 2
-
b 乙 4
-
c 丙 1
-
a 乙 2
-
b 丙 5
-
f a 2
数据已经出来了,下一步就是如何格式化为csv格式,这里要用到linux下的sed命令:
-
-bash-4.1$ mysql -uopenser -popenser -h192.168.21.56 --database test -ss -e "select * from t;"|sed 's/\t/","/g;s/^/"/g;s/$/"\r/g'
-
"a","甲","2"
-
"b","乙","4"
-
"c","丙","1"
-
"a","乙","2"
-
"b","丙","5"
-
"f","a","2"
大功即将告成,sed命令用到了正则表达式,简单说下,我们得到的数据,每个字段之间是用制表符"\t"分隔的,所以第一个表达式s/\t/","/g 把所有的制表符替换为 ",",然后第二个表达式,将每行的开头加上双引号,第三个表达式将每行的结尾添加一个双引号和一个回车符"\r"(原始数据每行结尾只有一个换行符"\n",sed命令是处理去掉回车符后的字符串),这样,最后一步,把得到的文本输出到文件:
-
-bash-4.1$ mysql -uopenser -popenser -h192.168.21.56 --database test -ss -e "select * from t;"|sed 's/\t/","/g;s/^/"/g;s/$/"\r/g' > t.csv
-
-bash-4.1$ cat t.csv
-
"a","甲","2"
-
"b","乙","4"
-
"c","丙","1"
-
"a","乙","2"
-
"b","丙","5"
-
"f","a","2"
OK!
阅读(9003) | 评论(0) | 转发(2) |