从CSV导入MySQL指定列时如何去除引号？

方法1：利用CSV字段包围符参数（最简便） #

你的CSV字段是用双引号包裹的，直接在FIELDS子句里添加OPTIONALLY ENCLOSED BY '"'，MySQL会自动去除引号并识别正确的数据类型：

LOAD DATA INFILE 'C:/xampp/htdocs/postcode_updates/csv/ONSPD_MAY_2024_UK_AB.csv'
INTO TABLE geopostcodes
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES 
(pcds, osnrth1m, oseast1m, lat, lng)
SET pcode = pcds, grid_n = osnrth1m, grid_e = oseast1m

注：这里可以直接映射字段，无需用户变量，MySQL会自动处理引号和类型转换

方法2：手动去除引号并转换类型（兼容复杂场景） #

如果第一种方法不生效，用TRIM函数去掉双引号，再用CAST转换对应的数据类型：

LOAD DATA INFILE 'C:/xampp/htdocs/postcode_updates/csv/ONSPD_MAY_2024_UK_AB.csv'
INTO TABLE geopostcodes
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 LINES 
(@pcds, @osnrth1m, @oseast1m, @lat, @lng)
SET 
  pcode = TRIM(BOTH '"' FROM @pcds),
  grid_n = CAST(TRIM(BOTH '"' FROM @osnrth1m) AS UNSIGNED),
  grid_e = CAST(TRIM(BOTH '"' FROM @oseast1m) AS UNSIGNED),
  lat = CAST(TRIM(BOTH '"' FROM @lat) AS DECIMAL(10,6)),
  lng = CAST(TRIM(BOTH '"' FROM @lng) AS DECIMAL(10,6))

说明：UNSIGNED适配网格参考的正整数，DECIMAL(10,6)足够存储经纬度精度

批量导入提示 #

要处理100多个文件，可以写批处理（Windows）或Shell脚本（Linux）循环执行LOAD DATA语句，省去手动逐个操作的麻烦。

内容的提问来源于stack exchange，提问作者Mike West