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)