mysql 导入csv脚本

1.创建csv的txt文件，将需要导入的文件名按行写入txt

2.主要用到mysql自带的load工具，前提mysql支持任何路径导入
直接按字段导入

mysql -uroot -p8888888 -S /data/mysql3306/mysql.sock box -e \
    "LOAD DATA INFILE '/data/hefa-fengkong/1701744558169.csv'
     INTO TABLE user_risk_record
     FIELDS TERMINATED BY ',' 
     LINES TERMINATED BY '\n'
     IGNORE 1 ROWS
     (mod_id, mod_name, mod_value,action_id,action_name,sku_name,company,user_id,policy_no,evaluated_at,order_no,num,created_at,updated_at);"

进行映射导入

mysql -uroot -p888888 -S /data/mysql3306/mysql.sock box -e \
    "LOAD DATA INFILE '/data/hefa-fengkong/20231129120000.csv'
     INTO TABLE user_risk_record
     FIELDS TERMINATED BY ',' 
     LINES TERMINATED BY '\n'
     IGNORE 1 ROWS
     (order_no,policy_no,mod_id,num,@created_at)
     set order_no=order_no,policy_no=policy_no,mod_id=mod_id,num=num,created_at=@created_at,updated_at=@created_at;"

3.上传导服务器，相应路径后执行脚本

#!/bin/bash
# 包含 CSV 文件路径的文本文件
FILE_LIST="/root/fengkong_hf/file"

# 逐行读取文件路径
while IFS= read -r CSV_FILE; do
    echo "Processing: $CSV_FILE"

    # 构造 LOAD DATA INFILE 语句
    SQL="LOAD DATA INFILE '$CSV_FILE'
         INTO TABLE user_test
         FIELDS TERMINATED BY ',' 
         LINES TERMINATED BY '\n'
         IGNORE 1 ROWS
         (mod_id, mod_name, mod_value,action_id,action_name,sku_name,company,user_id,policy_no,evaluated_at,order_no,num,created_at,updated_at);"

    # 使用 mysql 命令执行
    mysql -uroot -p8888888 -S /data/mysql3306/mysql.sock box -e "$SQL"

    if [ $? -eq 0 ]; then
        echo "Successfully imported: $CSV_FILE"
    else
        echo "Failed to import: $CSV_FILE"
    fi
done < "$FILE_LIST"