Mysql decimal available types: 1. FLOAT type, which can store single-precision floating-point numbers; 2. DOUBLE type, which can store double-precision floating-point numbers; 3. DECIMAL type, used to store precise values, such as accounting Currency data in the system.
The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.
Floating point numbers and fixed point numbers are used to represent decimals in MySQL.
There are two floating-point types, single-precision floating-point numbers (FLOAT) and double-precision floating-point numbers (DOUBLE); there is only one fixed-point type, which is DECIMAL.
Both floating-point types and fixed-point types can be represented by (M, D), where M is called precision, indicating the total number of digits; D is called scale, indicating the number of decimal digits.
The value range of floating point number type is M (1~255) and D (1~30, and cannot be greater than M-2), which represent the display width and number of decimal places respectively. M and D are optional in FLOAT and DOUBLE, and the FLOAT and DOUBLE types will be saved to the maximum precision supported by the hardware. The default D value for DECIMAL is 0 and M value is 10.
The following table lists the decimal types and storage requirements in MySQL.
The DECIMAL type is different from FLOAT and DOUBLE. DOUBLE is actually stored in the form of a string. The possible maximum value range of DECIMAL is the same as DOUBLE, but the effective value range is determined by M and D. If M is changed and D is fixed, the value range will become larger as M becomes larger.
As can be seen from the above table, the storage space of DECIMAL is not fixed, but is determined by the precision value M, occupying M 2 bytes.
The value range of the FLOAT type is as follows:
The signed value range: -3.402823466E 38～-1.175494351E-38.
Unsigned value range: 0 and -1.175494351E-38～-3.402823466E 38.
The value range of the DOUBLE type is as follows:
The signed value range: -1.7976931348623157E 308～ -2.2250738585072014E-308.
Unsigned value range: 0 and -2.2250738585072014E-308～-1.7976931348623157E 308.
Note: Whether it is a fixed-point or floating-point type, if the user-specified precision exceeds the precision range, it will be rounded for processing.
FLOAT and DOUBLE will default to the actual precision when the precision is not specified. DECIMAL will default to (10, 0) if the precision is not specified.
The advantage of floating-point numbers over fixed-point numbers is that they can represent a larger range when the length is constant; the disadvantage is that it can cause accuracy problems.
DECIMALThe data type is used to store precise values in the database. We often use theDECIMALdata type for columns that retain exact precision, such as currency data in accounting systems.
To define a column with data typeDECIMAL, use the following syntax:
column_name DECIMAL(P,D);
In the above syntax:
Prepresents the precision of the number of significant digits.Prange is1~65.Drepresents the number of digits after the decimal point. The range ofDis0~30. MySQL requiresDto be less than or equal to ()P.DECIMAL(P,D)Indicates that the column can storePdigits ofDdecimal places. The actual range of a decimal column depends on precision and scale.
Like the INT data type, theDECIMALtype also hasUNSIGNEDandZEROFILLattributes. If theUNSIGNEDattribute is used, the column forDECIMAL UNSIGNEDwill not accept negative values.
IfZEROFILLis used, MySQL will pad the display value to0to display the width specified by the column definition. Additionally, if we useZERO FILLon theDECIMALcolumn, MySQL will automatically add theUNSIGNEDattribute to the column.
The following example uses a column calledamountdefined using theDECIMALdata type.
amount DECIMAL(6,2);
In this example, theamountcolumn can store up to6digits with a decimal place of2; therefore, ## The #amountcolumn ranges from-9999.99to9999.99.
column_name DECIMAL(P);This is equivalent to:
column_name DECIMAL(P,0);In this case, the column does not contain a decimal part or decimal point. Also, we can even use the following syntax.
column_name DECIMAL;In this case, the default value of
Pis10.
MySQL DECIMAL STORAGE
MySQL分别为整数和小数部分分配存储空间。 MySQL使用二进制格式存储DECIMAL值。它将9位数字包装成4个字节。
对于每个部分，需要4个字节来存储9位数的每个倍数。剩余数字所需的存储如下表所示：
| 剩余数字 | 位 |
|---|---|
| 0 | 0 |
| 1–2 | 1 |
| 3–4 | 2 |
| 5–6 | 3 |
| 7-9 | 4 |
例如，DECIMAL(19,9)对于小数部分具有9位数字，对于整数部分具有19位=10位数字，小数部分需要4个字节。 整数部分对于前9位数字需要4个字节，1个剩余字节需要1个字节。DECIMAL(19,9)列总共需要9个字节。
MySQL DECIMAL数据类型和货币数据
经常使用DECIMAL数据类型的货币数据，如价格，工资，账户余额等。如果要设计一个处理货币数据的数据库，则可参考以下语法 -
amount DECIMAL(19,2);
但是，如果您要遵守公认会计原则(GAAP)规则，则货币栏必须至少包含4位小数，以确保舍入值不超过$0.01。 在这种情况下，应该定义具有4位小数的列，如下所示：
amount DECIMAL(19,4);
MySQL DECIMAL数据类型示例
首先，创建一个名为test_order的新表，其中包含三列：id，description和cost。
CREATE TABLE test_order ( id INT AUTO_INCREMENT PRIMARY KEY, description VARCHAR(255), cost DECIMAL(19,4) NOT NULL );
第二步，将资料插入test_order表。
INSERT INTO test_order(description,cost) VALUES('Bicycle', 500.34),('Seat',10.23),('Break',5.21);
第三步，从test_order表查询数据。
SELECT * from test_order
第四步，更改cost列以包含ZEROFILL属性。
ALTER TABLE test_order MODIFY cost DECIMAL(19,4) zerofill;
第五步，再次查询test_order表。
SELECT * from test_order
查询结果：
如上所见，在输出值中填充了许多零。
因为zerofill，当我们插入负值会报错：
INSERT INTO test_order(description,cost) VALUES('test', -100.11); 提示： [SQL]INSERT INTO test_order(description,cost) VALUES('test', -100.11) [Err] 1264 - Out of range value for column 'cost' at row 1
其它插入测试结论：
当数值在其取值范围之内，小数位多了，则四舍五入后直接截断多出的小数位。
若数值在其取值范围之外，则直接报Out of range value错误。
【相关推荐：mysql视频教程】
The above is the detailed content of What type does mysql use for decimals?. For more information, please follow other related articles on the PHP Chinese website!