sql 示例，这是一个查询订单导出到/tmp/order.csv文件的sql语句

select * into outfile '/tmp/order.csv' fields terminated by ',' lines terminated by '\n' from(
select '订单号', '用户', '总计', '小计','运费','优惠','使用积分','商家实收','收件人','类型','运单号','状态','创建日期','收件人地址','商品' ,'规格' ,'数量','单价','商品小计'  union 
select 
concat('\t',convert(db_orders.code using gbk)) as '订单号', 
convert(db_user.name using gbk) as '用户' ,
convert(db_orders.grand_total using gbk)as '总计' , 
convert(db_orders.subtotal using gbk) as '小计' ,
convert(db_orders.freight_price using gbk) as '运费' , 
convert(db_orders.shop_coupon_saving using gbk) as '优惠' ,
convert(db_orders.integral using gbk) as '使用积分',
convert(db_orders.shop_grand_total using gbk) as '商家实收',
convert(CONCAT_WS('|',db_orders.take_name ,db_orders.take_telephone) using gbk) as '收件人' ,  
convert(case  db_orders.peisong   
	when  1 then '物流配送'  
	when 2 then '同城自提'  
	end  using gbk) as '类型',
convert(db_orders.tracking_code using gbk) as '运单号',   
if(db_orders.closed=1,convert('已关闭' using gbk),convert(case  db_orders.status  
	when 0 then '待付款' 
	when 1 then '待发货' 
	when 2 then '已发货' 
	when 3 then '已完成'
	when 8 then '已收货' 
	end using gbk) ) as '状态',
convert(db_orders.create_date using gbk) as '创建日期',
convert(CONCAT_WS('|',CONCAT_WS('|',db_orders.take_area_msg,  REPLACE(db_orders.take_address,CHAR(10),'')) ,db_orders.take_post_code) using gbk) as '收件人地址' ,
convert(db_goods.title using gbk) as '商品', convert(db_goods_format.title using gbk) as '规格' ,
convert(db_orders_item.item_number using gbk) as '数量',
convert( db_orders_item.item_price  using gbk )as '单价',
convert( db_orders_item.item_subtotal using gbk)as '商品小计' 
from
db_orders join db_orders_item on db_orders.id= db_orders_item.orders_id 
join  db_goods on db_orders_item.goods_id = db_goods.id  
join  db_goods_format on db_orders_item.format_id = db_goods_format.id 
join db_user on db_orders.user_id =db_user.id 
where  db_orders.display =1 and db_orders.user_display =1) b;

将查询的结果导出到/tmp/order.csv 文件中，字段以','结尾，行以'\n'换行结尾。

select * into outfile '/tmp/order.csv' fields terminated by ',' lines terminated by '\n' from

获取到列名，select 所有列名 union上真实的查询结果

select '订单号', '用户', '总计', '小计','运费','优惠','使用积分','商家实收','收件人','类型','运单号','状态','创建日期','收件人地址','商品' ,'规格' ,'数量','单价','商品小计'  union 

查看需要导出的表编码格式

show create table  ***

CONVERT()函数可以用来获取一个类型的值，并产生另一个类型的值，数据库中是使用的utf8，sql 中使用gbk来重新获取对应的值。

convert(db_user.name using gbk) as '用户' 

查询结果字段拼接，将take_name和take_telephone两个字段使用 '|'拼接起来

CONCAT_WS('|',db_orders.take_name ,db_orders.take_telephone)

case 判断 ，根据字段对应的不同值，展示不同的信息。

case  db_orders.status  
	when 0 then '待付款' 
	when 1 then '待发货' 
	when 2 then '已发货' 
	when 3 then '已完成'
	when 8 then '已收货' 
end

if判断，当closed是1的时候，状态显示 已关闭，不是1的时候根据status的值显示状态信息。

if(db_orders.closed=1,
    convert('已关闭' using gbk),
    convert(
	    case  db_orders.status  
	        when 0 then '待付款' 
	        when 1 then '待发货' 
	        when 2 then '已发货' 
	        when 3 then '已完成'
	        when 8 then '已收货' 
	    end using gbk) 
    ) as '状态',

替换值中的"\n" 换行符，在take_address 中发现换行符，使用REPLACE函数替换。

REPLACE(db_orders.take_address,CHAR(10),'')) 

其他字符参考  ASCII控制字符

查询结果存入csv文件 如下图