MySQL Meta中的length字段 -- (1) 初始值的length计算

MySQL Meta中的length字段 – (1) 初始值的length计算
MySQL Meta中的length字段 – (2) length的推导
MySQL Meta中的length字段 – (3) length的推导举例
MySQL Meta中的length字段 – (4) 玩儿MySQL代码
后来的故事 - 想到哪写到哪百孔千疮漏洞百出MySQL MetaData Length实现
后来的故事 - Precision和Length对MySQL执行结果的影响

---

官方对于length的解释

unsigned int max_length

The maximum length of the result. The default max_length value differs depending on the result type of the function. For string functions, the default is the length of the longest argument. For integer functions, the default is 21 digits. For real functions, the default is 13 plus the number of decimal digits indicated by initid->decimals. (For numeric functions, the length includes any sign or decimal point characters.)

If you want to return a blob value, you can set max_length to 65KB or 16MB. This memory is not allocated, but the value is used to decide which data type to use if there is a need to temporarily store the data.

⬆️来源： http://docs.oracle.com/cd/E17952_01/refman-5.5-en/udf-calling.html

length是什么？

登陆mysql时，带上 --column-type-info 选项，那么对于任意一条有结果集输出的MySQL语句，它都会先输出结果集的MetaData。

例如：shell > mysql -h localhost -uroot -p111 --database=we  --column-type-info;
mysql> desc t1;
Field   1:  `Field`
Catalog:    `def`
Database:   ``
Table:      `COLUMNS`
Org_table:  ``
Type:       VAR_STRING
Collation:  latin1_swedish_ci (8)
Length:     64
Max_length: 11
Decimals:   0
Flags:      NOT_NULL


Field   2:  `Type`
Catalog:    `def`
Database:   ``
Table:      `COLUMNS`
Org_table:  ``
Type:       BLOB
Collation:  latin1_swedish_ci (8)
Length:     196605
Max_length: 14
Decimals:   0
Flags:      NOT_NULL BLOB


Field   3:  `Null`
Catalog:    `def`
Database:   ``
Table:      `COLUMNS`
Org_table:  ``
Type:       VAR_STRING
Collation:  latin1_swedish_ci (8)
Length:     3
Max_length: 3
Decimals:   0
Flags:      NOT_NULL


Field   4:  `Key`
Catalog:    `def`
Database:   ``
Table:      `COLUMNS`
Org_table:  ``
Type:       VAR_STRING
Collation:  latin1_swedish_ci (8)
Length:     3
Max_length: 3
Decimals:   0
Flags:      NOT_NULL


Field   5:  `Default`
Catalog:    `def`
Database:   ``
Table:      `COLUMNS`
Org_table:  ``
Type:       BLOB
Collation:  latin1_swedish_ci (8)
Length:     196605
Max_length: 0
Decimals:   0
Flags:      BLOB


Field   6:  `Extra`
Catalog:    `def`
Database:   ``
Table:      `COLUMNS`
Org_table:  ``
Type:       VAR_STRING
Collation:  latin1_swedish_ci (8)
Length:     27
Max_length: 0
Decimals:   0
Flags:      NOT_NULL




+-------------+----------------+------+-----+---------+-------+
| Field       | Type           | Null | Key | Default | Extra |
+-------------+----------------+------+-----+---------+-------+
| id          | int(11)        | NO   | PRI | NULL    |       |
| binaryvalue | varbinary(255) | YES  |     | NULL    |       |
| charvalue   | char(2)        | YES  |     | NULL    |       |
| bin2        | binary(3)      | YES  |     | NULL    |       |
+-------------+----------------+------+-----+---------+-------+
4 rows in set (0.00 sec)






mysql> select * from t1;
Field   1:  `id`
Catalog:    `def`
Database:   `we`
Table:      `t1`
Org_table:  `t1`
Type:       LONG
Collation:  binary (63)
Length:     11
Max_length: 1
Decimals:   0
Flags:      NOT_NULL PRI_KEY NO_DEFAULT_VALUE NUM PART_KEY

Field   2:  `binaryvalue`
Catalog:    `def`
Database:   `we`
Table:      `t1`
Org_table:  `t1`
Type:       VAR_STRING
Collation:  binary (63)
Length:     255
Max_length: 2
Decimals:   0
Flags:      BINARY

Field   3:  `charvalue`
Catalog:    `def`
Database:   `we`
Table:      `t1`
Org_table:  `t1`
Type:       STRING
Collation:  latin1_swedish_ci (8)
Length:     2
Max_length: 2
Decimals:   0
Flags:

