mysql导出csv文件

不带表头

ｓｅｌｅｃｔ * from table_name

INTO ｏｕｔｆｉｌｅ ‘/var/lib/mysql-files/train1.csv‘

character set gbk

FIELDS ENCLOSED BY ‘"‘

TERMINATED BY ‘,‘

ESCAPED BY ‘"‘

LINES TERMINATED BY ‘\r\n‘;

带表头：

ｓｅｌｅｃｔ * from (

ｓｅｌｅｃｔ ‘日期‘,‘platform‘

union

ｓｅｌｅｃｔ DATE_FORMAT(createTime,‘%Y-%m-%d‘),platform from table_name limit 2) b

INTO ｏｕｔｆｉｌｅ ‘/var/lib/mysql-files/train2.csv‘

character set gbk

FIELDS ENCLOSED BY ‘"‘

TERMINATED BY ‘,‘

ESCAPED BY ‘"‘

LINES TERMINATED BY ‘\r\n‘;

解决中文乱码的问题：

character set gbk

参数：

terminated by分隔符：意思是以什么字符作为分隔符enclosed by字段括起字符escaped by转义字符

terminated by描述字段的分隔符，默认情况下是tab字符（\t） enclosed by描述的是字段的括起字符。escaped by描述的转义字符。默认的是反斜杠（backslash：\ ）

导出xls文件能打开，但是会显示文件损坏

ｓｅｌｅｃｔ * from (

ｓｅｌｅｃｔ ‘日期‘,‘platform‘

union

ｓｅｌｅｃｔ DATE_FORMAT(createTime,‘%Y-%m-%d‘),platform from table_name limit 2) b

INTO ｏｕｔｆｉｌｅ ‘/var/lib/mysql-files/train6.xls‘

character set gbk ;

或

ｓｅｌｅｃｔ * from (

ｓｅｌｅｃｔ convert(‘日期‘using gbk) ,convert(‘pl‘using gbk)

union

ｓｅｌｅｃｔ convert(DATE_FORMAT(createTime,‘%Y-%m-%d‘) using gbk), convert(platform using gbk) from wo_register limit 2) b

INTO ｏｕｔｆｉｌｅ ‘/var/lib/mysql-files/train7.xls‘

导入文件汇总指令load data infile ‘/var/lib/mysql-files/train7.xls‘ into table student2 character set gbk;load data infile ‘/var/lib/mysql-files/train7.txt‘ into table student2；