将查询得到的身高厘米值插入新列时遇数据截断错误求助

问题描述 #

active_players_2表中身高列格式为6' 7"，使用SELECT语句可正确计算对应的厘米值，但新增Height_cm列后执行UPDATE语句时，出现[22001][1292]数据截断错误，提示信息为：Truncated incorrect INTEGER value: ' 5"'。

原SELECT语句：

SELECT
    Name,
    (CAST(SUBSTR(Height,1, LOCATE("'",Height)-1) AS UNSIGNED) * 30.48) +
    (CAST(SUBSTR(Height, LOCATE("'",Height)+1) AS UNSIGNED) * 2.54 )   AS cm
FROM
    active_players_2;

原ALTER和UPDATE语句：

ALTER TABLE active_players_2
   ADD Height_cm DECIMAL(10,2) NULL;
UPDATE active_players_2 
SET active_players_2.Height_cm= (CAST(SUBSTR(Height,1, LOCATE("'",Height)-1) AS UNSIGNED) * 30.48) +
            (CAST(SUBSTR(Height, LOCATE("'",Height)+1) AS UNSIGNED) * 2.54 )

错误原因 #

错误提示中的' 5"'表明，截取的英寸部分包含空格和双引号这类非数字字符，直接将其转为UNSIGNED整数时，因无法识别非数字内容触发数据截断。SELECT语句可能仅扫描了部分无异常的数据，而UPDATE是全表执行，因此暴露了数据格式的问题。

解决方案 #

修改UPDATE语句中的计算逻辑，确保提取到纯数字的英尺和英寸值：

方案1：使用TRIM清理非数字字符 #

通过TRIM(BOTH '" ' FROM ...)移除英寸部分的空格和双引号：

UPDATE active_players_2 
SET Height_cm= (CAST(SUBSTR(Height,1, LOCATE("'",Height)-1) AS UNSIGNED) * 30.48) +
               (CAST(TRIM(BOTH '" ' FROM SUBSTR(Height, LOCATE("'",Height)+1)) AS UNSIGNED) * 2.54 )

方案2：使用正则表达式提取数字 #

用REGEXP_SUBSTR直接从身高字符串中提取第一组和第二组数字，分别对应英尺和英寸：

UPDATE active_players_2 
SET Height_cm= (CAST(REGEXP_SUBSTR(Height, '[0-9]+', 1, 1) AS UNSIGNED) * 30.48) +
               (CAST(REGEXP_SUBSTR(Height, '[0-9]+', 1, 2) AS UNSIGNED) * 2.54 )

验证 #

执行修改后的UPDATE语句前，可先用SELECT语句验证计算逻辑是否正确：

SELECT
    Name,
    Height,
    (CAST(REGEXP_SUBSTR(Height, '[0-9]+', 1, 1) AS UNSIGNED) * 30.48) +
    (CAST(REGEXP_SUBSTR(Height, '[0-9]+', 1, 2) AS UNSIGNED) * 2.54 ) AS cm
FROM active_players_2;

内容的提问来源于stack exchange，提问作者Abdallah mohamed