Field   4:  `bin2`
Catalog:    `def`
Database:   `we`
Table:      `t1`
Org_table:  `t1`
Type:       STRING
Collation:  binary (63)
Length:     3
Max_length: 3
Decimals:   0
Flags:      BINARY


+----+-------------+-----------+------+
| id | binaryvalue | charvalue | bin2 |
+----+-------------+-----------+------+
|  1 | hi          | wo        | go   |
+----+-------------+-----------+------+
1 row in set (0.00 sec)

显示一个字段需要的最小字节数。
显示，意味着要转化为字符串；最小，意味着大一点没关系，小了不可以。

length字段的值来源于两方面：

• 初始值
• 推导运算

初始值，也来源于两方面：

• 常量定义
• table column定义

常量定义

见mysql-5.6/sql/sql_yacc.yy，NUM_literal，literal等的定义，如下，Item_int的第三个参数，就是length，可以看到，它的处理方式很直接粗暴，取了literal的length值。

NUM_literal:
    NUM
    {
      int error;
      $$= new (YYTHD->mem_root)
            Item_int($1,
                     (longlong) my_strtoll10($1.str, NULL, &error),
                     $1.length);
      if ($$ == NULL)
        MYSQL_YYABORT;
    }
  | LONG_NUM
    {
      int error;
      $$= new (YYTHD->mem_root)
            Item_int($1,
                     (longlong) my_strtoll10($1.str, NULL, &error),
                     $1.length);
      if ($$ == NULL)
        MYSQL_YYABORT;
    }

mysql> select  -003;
Field   1:  `-003`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       LONGLONG
Collation:  binary (63)
Length:     4
Max_length: 2
Decimals:   0
Flags:      NOT_NULL BINARY NUM

Table Column定义

代码还是在sql_yacc.cc中

type:
    int_type opt_field_length field_options { $$=$1; }
  | real_type opt_precision field_options { $$=$1; }
  | FLOAT_SYM float_options field_options { $$=MYSQL_TYPE_FLOAT; }
  | BIT_SYM
    {
      Lex->length= (char*) "1";
      $$=MYSQL_TYPE_BIT;
    }
  | BIT_SYM field_length
    {
      $$=MYSQL_TYPE_BIT;
    }
  | BOOL_SYM
    {
      Lex->length= (char*) "1";
      $$=MYSQL_TYPE_TINY;
    }
  | BOOLEAN_SYM
    {
      Lex->length= (char*) "1";
      $$=MYSQL_TYPE_TINY;
    }
  | char field_length opt_binary
    {
      $$=MYSQL_TYPE_STRING;
    }
  | char opt_binary
    {
      Lex->length= (char*) "1";
      $$=MYSQL_TYPE_STRING;
    }
  | nchar field_length opt_bin_mod
    {
      $$=MYSQL_TYPE_STRING;
      Lex->charset=national_charset_info;
    }


field_length:
    '(' LONG_NUM ')'      { Lex->length= $2.str; }
  | '(' ULONGLONG_NUM ')' { Lex->length= $2.str; }
  | '(' DECIMAL_NUM ')'   { Lex->length= $2.str; }
  | '(' NUM ')'           { Lex->length= $2.str; };

opt_field_length:
  /* empty */  { Lex->length=(char*) 0; /* use default length */ }
  | field_length { }
  ;

全部都是字面长度。对于varchar，也是如此。不过在后面的解析推导过程中，需要注意collation，补乘上collation的最大字节宽度。

Create Table中对应的Column解析代码位于field.cc

/**
  Initialize a column definition object. Column definition objects can be used
  to construct Field objects.

  @param thd                   Session/Thread handle.
  @param fld_name              Column name.
  @param fld_type              Column type.
  @param fld_length            Column length.
  @param fld_decimals          Number of digits to the right of the decimal
                               point (if any.)
  @param fld_type_modifier     Additional type information.
  @param fld_default_value     Column default expression (if any.)
  @param fld_on_update_value   The expression in the ON UPDATE clause.
  @param fld_comment           Column comment.
  @param fld_change            Column change.
  @param fld_interval_list     Interval list (if any.)
  @param fld_charset           Column charset.
  @param fld_geom_type         Column geometry type (if any.)

  @retval
    FALSE on success.
  @retval
    TRUE  on error.
*/

