今天在使用mysql的into outfile命令将数据库数据导出为csv时,发现数据可以导出,但是列名却没有,但是却可以通过修改sql语句的方法加上表头,具体实现方法是在查找数据的时候并上列名:
#!/bin/bash
#提示输入要导出数据的月份read -n 2 -p "请输入要导出的数据的月份必须两位,如1月请输入01,10月输入10:" mon
if [ ! -d "/home/databak/download/$mon" ];
then
mkdir /home/databak/download/$mon
chown mysql.mysql -R /home/databak/download
fi
for i in `seq 31`
do
num=`printf "%02d\n" $i`
mysql -uuser -p'password' <<EOF
#不带表头
select * from kswxp.gis_vehicle_locate_2017$mon$num into outfile '/home/databak/download/$mon/gis_vehicle_locate_2017$mon$num.xls' C
HARACTER SET gbk FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';
#带表头
select * from (select 'code'as code,'license' as license,'license_color' as license_color,'lon' as lon,'lat' as lat,'def_lon' as def_lon,'def_lat' as def_lat,'gps_time' as gps_time,'gps_speed' as gps_speed,'record_speed' as record_speed,'mileage' as mileage, 'direction' as direction, 'altitude' as altitude, 'acc_state' as acc_state, 'position_state' as position_state, 'business_state' as business_state, 'oil_state' as oil_state, 'circuit_state' as circuit_state, 'doors_state' as doors_state, 'emergency_alarm' as emergency_alarm, 'overspeed_alarm' as overspeed_alarm, 'fatigue_alarm' as fatigue_alarm, 'warning' as warning, 'gnss_module_fault' as gnss_module_fault, 'gnss_antenna_missed' as gnss_antenna_missed, 'gnss_antenna_short_circuit' as gnss_antenna_short_circuit, 'mainpower_undervoltage' as mainpower_undervoltage, 'mainpower_down' as mainpower_down, 'lcd_fault' as lcd_fault, 'tts_module_fault' as tts_module_fault, 'camera_fault' as camera_fault, 'drive_timeout_day' as drive_timeout_day, 'stop_timeout' as stop_timeout, 'in_out_area' as in_out_area, 'in_out_line' as in_out_line, 'section_drive_problem' as section_drive_problem, 'route_deviate' as route_deviate, 'vss_fault' as vss_fault, 'oil_fault' as oil_fault, 'vehicle_stolen' as vehicle_stolen, 'illegal_ignition' as illegal_ignition, 'illegal_move' as illegal_move, 'rollover_alarm' as rollover_alarm, 'receivetime' as receivetime, 'platcode' as platcode, 'location' as location union all select * from kswxp.gis_vehicle_locate_2017$mon$num) t into outfile '/home/databak/download/$mon/gis_vehicle_locate_2017$mon$num.csv' CHARACTER SET gbk FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';
EOFcd /home/databak/download/$mon
tar -cvzf gis_vehicle_locate_2017$mon$num.csv.tar.gz gis_vehicle_locate_2017$mon$num.csv
rm -rf /home/databak/download/$mon/gis_vehicle_locate_2017$mon$num.csv
done