Linux下将csv文件插入到mysql数据库

 将文件目录下最新csv文件导入数据库并将字段进行对应替换

#!/bin/bash
date=`date +%Y%m%d`
path=/dir/$date
#获取最新文件名
file=`ls -lt $path | grep name | head -n 1 |awk '{print $9}'`
#赋权
sudo chmod a=rwx $path/$file
#清空表
sudo mysql -A db_name -h host -uUSER -pPASSWD  -e "DELETE from table_name"
#采集最新数据入库
echo "load data local infile '$path/$file' into table table_name  fields terminated by '|' (user,sex,city,time_out);" > $path/$file.sql
#执行sql
sudo mysql -A db_name -h host -uUSER -pPASSWD -D dir_name --local-infile=on  < $path/$file.sql
#转换地市名称
sudo mysql -A db_name -h host -uUSER -pPASSWD  -e "update table_name set city = (case city when '阿拉善' then '阿拉善盟'when '巴彦淖尔' then '巴彦淖尔市'when '包头' then '包头市'when '赤峰' then '赤峰市'when '鄂尔多斯' then '鄂尔多斯市'when '呼和浩特' then '呼和浩特市'when '呼伦贝尔' then '呼伦贝尔市'when '通辽' then '通辽市'when '乌海' then '乌海市'when '乌兰察布' then '乌兰察布市'when '锡林郭勒' then '锡林郭勒盟'when '兴安' then '兴安盟' end);"