NOT_FIXED_DEC详解

值

宏，等于31

来源

There is a “magic” number for precision : NOT_FIXED_DEC.
This means that the precision is not a fixed number.

⬆️摘自：https://bugs.mysql.com/bug.php?id=33544

---

The number of decimal digits to the right of the decimal point. The default value is the maximum number of decimal digits in the arguments passed to the main function. For example, if the function is passed 1.34, 1.345, and 1.3, the default would be 3, because 1.345 has 3 decimal digits.

For arguments that have no fixed number of decimals, the decimals value is set to 31, which is 1 more than the maximum number of decimals permitted for the DECIMAL, FLOAT, and DOUBLE data types. As of MySQL 5.5.3, this value is available as the constant NOT_FIXED_DEC in the mysql_com.h header file.

A decimals value of 31 is used for arguments in cases such as a FLOAT or DOUBLE column declared without an explicit number of decimals (for example, FLOAT rather than FLOAT(10,3)) and for floating-point constants such as 1345E-3. It is also used for string and other nonnumber arguments that might be converted within the function to numeric form.

The value to which the decimals member is initialized is only a default. It can be changed within the function to reflect the actual calculation performed. The default is determined such that the largest number of decimals of the arguments is used. If the number of decimals is NOT_FIXED_DEC for even one of the arguments, that is the value used for decimals.

⬆️摘自：http://docs.oracle.com/cd/E17952_01/refman-5.5-en/udf-calling.html

上面有一句很重要，说明了NOT_FIXED_DEC为什么取值为31：

the decimals value is set to 31, which is 1 more than the maximum number of decimals permitted for the DECIMAL, FLOAT, and DOUBLE data types

做个实验：

mysql> create table tf (x decimal(33,31));
ERROR 1425 (42000): Too big scale 31 specified for column 'x'. Maximum is 30.

Ooops…

---

The fact that an Item has a not fixed number of decimal digits
is currently stored in Item::decimals as a special value
NOT_FIXED_DEC (31).

We should stop using decimal==NOT_FIXED_DEC for that purposes,
and introduce a new virtual method “bool NOT_FIXED_DEC()” instead.
This should help to avoid bugs like MDEV-4511
when NOT_FIXED_DEC is erroneously treated as “normal”
number of decimals.

Also, there is currently no a way to get the real number of
decimals from an Item if decimals=NOT_FIXED_DEC, which
makes calculating decimal precision and scale hard in
fix_length_and_dec() of some items.

⬆️ 摘自： https://mariadb.atlassian.net/browse/MDEV-6346

---

#用途

先看几个例子，分别执行下面的SQL：

mysql> select 1.3e1;
mysql> select 'hi';

他们的meta结果中，decimal都是NOT_FIXED_DEC。

mysql> select 'hi';
Field   1:  `hi`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  utf8_general_ci (33)
Length:     6
Max_length: 2
Decimals:   31
Flags:      NOT_NULL


+----+
| hi |
+----+
| hi |
+----+
1 row in set (0.01 sec)

mysql> select 1.3e1;
Field   1:  `1.3e1`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       DOUBLE
Collation:  binary (63)
Length:     5
Max_length: 2
Decimals:   31
Flags:      NOT_NULL BINARY NUM


+-------+
| 1.3e1 |
+-------+
|    13 |
+-------+
1 row in set (0.00 sec)

#推论
其实，并不一定必须用31，用-1也是可以的，反正是一个Magic Number，不会直接参与加减运算。
阅读MySQL源码的时候，可能看到类似这样的比较：

if (decimals >= NOT_FIXED_DEC) { ... }

它只是一种防御性编程的写法，实际等价于：

if (decimals == NOT_FIXED_DEC) { ... }

Update

from ChatGPT:MySQL 源码里，NOT_FIXED_DEC 是什么概念？

NOT_FIXED_DEC 在MySQL源码中是一个常量，用于表示在对DECIMAL和NUMERIC类型的数据进行操作时，该数字的小数部分所需的精确度还未确定或固定。这个概念通常在MySQL的内部函数和方法中使用，尤其是涉及到计算或比较浮点数时。

在MySQL中，处理数字类型的函数需要知道操作数的小数精度，以便正确处理四舍五入和截断。当函数在不同上下文中处理DECIMAL或NUMERIC类型的数据而又无法预先知道精确的小数位数时，它可能会使用NOT_FIXED_DEC来表示这种不确定性。

例如，在进行数学计算或聚合函数（如SUM、AVG等）的过程中，可能需要将多个DECIMAL类型的值相加，这些值可能具有不同的小数精确度。为了正确处理结果值的小数点位数，需要确定各个参与操作数的精确度，而在操作过程开始时这可能尚未知晓。这种情况下，NOT_FIXED_DEC可能就会被用到，作为一个标志，直到真实的小数位数能够在运算过程中被确定。一旦确定了具体的小数位数，就可以替换NOT_FIXED_DEC为实际的小数精确度值。

请注意，随着MySQL的发展和版本更新，源码的实现细节可能会发生变化，因此具体的应用和实现可能会有所不同。如果你想获得更为详细的信息和上下文，请参考对应版本的MySQL源码和官方文档。