Mysql精度丢失踩坑记录

最新推荐文章于 2025-08-04 09:23:45 发布

原创最新推荐文章于 2025-08-04 09:23:45 发布 · 3.8k 阅读

4 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #decimal #精度丢失

大数据专栏收录该内容

15 篇文章

订阅专栏

在将业务表的价格转换为美元时，由于价格字段数据类型为varchar导致与decimal类型的汇率表进行计算时出现精度丢失。解决方案是在查询时使用CONVERT函数将价格字段转换为decimal类型，避免精度损失。此问题虽小但易被忽视，提醒注意类型匹配的重要性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

背景

最近在做一个功能，需要将业务表的价格统一转成美元以便后续对各产品价格变动进行对比和分析，然后一不小心，就发现自己写了个bug哈哈哈。

场景模拟

我们将问题尽可能的简化，只要2张表，一张价格表，一张是汇率表。

-- 汇率表
CREATE TABLE `exchange_rate_test` (
  `id` int NOT NULL AUTO_INCREMENT,
  `UKURS` decimal(10,4) DEFAULT NULL,
  `FCURR` varchar(16) DEFAULT NULL,
  `TCURR` varchar(16) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb3;

--价格表
CREATE TABLE `price_test` (
  `id` int NOT NULL AUTO_INCREMENT,
  `currency` varchar(16) DEFAULT NULL,
  `price` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb3;

--插入汇率表数据
INSERT INTO `exchange_rate_test` VALUES (1,0.2722,'AED','USD'),(2,0.0090,'ARS','USD'),(3,0.7507,'AUD','USD'),(4,0.2105,'BRL','USD'),(5,0.7997,'CAD','USD'),(6,0.0013,'CLP','USD'),(7,0.2664,'COP','USD'),(8,0.0458,'CZK','USD'),(9,0.1503,'DKK','USD'),(10,0.0183,'DOP','USD'),(11,0.0070,'DZD','USD'),(12,1.1176,'EUR','USD'),(13,1.3135,'GBP','USD'),(14,0.1329,'GHS','USD'),(15,0.0435,'GTO','USD'),(16,0.1277,'HKD','USD'),(17,0.0030,'HUF','USD'),(18,0.0697,'IDR','USD'),(19,0.0132,'INR','USD'),(20,0.0082,'JPY','USD'),(21,0.0087,'KES','USD'),(22,0.0119,'KGS','USD'),(23,0.0008,'KRW','USD'),(24,0.1037,'MAD','USD'),(25,0.0503,'MXN','USD'),(26,0.2380,'MYR','USD'),(27,0.2685,'PEN','USD'),(28,0.0192,'PHP','USD'),(29,0.2407,'PLN','USD'),(30,0.1575,'RMB','USD'),(31,0.0119,'RUB','USD'),(32,0.2665,'SAR','USD'),(33,0.1082,'SEK','USD'),(34,0.7393,'SGD','USD'),(35,0.0298,'THB','USD'),(36,0.3399,'TND','USD'),(37,0.0682,'TRY','USD'),(38,0.0350,'TWD','USD'),(39,0.0338,'UAH','USD'),(40,1.0000,'USD','USD'),(41,0.0440,'VND','USD'),(42,0.0017,'XOF','USD'),(43,0.0690,'ZAR','USD');

--插入价格表数据
INSERT INTO `price_test` VALUES (1,'DOP','1369'),(2,'DZD','949'),(3,'EUR','979'),(4,'GBP','399'),(5,'GHS','609'),(6,'GTO','459'),(7,'HKD','289'),(8,'HUF','699'),(9,'IDR','1449'),(10,'INR','1369'),(11,'JPY','539'),(12,'KES','939'),(13,'KGS','979'),(14,'KRW','779'),(15,'MAD','889'),(16,'MXN','未定价'),(17,'MYR','809'),(18,'PEN','869'),(19,'PHP','479'),(20,'PLN','399'),(21,'RMB','1219'),(22,'RUB','539'),(23,'SAR','1819'),(24,'SEK','1169'),(25,'SGD','2899');

细心的你应该发现了问题，汇率表中的汇率是decimal类型，我们知道，一般跟金钱有关的数据类型都要定义成decimal类型。但是！价格表的价格字段由于业务上需要兼容一些不确定的场景，数据类型被定义成了varchar类型。而mysql底层的类型转换正是精度丢失的罪魁祸首。

-- 模拟精度丢失
select e.UKURS,p.price,e.UKURS*p.price
from exchange_rate_test e, price_test p
where e.FCURR=p.currency;

解决方案

我们当然不能去修改业务表的数据类型，这是肯定的。只需要在计算的时候，CONVERT(p.price,decimal)把价格字段的类型手动转成decimal即可。

select e.UKURS,p.price,e.UKURS*CONVERT(p.price,decimal)
from exchange_rate_test e, price_test p
where e.FCURR=p.currency;

总结

这是一个很低级的小问题，但是往往越容易被忽略的细节越容易出bug哦~