decimal 位数的坑
1、场景还原
数据库版本:
mysql> select version();
±----------+
| version() |
±----------+
| 5.7.32 |
±----------+
1 row in set (0.02 sec)
数据库中 invoiceAmount 字段设置的类型为 decimal(10,4)
然后保存数据时提交数据为 1234567,然后就报错了。Out of range value for column ‘invoice_amount’ at row 1
就很纳闷,明明设置的是 10 位数呀,这也就 7 位,怎么就报错了呢。
2、原因分析
decimal(10, 4)
-- 这个设置表示的是小数位加上整数位总共为 10 位.醉了,也是。所以调整了位数为 20 位,就 OK 了
3、示例演示一下
3.1 创建脚本
mysql> show create table bcus_test_decimal;
+-------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| bcus_test_decimal | CREATE TABLE `bcus_test_decimal` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`invoice_amount` decimal(10,4) NOT NULL DEFAULT '0.0000',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
3.2 测试插入数据
-- 7 位数的时候就异常
mysql> INSERT INTO bcus_test_decimal (invoice_amount) VALUES(1234567);
1264 - Out of range value for column 'invoice_amount' at row 1
-- 6 位数的时候就正常
mysql> INSERT INTO bcus_test_decimal (invoice_amount) VALUES(123456);
Query OK, 1 row affected (0.04 sec)
mysql> select * from bcus_test_decimal;
+----+----------------+
| id | invoice_amount |
+----+----------------+
| 1 | 123456.0000 |
+----+----------------+
1 row in set (0.00 sec)
3.3 修改数据库表字段限制
-- 修改为 20 位
mysql> ALTER TABLE `bcrm`.`bcus_test_decimal`
MODIFY COLUMN `invoice_amount` decimal(20, 4) NOT NULL DEFAULT 0.0000 AFTER `id`;
Query OK, 1 row affected (0.16 sec)
Records: 1 Duplicates: 0 Warnings: 0
-- 再次插入成功
mysql> INSERT INTO bcus_test_decimal (invoice_amount) VALUES(1234567);
Query OK, 1 row affected (0.00 sec)
mysql> select * from bcus_test_decimal;
+----+----------------+
| id | invoice_amount |
+----+----------------+
| 1 | 123456.0000 |
| 2 | 1234567.0000 |
+----+----------------+
以前没有在意这个字段的限制,现在遇到了,记录一下.也希望有遇到同类问题的老铁们借鉴一下。