MySQL十进制类型超出范围错误

This code:

DROP DATABASE IF EXISTS `my_db`;
CREATE DATABASE `my_db`; 
CREATE TABLE `my_db`.`my_table` (
    `id` integer NOT NULL AUTO_INCREMENT, 
    `multiplier` decimal(18, 10) NOT NULL, 
    PRIMARY KEY (`id`)
) Engine=InnoDB;

INSERT INTO `my_db`.`my_table` (`multiplier`) VALUES (100000000.0);

Returns an error:

返回错误:

Error Code: 1264. Out of range value for column 'multiplier' at row 1

Why? There are only 9 digits before comma whereas the column should work until 18 digits - or am I missing something here? Thank you.

为什么?逗号之前只有9位数,而列应该工作到18位数 - 或者我在这里遗漏了什么?谢谢。

1 个解决方案

#1

decimal(18, 10) means 10 digits after decimal and 8 before decimal, not until 18 digits

decimal(18,10)表示十进制后的10位数,小数点后的8位,直到18位数

https://dev.mysql.com/doc/refman/5.1/en/precision-math-decimal-characteristics.html

For example, a DECIMAL(18,9) column has nine digits on either side of the decimal point, so the integer part and the fractional part each require 4 bytes. A DECIMAL(20,6) column has fourteen integer digits and six fractional digits. The integer digits require four bytes for nine of the digits and 3 bytes for the remaining five digits. The six fractional digits require 3 bytes.

例如,DECIMAL(18,9)列在小数点的两侧有九个数字,因此整数部分和小数部分每个需要4个字节。 DECIMAL(20,6)列有十四个整数位和六个小数位。对于九个数字,整数位需要四个字节,对于剩余的五个数字,整数位需要3个字节。六个小数位需要3个字节。