MySQL Meta中的length字段 -- (3) length的推导举例

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

---

为了搞清楚下面一条SQL结果中length的来龙去脉，可费了老鼻子劲了！祭出了终极大法：修改MySQL源码，加调试日志。效果不错，比跟源码有效率，验证了一些结果。

首先，看SQL：

CREATE TABLE `t2` (
  `i` int(8) DEFAULT NULL,
  `j` bigint(15) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

insert into t2 values (3, 40);

mysql> select i, j , i*j, 3, 40, 120, 3*40, i*40, 3*j from t2;
Field   1:  `i`
Type:       LONG
Collation:  binary (63)
Length:     11
Max_length: 1
Decimals:   0
Flags:      NUM

Field   2:  `j`
Type:       LONGLONG
Collation:  binary (63)
Length:     20
Max_length: 2
Decimals:   0
Flags:      NOT_NULL NO_DEFAULT_VALUE NUM

Field   3:  `i*j`
Type:       LONGLONG
Collation:  binary (63)
Length:     30
Max_length: 3
Decimals:   0
Flags:      BINARY NUM

Field   4:  `3`
Type:       LONGLONG
Collation:  binary (63)
Length:     1
Max_length: 1
Decimals:   0
Flags:      NOT_NULL BINARY NUM

Field   5:  `40`
Type:       LONGLONG
Collation:  binary (63)
Length:     2
Max_length: 2
Decimals:   0
Flags:      NOT_NULL BINARY NUM

Field   6:  `120`
Type:       LONGLONG
Collation:  binary (63)
Length:     3
Max_length: 3
Decimals:   0
Flags:      NOT_NULL BINARY NUM

Field   7:  `3*40`
Type:       LONGLONG
Collation:  binary (63)
Length:     4
Max_length: 3
Decimals:   0
Flags:      NOT_NULL BINARY NUM

Field   8:  `i*40`
Type:       LONGLONG
Collation:  binary (63)
Length:     13
Max_length: 3
Decimals:   0
Flags:      BINARY NUM

Field   9:  `3*j`
Type:       LONGLONG
Collation:  binary (63)
Length:     21
Max_length: 3
Decimals:   0
Flags:      NOT_NULL BINARY NUM


+------+----+------+---+----+-----+------+------+-----+
| i    | j  | i*j  | 3 | 40 | 120 | 3*40 | i*40 | 3*j |
+------+----+------+---+----+-----+------+------+-----+
|    3 | 40 |  120 | 3 | 40 | 120 |  120 |  120 | 120 |
+------+----+------+---+----+-----+------+------+-----+
1 row in set (0.01 sec)

通过在MySQL增加日志

  sql_print_information("Item::decimal_precision restype(%u) fieldtype(%u) uflag(%d) dec(%u) "
      "max_length(%u) mbmaxlen(%u) dec_prec(%u)",
      restype,
      field_type(),
      unsigned_flag,
      decimals,
      max_length,
      NULL == collation.collation ? collation.collation->mbmaxlen : 0,
      dec_prec);

  sql_print_information("mul_precision uflag(%d) dec0(%u) dec1(%u) dec(%u)"
      "dec0_prec(%u) dec1_prec(%u) est(%u) prec(%u) len(%u)",
      unsigned_flag,
      args[0]->decimals,
      args[1]->decimals,
      decimals,
      args[0]->decimal_precision(),
      args[1]->decimal_precision(),
      est_prec,
      precision,
      max_length);

能看到如下输出

i: Item::decimal_precision restype(2) fieldtype(3) uflag(0) dec(0) max_length(11) mbmaxlen(0) dec_prec(10)
j: Item::decimal_precision restype(2) fieldtype(8) uflag(0) dec(0) max_length(20) mbmaxlen(0) dec_prec(19)
i*j: mul_precision uflag(0) dec0(0) dec1(0) dec(0)dec0_prec(10) dec1_prec(19) est(29) prec(29) len(30)
3*40: mul_precision uflag(0) dec0(0) dec1(0) dec(0)dec0_prec(1) dec1_prec(2) est(3) prec(3) len(4)
i: Item::decimal_precision restype(2) fieldtype(3) uflag(0) dec(0) max_length(11) mbmaxlen(0) dec_prec(10)
i*40: mul_precision uflag(0) dec0(0) dec1(0) dec(0)dec0_prec(10) dec1_prec(2) est(12) prec(12) len(13)
j: Item::decimal_precision restype(2) fieldtype(8) uflag(0) dec(0) max_length(20) mbmaxlen(0) dec_prec(19)
3*j: mul_precision uflag(0) dec0(0) dec1(0) dec(0)dec0_prec(1) dec1_prec(19) est(20) prec(20) len(21)

可以知道，在做乘法运算的时候，i和j在Schema中定义的宽度8、15都被忽略了，直接采用了int和bigint的默认长度来计算。

另外，上面这种输出日志的方式太低级了，还可以用高级的东西：

void push_warning_printf(THD *thd, Sql_condition::enum_warning_level level,
       uint code, const char *format, ...)
{
  va_list args;
  char    warning[MYSQL_ERRMSG_SIZE];
  DBUG_ENTER("push_warning_printf");
  DBUG_PRINT("enter",("warning: %u", code));

  DBUG_ASSERT(code != 0);
  DBUG_ASSERT(format != NULL);

  va_start(args,format);
  my_vsnprintf_ex(&my_charset_utf8_general_ci, warning,
                  sizeof(warning), format, args);
  va_end(args);
  push_warning(thd, level, code, warning);
  DBUG_VOID_RETURN;
}

这样就可以通过show warnings来直接查看日志了，更高级哦！例如：

  push_warning_printf(current_thd, Sql_condition::WARN_LEVEL_WARN,
                      ER_WARN_DATA_OUT_OF_RANGE,
                      ER(ER_WARN_DATA_OUT_OF_RANGE),
                      item_name.ptr(), 1L);

再看CONCAT函数，有很多神奇的事情会发生。先上SQL：

CREATE TABLE `t5` (
   `i` float(30) zerofill NOT NULL,
   `j` bigint(5) NOT NULL 
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

insert into t5 values (0.01, 0.1), (1, 1);

mysql> select concat(j, i), i, j from t5;
Field   1:  `concat(j, i)`
Type:       VAR_STRING
Collation:  utf8_general_ci (33)
Length:     126
Max_length: 23
Decimals:   31
Flags:

Field   2:  `i`
Type:       DOUBLE
Collation:  binary (63)
Length:     22
Max_length: 22
Decimals:   31
Flags:      NOT_NULL UNSIGNED ZEROFILL NO_DEFAULT_VALUE NUM

Field   3:  `j`
Type:       LONGLONG
Collation:  binary (63)
Length:     5
Max_length: 1
Decimals:   0
Flags:      NOT_NULL NO_DEFAULT_VALUE NUM


+-------------------------+------------------------+---+
| concat(j, i)            | i                      | j |
+-------------------------+------------------------+---+
| 00000000000000000000.01 | 0000000000000000000.01 | 0 |
| 10000000000000000000001 | 0000000000000000000001 | 1 |
+-------------------------+------------------------+---+
2 rows in set (0.00 sec)

上面的结果，是不是很神奇？ i float(30) zerofill在内部被强制改成了 i double zerofill。如果写成 i float(5) zerofill，则会被强制改写成 i float zerofill，该行为在MySQL文档中有记录。这个改写是发生在解析阶段，通过show create table能看出：

mysql> show create table t5;
+-------+------------------------------------------------------+
| Create Table                                                                                                                  +-------+--------------------------------------------------------+
CREATE TABLE `t5` (
  `i` double unsigned zerofill NOT NULL,
  `j` bigint(5) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 
+-------+------------------------------------------------------+
1 row in set (0.00 sec)

concat(j, i)的length是126，推导过程如下： i的length为22（double的默认宽度），j的length为20（bigint默认宽度）， （22+20 + scale_flag_fix） * 3 = 126。 scale_flag_fix为符号和小数点的修正值，一般为0或1，详细算法见MySQL源码或者上一篇文章贴出的源码。3是utf8的最大字符宽度。

对于结果也需要解释一下。concat(j=1,i=1)，为什么结果不是11，而是10000000000000000000001呢？这是因为i有zerofill属性，MySQL将数值转换成字符串的时候会根据定义的显示宽度在结果前面补0。原来我们以为补0操作只会发生在MySQL对客户端输出结果的时候，通过concat实验却发现在内部运算阶段只要将数值强转为字符串，就会补0。例如SUBSTR函数：

mysql> select substr(i, 15, 4) , substr(i, 15), i from t5;
Field   1:  `substr(i, 15, 4)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  utf8_general_ci (33)
Length:     12
Max_length: 4
Decimals:   31
Flags:

Field   2:  `substr(i, 15)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  utf8_general_ci (33)
Length:     24
Max_length: 8
Decimals:   31
Flags:

Field   3:  `i`
Catalog:    `def`
Database:   `test`
Table:      `t5`
Org_table:  `t5`
Type:       DOUBLE
Collation:  binary (63)
Length:     22
Max_length: 22
Decimals:   31
Flags:      NOT_NULL UNSIGNED ZEROFILL NO_DEFAULT_VALUE NUM


+------------------+---------------+------------------------+
| substr(i, 15, 4) | substr(i, 15) | i                      |
+------------------+---------------+------------------------+
| 0000             | 00000.01      | 0000000000000000000.01 |
| 0000             | 00000001      | 0000000000000000000001 |
+------------------+---------------+------------------------+
2 rows in set (0.00 sec)