MySQL Meta 信息与 CREATE TABLE 的对应关系

2021-07-19 04:14:59

前情提要

下面四篇文章是数月之前对length的理解

MySQL Meta中的length字段 -- (1) 初始值的length计算
 MySQL Meta中的length字段 -- (2) length的推导
 MySQL Meta中的length字段 -- (3) length的推导举例
 MySQL Meta中的length字段 -- (4) 玩儿MySQL代码

温故而知新，几个月后，对 length 等 meta 信息的理解又进一步，本文再记之。

混乱的MySQL定义

用“混乱”这个词有点大言不惭，但是，MySQL通过SQL语句描述 Length、Scale、Precision、Display Width这几个概念，描述手法真的很混乱。

你能清楚地描述 Length、Scale、Precision、Display Width 四个概念吗？他们的区别与关联是什么？CREATE TABLE 语句如何表达这四个概念？

如果你能说清楚，就已经掌握，不用看本文了。说不清楚，就听我慢慢道来。

本文简单起见，只以 MySQL 的四大类数据类型作为范例讨论：

整形（Int、TinyInt、MediumInt、BigInt）
浮点（Float、Double）
字符串（Binary、Char、VarBinary、VarChar）
定点数（Decimal）

创建一个包含上面4中类型的表：

CREATE TABLE sample (
    i int(3),
    f double(4, 1),
    d decimal(6, 1),
    c varchar(5)
);

i int(3)

其中 3 仅仅表示display width，i 的 precision 为 11，也就是说，INSERT INTO sample (i) VALUES (1234567) 是没问题的，尽管1234567的位数超过了3位。它的 SCALE 为 0。它的 LENGTH 是 3，它的 LENGTH 是 3，它的 LENGTH 是 3（重要事情说三遍！！！），display width 等于 length。

mysql> select i from sample;
Field   1:  `i`
Catalog:    `def`
Database:   `test`
Table:      `sample`
Org_table:  `sample`
Type:       LONG
Collation:  binary (63)
Length:     3
Max_length: 7
Decimals:   0
Flags:      NUM

在这种情况下，如果客户端傻乎乎地按照Length的值分配内存，必将Buffer溢出。怎么办？客户端才不会相信服务器传来的这个值，它完全可以根据Type=Long来分配一个最大Buffer，确保任意整数都能放得下。

理解了上面的概念后，回去好好看看自己写的 Server 代码，是不是把 Length 和 Precision 的概念弄混了？再来个例子加强理解：

mysql> INSERT INTO sample (i) VALUES (123456789012);
ERROR 1264 (22003): Out of range value for column 'i' at row 1
mysql> INSERT INTO sample (i) VALUES (1234567890);
Query OK, 1 row affected (0.01 sec)

f double(4, 1),

4 表示 precision，1 表示 scale，length 为4 + 1 + 1 = 6。其中 length 的算法为 precision + 小数点1位 + 符号位1位。 display width 有意义的前提是有 ZEROFILL 标记。如果有 ZEROFILL 标记，则自动为 UNSIGNED，所以 display width = precision + 小数点1位， length = precision + 小数点 1 位。

总结一下：没有ZEROFILL，LENGTH 有意义， DISPLAY WIDTH 无意义；有 ZEROFILL，LENGTH 和 DISPLAY WIDTH 相等。

mysql> INSERT INTO sample(f) VALUES (1234.1);
ERROR 1264 (22003): Out of range value for column 'f' at row 1
mysql> INSERT INTO sample(f) VALUES (123.1);
Query OK, 1 row affected (0.01 sec)

d decimal(6, 1),

因为 decimal 会用于金融领域，MySQL 那帮孙子也谨慎起来，把 Decimal 的行为实现得非常规范，所有的行为都可以解释。
6 表示 precision，1 表示 scale，Length = precision + 小数点1 + 符号位1 = 8。如果后面加上 ZEROFILL，则 Length = precision + 小数点 = 7， Display width = Length = 7。

下面专门写一个CASE来验证上面对Decimal的分析，同时证明 MySQL 那帮人的不严肃。按道理讲，Decimal 和 Double 在 precision、scale、length 上的表现行为应该是一致的，但实际偏偏不是，请看：

mysql> CREATE TABLE x_zf (f_zf double(6, 1) zerofill, d_zf decimal(6, 1) zerofill );
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE x (  f double(6, 1),   d decimal(6, 1) );
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO x_zf VALUES (1.1, 1.1);
Query OK, 1 row affected (0.06 sec)

mysql> INSERT INTO x VALUES (1.1, 1.1);
Query OK, 1 row affected (0.02 sec)

mysql> SELECT * FROM x_zf;
Field   1:  `f_zf`
Type:       DOUBLE
Length:     6
Decimals:   1
Flags:      UNSIGNED ZEROFILL NUM

Field   2:  `d_zf`
Type:       NEWDECIMAL
Collation:  binary (63)
Length:     7
Decimals:   1
Flags:      UNSIGNED ZEROFILL NUM

+--------+---------+
| f_zf   | d_zf    |
+--------+---------+
| 0001.1 | 00001.1 |
+--------+---------+
1 row in set (0.00 sec)

看！除了Type不同，其余都一样，但计算出来的Length、填充的0的个数，居然不一样。没别的原因，MySQL 的浮点类型实现得不对。

mysql> SELECT * FROM x;
Field   1:  `f`
Type:       DOUBLE
Length:     6
Decimals:   1
Flags:      NUM

Field   2:  `d`
Type:       NEWDECIMAL
Length:     8
Decimals:   1
Flags:      NUM


+------+------+
| f    | d    |
+------+------+
|  1.1 |  1.1 |
+------+------+
1 row in set (0.00 sec)

c varchar(5)

precision、scale都无意义，默认为0，LENGTH=5，Display width无意义。

总结

字符类型在这四大类中是最简单的，其次是定点数，整形和浮点数都有点Bug，根本就无法“理解”
Display Width 仅仅在有zerofill、且为基础列的时候有意义
当 Display Width 有意义的时候，Display Width = Length
Length 总是有意义，它表示一个列转化成字符串时需要用多少个字节来容纳
CREATE TABLE语句中的数字，对不同类型的意义不同：
- 整形：display width
- 浮点：precision、scale、display width、length
- 定点：precision、scale、display width、length
- 字符：length

码农公寓

前情提要

混乱的MySQL定义

总结

相关文章