在使用mysql的into outfile命令将数据库数据导出为csv、excel时,发现数据可以导出,但是列名却没有,但是却可以通过修改sql语句的方法加上表头,具体实现方法是在查找数据的时候并上列名:
不带表头
select uid,nickname,realname,mobile,idcard from m order by m.uid desc into outfile '/var/www/api/public/a/download/".$file_name."' fields terminated by ',' optionally enclosed by '\"' escaped by '\"' lines terminated by '\r\n'
带上表头
select * into outfile 'e:/555.xlsx' fields terminated by '\\t' OPTIONALLY ENCLOSED BY '"' lines terminated by '\\n' from (select '姓名','性别','年龄' union select username,sex,age from table) b;
使用mysql导入
LOAD DATA INFILE '/tmp/fi.txt' INTO TABLE test.fii FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'
而为了满足某种特别的需求,会采用不同的分割方式。支持,在导出数据时,设置记录,与字段的分隔符。通过如下的选项:
fields:设置字段选项 Lines:设置行选项(记录选项)
字段:field terminated by '\t'
记录:lines terminated by '\n' starting by ''(每一行开始是空字符串);
FIELDS TERMINATED BY ',' 字段间分割符
OPTIONALLY ENCLOSED BY '"' 将字段包围 对数值型无效
LINES TERMINATED BY '\n' 换行符
注:常规的,所有的记录,应该通过行来显示,例外是保存二进制数据:
Blob binary
使用into dumpfile
select * into dumpfile 'f:/mysql/test/six' from teacher_class limit 1;