Decimal Data Type Syntax

原创

kq1983 2021-08-25 10:56:44 博主文章分类：mysql ©著作权

©著作权归作者所有：来自51CTO博客作者kq1983的原创作品，请联系作者获取转载授权，否则将追究法律责任

DECIMAL 11.1.1

DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]

A packed “exact” fixed-point number. M is the total number of digits (the precision) and D is the number of digits after the decimal point (the scale). The decimal point and (for negative numbers) the - sign are not counted in M. If D is 0, values have no decimal point or fractional part. The maximum number of digits (M) for DECIMAL is 65. The maximum number of supported decimals (D) is 30. If D is omitted, the default is 0. If M is omitted, the default is 10. (There is also a limit on how long the text of DECIMAL literals can be; see Section 12.22.3, “Expression Handling”.)

UNSIGNED, if specified, disallows negative values.

All basic calculations (+, -, *, /) with DECIMAL columns are done with a precision of 65 digits.

M是总位数（精度），D是小数点后的位数.
小数点和符号(+-) ，不计入M
如果D为0，则值没有小数点或小数部分
M最大位数为65，D最大位数为30
如果忽略M，默认为10
如果忽略D，默认为0
如果指定了UNSIGNED，则不允许使用负值。
使用DECIMAL列的所有基本计算（+，-，*，/）均以65位精度完成。

DECIMAL同义词

DEC[(M[,D])] [UNSIGNED] [ZEROFILL], NUMERIC[(M[,D])] [UNSIGNED] [ZEROFILL], FIXED[(M[,D])] [UNSIGNED] [ZEROFILL]

These types are synonyms for DECIMAL. The FIXED synonym is available for compatibility with other database systems.

例子

create table decimal_table(id serial, m1 decimal(20,8),m2 dec(10,2),m3 NUMERIC(8,2),m4 fixed(6,2),un2 decimal(10,2) unsigned,zf2 decimal(10,2) zerofill)

Decimal Data Type Syntax_四舍五入

从这图可知，最终都变成了decimal

举例总结

本文例子在MySql5.7.23中测试

以decimal(10,2)为例

# ok  最终保存的是99999999.99
insert decimal_table(un2) values(000099999999.99499999999999999999999)

# fail
insert decimal_table(un2) values(000099999999.995)

# fail  zf2是无符号，如果是有符号就不会报错
insert decimal_table(zf2) values(-0.01)