MySQL拆分price字段整数与小数部分的SQL优化问题

需求：将表中price字段（示例值：1020.2200、202.0000、0.5000、820.6700）拆分为整数部分p_units和保留两位的小数部分p_decimals，要求整数部分取整，小数部分为两位数字（如1020.2200对应p_units=1020，p_decimals=22）。

正确SQL实现方法 #

方法1：数值运算（推荐，避免字符串处理的潜在问题）

SELECT 
    price,
    FLOOR(price) AS p_units,
    ROUND((price - FLOOR(price)) * 100) AS p_decimals
FROM mytable;

• FLOOR(price)：直接提取price的整数部分；
• (price - FLOOR(price))：获取price的纯小数部分；
• 乘以100后用ROUND()取整，得到两位小数对应的整数值（如0.2200*100=22.00，ROUND后为22）。

方法2：字符串格式化处理

如果担心浮点精度问题，可通过字符串格式化拆分：

SELECT 
    price,
    FLOOR(price) AS p_units,
    CAST(SUBSTRING_INDEX(FORMAT(price, 2), '.', -1) AS UNSIGNED) AS p_decimals
FROM mytable;

• FORMAT(price, 2)：将price格式化为保留两位小数的字符串（如1020.2200转为"1020.22"）；
• SUBSTRING_INDEX(..., '.', -1)：截取小数点后的两位数字；
• CAST(... AS UNSIGNED)：将截取的字符串转为无符号整数。

原方法问题说明 #

原SQL中price MOD 1返回的是带前导"0."的四位小数（如0.2200），无法直接得到纯两位数字的小数部分，因此需要调整为上述两种方案。

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