MySQL Meta中的length字段 -- (5) 开始懂了

前情提要

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

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

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

混乱的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无意义。

总结

1. 字符类型在这四大类中是最简单的，其次是定点数，整形和浮点数都有点Bug，根本就无法“理解”
2. Display Width 仅仅在有zerofill、且为基础列的时候有意义
3. 当 Display Width 有意义的时候，Display Width = Length
4. Length 总是有意义，它表示一个列转化成字符串时需要用多少个字节来容纳
5. Length/DisplayWidth 和 Precision 没有必然联系，特别注意整形中的这种区别。
6. CREATE TABLE语句中的数字，对不同类型的意义不同：
   
   • 整形：display width
   • 浮点：precision、scale、display width、length
   • 定点：precision、scale、display width、length
   • 字符：length