MySQL读取CSV日期值为科学计数法，如何拆分成年日时字符段？

这问题我之前也碰到过！核心原因是MySQL自动把CSV里的长数字字符串识别成了浮点数值，导致存储为科学计数法格式，后续的转换操作都是基于这个失真的浮点值，自然得不到你想要的完整数字字符串。下面分两种场景给你解决方案：

一、从根源解决：导入时直接按字符串读取 #

这是最优解，避免后续的修复麻烦。问题出在MySQL的自动类型推断——如果CSV里的日期字段没有被双引号包裹，MySQL会把纯数字内容当成数值处理，超过一定长度就会转成科学计数法。

方法1：给CSV里的日期字段加双引号 #

把CSV中的200001011130改成"200001011130"，这样你的OPTIONALLY ENCLOSED BY '"'配置就会生效，直接把该字段读取为字符串。修改后的LOAD语句可以直接拆分：

LOAD DATA LOCAL INFILE 'C:\\ProgramData\\MySQL\\MySQL Server 5.7\\Uploads\\xxxx.csv' 
INTO TABLE xxx 
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' 
LINES TERMINATED BY '\r\n' 
IGNORE 1 LINES 
(@datetimeStr, ...)
SET 
  year_col = LEFT(@datetimeStr, 4),
  date_col = MID(@datetimeStr, 5, 4),
  time_col = RIGHT(@datetimeStr, 4);

方法2：不修改CSV，强制转成完整数字字符串 #

如果没法修改CSV，可以在LOAD时先把读取到的浮点数值转成无格式的完整字符串，再拆分：

LOAD DATA LOCAL INFILE 'C:\\ProgramData\\MySQL\\MySQL Server 5.7\\Uploads\\xxxx.csv' 
INTO TABLE xxx 
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' 
LINES TERMINATED BY '\r\n' 
IGNORE 1 LINES 
(@raw_datetime, ...)
SET 
  @datetimeStr = REPLACE(FORMAT(@raw_datetime, 0), ',', ''), -- 去掉千位分隔符，得到完整数字串
  year_col = LEFT(@datetimeStr, 4),
  date_col = MID(@datetimeStr, 5, 4),
  time_col = RIGHT(@datetimeStr, 4);

FORMAT(@raw_datetime, 0)会把科学计数法的数值转成带千位分隔符的字符串（比如200,001,011,300），再用REPLACE去掉逗号就得到了完整的数字字符串。

二、修复已转成科学计数法的变量 #

如果已经把@datetimeStr变成了科学计数法的数值（比如2.00001E+11），可以用以下方式恢复成完整字符串：

-- 方案1：用FORMAT+REPLACE
SET @full_str = REPLACE(FORMAT(@datetimeStr, 0), ',', '');

-- 方案2：转成DECIMAL再转字符串（适合数字长度在DECIMAL范围内的情况）
SET @full_str = CAST(CAST(@datetimeStr AS DECIMAL(20,0)) AS CHAR);

得到完整字符串后，就可以正常拆分了：

SELECT 
  LEFT(@full_str, 4) AS year_part,
  MID(@full_str, 5, 4) AS date_part,
  RIGHT(@full_str, 4) AS time_part;

为什么你之前的转换没用？ #

你用CONVERT(@datetimeStr, CHAR)或CAST(@datetimeStr AS CHAR)得到科学计数法，是因为@datetimeStr本身是浮点数值类型——MySQL把浮点数值转成字符串时，默认会用科学计数法表示大数，而不是展开成完整数字串。所以必须先把浮点值转成完整的整数形式，再转成字符串，这就是FORMAT()或CAST(DECIMAL)的作用。

内容的提问来源于stack exchange，提问作者Matthias Otto