确定MariaDB中十进制数据类型列所需的精度和小数位数

9w11ddsr 于 2023-10-20 发布在其他

关注(0)|答案(1)|浏览(137)

我有大量的MariaDB 10.6表，其中有几个decimal(P,D)列和数百万行。这些列的精度（P）和比例（D）曾经被设置为不切实际的高水平，仅适应每种可能的输入，例如。decimal(24,10)。
现在我想根据这些表中的实际数据确定所需的P和D值。
我能想到的最好的办法就是

SELECT CEIL(LOG10(MAX(col))) AS maxI,
       MAX(CEIL(LOG10(CAST(REVERSE(SUBSTR(CAST(col % 1 AS VARCHAR(12)), 3)) AS INT)))) AS maxD
FROM tbl;

其中col是数据类型为decimal(24,10)的列，maxI是最大整数部分，maxP是小数点后的最大有效位数（因此maxP = maxI + maxD）。
虽然它确实有效，但它的速度并不令人惊讶，我想知道是否有更有效的方法到达那里。

mariadb

来源：https://stackoverflow.com/questions/77231346/determining-required-precision-and-scale-for-decimal-data-type-columns-in-mariad

1条答案

按热度按时间

oymdgrw71#

在处理精度数字时不要使用双精度函数（如LOG10），以避免警告和舍入错误。
计算小数点前的最大位数非常简单，因为这可以由优化器处理，而不涉及索引。对于无符号小数，你可以使用MAX()，对于有符号小数，你必须使用MIN()和MAX()：

select @maxI1:=LENGTH(TRUNCATE(MIN(col1),0)), @maxI2:=LENGTH(TRUNCATE(MAX(col1),0)) FROM tbl;

最多的十进制数有记录MAX(col1 - TRUNCATE(col1,0))。所以第二个陈述是：

SELECT IF(@maxI1 > @maxI2, @maxI1, @maxI2) as maxI,
       LENGTH(TRIM(MAX(col1 - TRUNCATE(col1, 0)))+0) -2 as maxD
FROM tbl;

速度比较（700万行）：

SELECT CEIL(LOG10(MAX(col1))) AS maxI, MAX(CEIL(LOG10(CAST(REVERSE(SUBSTR(CAST(col1 % 1 AS VARCHAR(12)), 3)) AS INT)))) AS maxD FROM tbl;
+------+------+
| maxI | maxD |
+------+------+
|   13 |   10 |
+------+------+
1 row in set, 40964 warnings (6.166 sec)

vs.

SELECT @maxI1:=LENGTH(TRUNCATE(MIN(col1),0)) as maxI1, @maxI2:=LENGTH(TRUNCATE(MAX(col1),0)) as maxI2 FROM tbl;
+-------+-------+
| maxI1 | maxI2 |
+-------+-------+
|     5 |    13 |
+-------+-------+
1 row in set (0.000 sec)

SELECT IF(@maxI1 > @maxI2, @maxI1, @maxI2) as maxI, LENGTH(TRIM(MAX(col1 - TRUNCATE(col1, 0)))+0) -2 as maxD FROM tbl;
+------+------+
| maxI | maxD |
+------+------+
|   13 |    7 |
+------+------+
1 row in set (4.168 sec)

赞(0）回复(0）举报 2023-10-20

我来回答

确定MariaDB中十进制数据类型列所需的精度和小数位数

1条答案

相关问题

热门标签

最新问答