Decimal中Scale和Precision是什么

原创

晓楚 2023-06-15 14:56:25 博主文章分类：OceanBase ©著作权

文章标签 mysql Org Max 文章分类 JavaScript 前端开发

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

Precision (field length)         精度（字段长度） 

Scale (decimal places)         范围（小数位数）
例如：-4.75, precision=3，scale=2，和符号位无关

详解

MySQL要求精度大于等于范围：

mysql> create table test (a decimal(1,3));
ERROR 1427 (42000): For float(M,D), double(M,D) or decimal(M,D), M must be >= D (column 'a').

a的定义如下：

create table test (a decimal(3,1));

a字段长度为3，小数点位数为1，表示范围为99.9 ~ -99.9。如果插入的数据小数点位数比scale大，则对scale+1位置的小数进行四舍五入。例如：99.94存储为99.9，99.95提示溢出，99.949存储为99.9（注意，不是这样的四舍五入逻辑哦：99.949=>99.95=>100.0。只看scale后面的一位。）

看一些例子，加深印象：

mysql> create table test (a decimal(3,3));
Query OK, 0 rows affected (0.04 sec)
mysql> insert into test values (11);
ERROR 1264 (22003): Out of range value for column 'a' at row 1
mysql> insert into test values (1);
ERROR 1264 (22003): Out of range value for column 'a' at row 1
mysql> insert into test values (0.999);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test values (0.9999);
ERROR 1264 (22003): Out of range value for column 'a' at row 1
mysql> insert into test values (0.0004);
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> insert into test values (0.0005);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from test;
Field   1:  `a`
Catalog:    `def`
Database:   `test`
Table:      `test`
Org_table:  `test`
Type:       NEWDECIMAL
Collation:  binary (63)
Length:     5
Max_length: 5
Decimals:   3
Flags:      NUM


+-------+
| a     |
+-------+
| 0.999 |
| 0.000 |
| 0.001 |
+-------+
3 rows in set (0.00 sec)






mysql> drop table test;
Query OK, 0 rows affected (0.00 sec)
mysql> create table test (a decimal(3,0));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into test values (-999.3);
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> insert into test values (-999.7);
ERROR 1264 (22003): Out of range value for column 'a' at row 1
mysql> insert into test values (1999);
ERROR 1264 (22003): Out of range value for column 'a' at row 1
mysql> insert into test values (1.0);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test values (1.3);
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> insert into test values (-1.3);
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> insert into test values (-9.3);
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> insert into test values (-0.3);
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> select * from test;
Field   1:  `a`
Catalog:    `def`
Database:   `test`
Table:      `test`
Org_table:  `test`
Type:       NEWDECIMAL
Collation:  binary (63)
Length:     4
Max_length: 4
Decimals:   0
Flags:      NUM




+------+
| a    |
+------+
| -999 |
|    1 |
|    1 |
|   -1 |
|   -9 |
|    0 |
+------+
6 rows in set (0.00 sec)






mysql> drop table test;
Query OK, 0 rows affected (0.01 sec)
mysql> create table test (a decimal(3,1));
Query OK, 0 rows affected (0.02 sec)
mysql> insert into test values (1.3);
Query OK, 1 row affected (0.01 sec)
mysql> insert into test values (99.99);
ERROR 1264 (22003): Out of range value for column 'a' at row 1
mysql> insert into test values (99.9);
Query OK, 1 row affected (0.01 sec)
mysql> insert into test values (-99.9);
Query OK, 1 row affected (0.01 sec)
mysql> insert into test values (-99.99);
ERROR 1264 (22003): Out of range value for column 'a' at row 1
mysql> insert into test values (-99.95);
ERROR 1264 (22003): Out of range value for column 'a' at row 1
mysql> insert into test values (-99.949);
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> insert into test values (-99.94);
Query OK, 1 row affected, 1 warning (0.00 sec)


mysql> select * from test;
Field   1:  `a`
Catalog:    `def`
Database:   `test`
Table:      `test`
Org_table:  `test`
Type:       NEWDECIMAL
Collation:  binary (63)
Length:     5
Max_length: 5
Decimals:   1
Flags:      NUM




+-------+
| a     |
+-------+
|   1.3 |
|  99.9 |
| -99.9 |
| -99.9 |
| -99.9 |
+-------+
5 rows in set (0.00 sec)

顺便提一句关于Result Meta的length计算：

对于decimal来说，scale和precision相等的情况下，整数部分的0要占一位length；scale为0的情况下没有小数点，length会少占一位。再考虑到unsigned，如果是无符号数，则length的计算中还不需要考虑符号位，又可以少占一位。为了处理这些逻辑，MySQL对应代码如下：

inline uint32 my_decimal_precision_to_length_no_truncation(uint precision,
                                                           uint8 scale,
                                                           bool unsigned_flag)
{
  /*
    When precision is 0 it means that original length was also 0. Thus
    unsigned_flag is ignored in this case.
  */
  DBUG_ASSERT(precision || !scale);
  return (uint32)(precision + (scale > 0 ? 1 : 0) +
                  (unsigned_flag || !precision ? 0 : 1));
}

看一个数字-4.75来理解上面的逻辑， -4.75， precision=3，scale=2，unsigned_flag=0，则上面的表达式分解如下：

precison算4、7、5三个数字，(scale > 0 ? 1 : 0)算小数点，(unsigned_flag ? 0 : 1)算符号位；根据上面代码中的注释，（!precision ? 0 : 1)表示数值0的情况，此时忽略符号位，也就是说，即使unsigned_flag=1，只要precision为0的话，负号所占的那一位总是不算，-0总是记作0。