bool Create_field::init(THD *thd, const char *fld_name,
                        enum_field_types fld_type, const char *fld_length,
                        const char *fld_decimals, uint fld_type_modifier,
                        Item *fld_default_value, Item *fld_on_update_value,
                        LEX_STRING *fld_comment, const char *fld_change,
                        List<String> *fld_interval_list,
                        const CHARSET_INFO *fld_charset, uint fld_geom_type)
{

  ....

  if (fld_length != NULL)
  {
    errno= 0;
    length= strtoul(fld_length, NULL, 10);  // SQL中fld_length都是用字符串表示，转成整数
    if ((errno != 0) || (length > MAX_FIELD_BLOBLENGTH))
    {
      my_error(ER_TOO_BIG_DISPLAYWIDTH, MYF(0), fld_name, MAX_FIELD_BLOBLENGTH);
      DBUG_RETURN(TRUE);
    }

    if (length == 0)
      fld_length= NULL; /* purecov: inspected */
  }

  sign_len= fld_type_modifier & UNSIGNED_FLAG ? 0 : 1;

  switch (fld_type) {
  case MYSQL_TYPE_TINY:
    if (!fld_length)
      length= MAX_TINYINT_WIDTH+sign_len;
    allowed_type_modifier= AUTO_INCREMENT_FLAG;
    break;
  case MYSQL_TYPE_SHORT:
    if (!fld_length)
      length= MAX_SMALLINT_WIDTH+sign_len;
    allowed_type_modifier= AUTO_INCREMENT_FLAG;
    break;
  case MYSQL_TYPE_INT24:
    if (!fld_length)
      length= MAX_MEDIUMINT_WIDTH+sign_len;
    allowed_type_modifier= AUTO_INCREMENT_FLAG;
    break;

SELECT i+j FROM … 中对应i+j的解析见：sql_yacc.yy

select_item:
          remember_name table_wild remember_end
          {
            THD *thd= YYTHD;

            if (add_item_to_list(thd, $2))
              MYSQL_YYABORT;
          }
        | remember_name expr remember_end select_alias
          {
            THD *thd= YYTHD;
            DBUG_ASSERT($1 < $3);

            if (add_item_to_list(thd, $2))
              MYSQL_YYABORT;
            if ($4.str)
            {
              if (Lex->sql_command == SQLCOM_CREATE_VIEW &&
                  check_column_name($4.str))
              {
                my_error(ER_WRONG_COLUMN_NAME, MYF(0), $4.str);
                MYSQL_YYABORT;
              }
              $2->item_name.copy($4.str, $4.length, system_charset_info, false);
            }
            else if (!$2->item_name.is_set())
            {
              $2->item_name.copy($1, (uint) ($3 - $1), thd->charset());
            }
          }
        ;
        
/* all possible expressions */
expr:
          expr or expr %prec OR_SYM
          {
            /*
              Design notes:
              Do not use a manually maintained stack like thd->lex->xxx_list,
              but use the internal bison stack ($$, $1 and $3) instead.
              Using the bison stack is:
              - more robust to changes in the grammar,
              - guaranteed to be in sync with the parser state,
              - better for performances (no memory allocation).
            */
            Item_cond_or *item1;
            Item_cond_or *item3;
            if (is_cond_or($1))
      
....

simple_ident:
          ident
          {
            THD *thd= YYTHD;
            LEX *lex= thd->lex;
            sp_pcontext *pctx = lex->get_sp_current_parsing_ctx();
            sp_variable *spv;

            if (pctx && (spv= pctx->find_variable($1, false)))
            {
              Lex_input_stream *lip= &thd->m_parser_state->m_lip;
              sp_head *sp= lex->sphead;

              DBUG_ASSERT(sp);

              /* We're compiling a stored procedure and found a variable */
              if (! lex->parsing_options.allows_variable)
              {
                my_error(ER_VIEW_SELECT_VARIABLE, MYF(0));
                MYSQL_YYABORT;
              }

              $$=
                create_item_for_sp_var(
                  thd, $1, spv,
                  sp->m_parser_data.get_current_stmt_start_ptr(),
                  lip->get_tok_start_prev(),
                  lip->get_tok_end());

              if ($$ == NULL)
                MYSQL_YYABORT;

              lex->safe_to_cache_query= false;
            }
            else
            {
              SELECT_LEX *sel=Select;
              if ((sel->parsing_place != IN_HAVING) ||
                  (sel->get_in_sum_expr() > 0))
              {
                $$= new (thd->mem_root) Item_field(Lex->current_context(),
                                                   NullS, NullS, $1.str);
              }
              else
              {
                $$= new (thd->mem_root) Item_ref(Lex->current_context(),
                                                 NullS, NullS, $1.str);
              }
              if ($$ == NULL)
                MYSQL_YYABORT;
            }
          }
        | simple_ident_q { $$= $1; }
        ;