技術分享 | MySQL 字段長度限制的計算方法

• 2020 年 3 月 13 日
• 筆記

作者：kay 擅長 Oracle、MySQL、PostgresSQL 等多種數據庫領域； 擅長 Oracle、MySQL 性能優化、數據庫架構設計、數據庫故障修復、數據遷移以及恢復； 熱衷於研究 MySQL 數據庫內核源碼、分享技術文章，並擁有 Oracle OCP 認證； 就職於江蘇國泰新點軟件有限公司，DBA 技術團隊成員。

一、MySQL 限制回顧

之前在《MySQL Text 字段的限制》一文中講過了關於 MySQL 在 Server 層和 InnoDB 層的限制，但是限制的算法沒有詳細展開，這裡進行補充說明，先回顧一下 MySQL 的兩個限制：

1. MySQL Server 層 的限制為單條記錄的大小不超過 65535 位元組；

2. InnoDB 層 不能超過 innodb_page_size 大小的一半（實際上還要小一點，因為要扣除一些頁中元數據信息）, 以默認的 16K 設置為例，其限制為 8126。

另：以下計算方式均已 MySQL 5.7.27 進行說明。

二、Server 層限制的計算方法

2.1 計算過程

一般說來，如果是 MySQL Server 層做了限制，則返回如下報錯：

Row size too large. The maximum row size for the used table type,  not counting BLOBs, is 65535.  This includes storage overhead, check the manual.  You have to change some columns to TEXT or BLOBs

所以可以根據這個報錯來定位到計算方式，其關鍵函數路徑如下：

mysql_prepare_create_table    /* sql/sql_table.cc:3474 */    --> mysql_create_frm        /* sql/unireg.cc:107     */     --> pack_header           /* sql/unireg.cc:733     */

在 pack_header 中存在這部分代碼：

if (reclength > (ulong) file->max_record_length()) /* 右值為 65535 */  {    my_error(ER_TOO_BIG_ROWSIZE, /* 這個就是上面的報錯信息對應的狀態值 */              MYF(0),              static_cast<long>(file->max_record_length()));    DBUG_RETURN(1);  }

重點在於 reclength 的值是否 大於 65535。因此了解一下 reclength 的計算過程：

/* sql/unireg.cc:843 */  if (field->offset + data_offset + length > reclength)    reclength= field->offset + data_offset + length;    /*  field->offset 的計算方式  sql/sql_table.cc:3816  */  while ((sql_field=it++)) {       /* 省略部分 */      sql_field->offset= record_offset;  /* 當前記錄的數據偏移量 */      if (MTYP_TYPENR(sql_field->unireg_check) == Field::NEXT_NUMBER)        auto_increment++;        if (sql_field->stored_in_db)        record_offset+= sql_field->pack_length; /* 累加當前字段的pack_length */  }      /*  data_offset 的計算方式  sql/unireg.cc:139  */  data_offset= (create_info->null_bits + 7) / 8;      /*  length 的計算方式  sql/unireg.cc:833  */  length=field->pack_length;   /* 這個pack_length就是和字段類型相關，以varchar為例*/    /* sql/sql_table.cc:3082 */  sql_field->sql_type=    MYSQL_TYPE_VAR_STRING;  sql_field->pack_length= calc_pack_length(sql_field->sql_type,                                          (uint) sql_field->length);    關於 `calc_pack_length` 的計算方式如下：      size_t calc_pack_length(enum_field_types type, size_t length)  {    switch (type) {    case MYSQL_TYPE_VAR_STRING:    case MYSQL_TYPE_STRING:    case MYSQL_TYPE_DECIMAL:     return (length);    case MYSQL_TYPE_VARCHAR:     return (length + (length < 256 ? 1: 2));    case MYSQL_TYPE_YEAR:    case MYSQL_TYPE_TINY  : return 1;    case MYSQL_TYPE_SHORT : return 2;    case MYSQL_TYPE_INT24:    case MYSQL_TYPE_NEWDATE: return 3;    case MYSQL_TYPE_TIME: return 3;    case MYSQL_TYPE_TIME2:      return length > MAX_TIME_WIDTH ?             my_time_binary_length(length - MAX_TIME_WIDTH - 1) : 3;    case MYSQL_TYPE_TIMESTAMP: return 4;    case MYSQL_TYPE_TIMESTAMP2:      return length > MAX_DATETIME_WIDTH ?             my_timestamp_binary_length(length - MAX_DATETIME_WIDTH - 1) : 4;    case MYSQL_TYPE_DATE:    case MYSQL_TYPE_LONG  : return 4;    case MYSQL_TYPE_FLOAT : return sizeof(float);    case MYSQL_TYPE_DOUBLE: return sizeof(double);    case MYSQL_TYPE_DATETIME: return 8;    case MYSQL_TYPE_DATETIME2:      return length > MAX_DATETIME_WIDTH ?             my_datetime_binary_length(length - MAX_DATETIME_WIDTH - 1) : 5;    case MYSQL_TYPE_LONGLONG: return 8;   /* Don't crash if no longlong */    case MYSQL_TYPE_NULL  : return 0;    case MYSQL_TYPE_TINY_BLOB:    return 1+portable_sizeof_char_ptr;    case MYSQL_TYPE_BLOB:     return 2+portable_sizeof_char_ptr;    case MYSQL_TYPE_MEDIUM_BLOB:  return 3+portable_sizeof_char_ptr;    case MYSQL_TYPE_LONG_BLOB:    return 4+portable_sizeof_char_ptr;    case MYSQL_TYPE_GEOMETRY: return 4+portable_sizeof_char_ptr;    case MYSQL_TYPE_JSON:         return 4+portable_sizeof_char_ptr;    case MYSQL_TYPE_SET:    case MYSQL_TYPE_ENUM:    case MYSQL_TYPE_NEWDECIMAL:      abort(); return 0;                          // This shouldn't happen    case MYSQL_TYPE_BIT: return length / 8;    default:      return 0;    }  }

2.2 小結

根據上面計算方式的梳理，在 MySQL Server 層計算長度的公式，可以寫成這樣的偽代碼：

data_offset = (number_null_field + 7) / 8; /*number_null_field表示允許為null的字段數*/    total_length = 0;  for (int i = 0; i < n_fileds; i++) {      total_length += calc_pack_length(field_type, length)  }    /*  field_type:數據類型  length:數據的位元組數  注意varchar(100) 的位元組數，在utf8mb4環境下，位元組數為400  */    total_length += data_offset;

通過上述計算，需要滿足 total_length <= 65535，即可通過 MySQL Server 層的檢查。

三、InnoDB 層限制的計算方法

3.1 計算過程

InnooDB 層面如果出現長度超過限制，報錯如下所示：

ERROR 1118 (42000): Row size too large (> 8126).  Changing some columns to TEXT or BLOB may help.  In current row format, BLOB prefix of 0 bytes is stored inline.

通過對報錯信息的搜索，其判斷方式如下：

/* dict0dict.cc:2515 */  /* Check the size limit on leaf pages. */  if (rec_max_size >= page_rec_max) {      ib::error_or_warn(strict)          << "Cannot add field " << field->name          << " in table " << table->name          << " because after adding it, the row size is "          << rec_max_size          << " which is greater than maximum allowed"          " size (" << page_rec_max          << ") for a record on index leaf page.";        return(TRUE);  }

其中 page_rec_max 的定義如下：

/* dict0dict.cc:2431 */  page_rec_max = srv_page_size == UNIV_PAGE_SIZE_MAX                ? REC_MAX_DATA_SIZE - 1                : page_get_free_space_of_empty(comp) / 2;    /* srv0srv.cc:222 */  ulong srv_page_size = UNIV_PAGE_SIZE_DEF;    /* 這個是默認值(DEF)，如果my.cnf中配置page_size為32K，那這個值就是32K */    /* univ.i:343 */  # define UNIV_PAGE_SIZE_DEF    (1 << UNIV_PAGE_SIZE_SHIFT_DEF)    /* univ.i:332 */  # define UNIV_PAGE_SIZE_SHIFT_DEF    14    /* univ.i:341 */  # define UNIV_PAGE_SIZE_MAX    (1 << UNIV_PAGE_SIZE_SHIFT_MAX)    /* univ.i:330 */  # define UNIV_PAGE_SIZE_SHIFT_MAX    16    /* rem0rec.h:1092 */  # define REC_MAX_DATA_SIZE    16384

從上面的源碼定義中可以看到：

1. srv_page_size 的默認值為 1<<14 即 16384，而我們配置也是 16384；

2. UNIV_PAGE_SIZE_MAX 的值為 1<<16 即 65536；

因此 srv_page_size==UNIV_PAGE_SIZE_MAX 不成立，所以 page_rec_max = page_get_free_space_of_empty(comp) / 2

其中 page_get_free_space_of_empty 的定義如下：

/*************************************************************//**  Calculates free space if a page is emptied.  @return free space */  UNIV_INLINE  ulint  page_get_free_space_of_empty(  /*=========================*/      ulint   comp)       /*!< in: nonzero=compact page layout */  {      if (comp) { /* 現在發佈版本的配置，會走這個邏輯 */          return((ulint)(UNIV_PAGE_SIZE                     - PAGE_NEW_SUPREMUM_END                     - PAGE_DIR                     - 2 * PAGE_DIR_SLOT_SIZE));      } /* 16384 - 120 - 8 - 4 = 16252 */        return((ulint)(UNIV_PAGE_SIZE                 - PAGE_OLD_SUPREMUM_END                 - PAGE_DIR                 - 2 * PAGE_DIR_SLOT_SIZE));  }    /* univ.i:312 */  # define UNIV_PAGE_SIZE        ((ulint) srv_page_size)  /* 在我們的版本中為 16384 */    /* page0page.h:122 */  # define PAGE_NEW_SUPREMUM_END (PAGE_NEW_SUPREMUM + 8)  /* 38 + 36 + 2*10 + 2*5 + 8 + 8 = 120 */    /* page0page.h:119 */  # define PAGE_NEW_SUPREMUM    (PAGE_DATA + 2 * REC_N_NEW_EXTRA_BYTES + 8)    /* page0page.h:104 */  # define PAGE_DATA    (PAGE_HEADER + 36 + 2 * FSEG_HEADER_SIZE)    /* page0page.h:56 */  # define    PAGE_HEADER FSEG_PAGE_DATA    /* fsp0types.h:68 */  # define FSEG_PAGE_DATA        FIL_PAGE_DATA    /* fil0fil.h:517 */  # define FIL_PAGE_DATA        38U    /* fsp0types.h:81 */  # define FSEG_HEADER_SIZE    10    /* rem0rec.h:52 */  # define REC_N_NEW_EXTRA_BYTES    5    /* page0page.h:152 */  # define    PAGE_DIR        FIL_PAGE_DATA_END    /* fil0fil.h:525 */  # define FIL_PAGE_DATA_END    8    /* page0page.h:155 */  # define    PAGE_DIR_SLOT_SIZE  2

如上所示，page_get_free_space_of_empty(comp) 返回的值為 16252，即 page_rec_max = 16252 / 2，剛好等於8126，其實從上面的報錯結果( > 8126)也可以推測出來。

接下來我們看一下一條記錄實際長度（ rec_max_size）的計算方式:

我們把代碼精簡一下，其計算過程如下：

/* 下面是函數 dict_index_too_big_for_tree 中關於  rec_max_size 計算部分的內容 */  /* 涉及到的輔助函數，下面也有相關說明 */    rec_max_size = comp /* comp 為 true */          ? REC_N_NEW_EXTRA_BYTES  /* rem0rec.h:52:#define REC_N_NEW_EXTRA_BYTES    5 */          : REC_N_OLD_EXTRA_BYTES; /* rem0rec.h:49:#define REC_N_OLD_EXTRA_BYTES    6 */    rec_max_size += UT_BITS_IN_BYTES(new_index->n_nullable);    /*  這個宏的實現如下：    ut0ut.h:222  #define UT_BITS_IN_BYTES(b) (((b) + 7) / 8)    即 (允許為空的字段個數 + 7) / 8  */    for (i = 0; i < new_index->n_fields; i++) {      const dict_field_t* field          = dict_index_get_nth_field(new_index, i);      const dict_col_t*   col          = dict_field_get_col(field);      ulint           field_max_size;      ulint           field_ext_max_size;        /* In dtuple_convert_big_rec(), variable-length columns      that are longer than BTR_EXTERN_LOCAL_STORED_MAX_SIZE      may be chosen for external storage.        Fixed-length columns, and all columns of secondary      index records are always stored inline. */        /* Determine the maximum length of the index field.      The field_ext_max_size should be computed as the worst      case in rec_get_converted_size_comp() for      REC_STATUS_ORDINARY records. */        field_max_size = dict_col_get_fixed_size(col, comp);        /* 本質上是判斷是否定長，並獲得定長字段的位元組數*/        /*如果是定長，則直接 goto add_field_size 進行求值*/        if (field_max_size && field->fixed_len != 0) {          /* dict_index_add_col() should guarantee this */          ut_ad(!field->prefix_len                || field->fixed_len == field->prefix_len);          /* Fixed lengths are not encoded          in ROW_FORMAT=COMPACT. */          field_ext_max_size = 0;          goto add_field_size;      }        /*如果是變長，則計算最大值，然後求 field_ext_max_size的長度，      這個是用戶存儲字段長度的變量      這個只是默認初始值，後面會根據長度進行溢出頁的設置      */        field_max_size = dict_col_get_max_size(col);      field_ext_max_size = field_max_size < 256 ? 1 : 2;        if (field->prefix_len) {      /* 建表判斷長度時，直接走else即可，這裡會在建立二級索引時用到，但是和聚集索引不是一顆B_Tree，因此可以忽略 */          if (field->prefix_len < field_max_size)  {              field_max_size = field->prefix_len;          }      } else if (field_max_size > BTR_EXTERN_LOCAL_STORED_MAX_SIZE             && dict_index_is_clust(new_index)) {        /*      btr0types.h:56      #define BTR_EXTERN_LOCAL_STORED_MAX_SIZE                   (BTR_EXTERN_FIELD_REF_SIZE * 2)        btr0types.h:53      #define BTR_EXTERN_FIELD_REF_SIZE   FIELD_REF_SIZE        page0size.h:32      #define FIELD_REF_SIZE 20        所以這段代碼的意思為：      如果變長字段的最大值大於40 （溢出頁指針的2倍），則這個字段在頁內      只保留40個位元組，且長度變量設置為1，即總共佔用41個位元組。*/        /* In the worst case, we have a locally stored      column of BTR_EXTERN_LOCAL_STORED_MAX_SIZE bytes.      The length can be stored in one byte.  If the      column were stored externally, the lengths in      the clustered index page would be      BTR_EXTERN_FIELD_REF_SIZE and 2. */            field_max_size = BTR_EXTERN_LOCAL_STORED_MAX_SIZE;          field_ext_max_size = 1;      }        if (comp) {          /* Add the extra size for ROW_FORMAT=COMPACT.          For ROW_FORMAT=REDUNDANT, these bytes were          added to rec_max_size before this loop. */          rec_max_size += field_ext_max_size;      }      add_field_size:      rec_max_size += field_max_size;        /* Check the size limit on leaf pages. */      if (rec_max_size >= page_rec_max) {          ib::error_or_warn(strict)              << "Cannot add field " << field->name              << " in table " << table->name              << " because after adding it, the row size is "              << rec_max_size              << " which is greater than maximum allowed"              " size (" << page_rec_max              << ") for a record on index leaf page.";            return(TRUE);      }    / * 省略後續代碼，相關輔助函數：*/    ulint  dict_col_get_fixed_size(  /*====================*/      const dict_col_t*   col,    /*!< in: column */      ulint           comp)   /*!< in: nonzero=ROW_FORMAT=COMPACT */  {      return(dtype_get_fixed_size_low(col->mtype, col->prtype, col->len,                      col->mbminmaxlen, comp));      /*       根據實現，可以簡單的計算為：   1. 定長的為顯示指定的 字符數*字符寬度     - 例如 char(10), 在 utf8mb4 裏面，位元組為 40   2. 變長的直接返回0      */  }    /***********************************************************************//**  Returns the size of a fixed size data type, 0 if not a fixed size type.  @return fixed size, or 0 */  UNIV_INLINE  ulint  dtype_get_fixed_size_low(  /*=====================*/      ulint   mtype,      /*!< in: main type */      ulint   prtype,     /*!< in: precise type */      ulint   len,        /*!< in: length */      ulint   mbminmaxlen,    /*!< in: minimum and maximum length of                  a multibyte character, in bytes */      ulint   comp)       /*!< in: nonzero=ROW_FORMAT=COMPACT  */  {      switch (mtype) {      case DATA_SYS:  # ifdef UNIV_DEBUG          switch (prtype & DATA_MYSQL_TYPE_MASK) {          case DATA_ROW_ID:              ut_ad(len == DATA_ROW_ID_LEN);              break;          case DATA_TRX_ID:              ut_ad(len == DATA_TRX_ID_LEN);              break;          case DATA_ROLL_PTR:              ut_ad(len == DATA_ROLL_PTR_LEN);              break;          default:              ut_ad(0);              return(0);          }  # endif /* UNIV_DEBUG */      // Fall through.      case DATA_CHAR:      case DATA_FIXBINARY:      case DATA_INT:      case DATA_FLOAT:      case DATA_DOUBLE:      case DATA_POINT:          return(len);      case DATA_MYSQL:  # ifndef UNIV_HOTBACKUP          if (prtype & DATA_BINARY_TYPE) {              return(len);          } else if (!comp) {              return(len);          } else {  #ifdef UNIV_DEBUG              ulint   i_mbminlen, i_mbmaxlen;                innobase_get_cset_width(                  dtype_get_charset_coll(prtype),                  &i_mbminlen, &i_mbmaxlen);                ut_ad(DATA_MBMINMAXLEN(i_mbminlen, i_mbmaxlen)                    == mbminmaxlen);  # endif /* UNIV_DEBUG */              if (DATA_MBMINLEN(mbminmaxlen)                  == DATA_MBMAXLEN(mbminmaxlen)) {                  return(len);              }          }  # else /* !UNIV_HOTBACKUP */          return(len);  # endif /* !UNIV_HOTBACKUP */          /* fall through for variable-length charsets */      case DATA_VARCHAR:      case DATA_BINARY:      case DATA_DECIMAL:      case DATA_VARMYSQL:      case DATA_VAR_POINT:      case DATA_GEOMETRY:      case DATA_BLOB:          return(0);      default:          ut_error;      }        return(0);  }    ulint  dict_col_get_max_size(  /*==================*/   const dict_col_t*   col)    /*!< in: column */  {     return(dtype_get_max_size_low(col->mtype, col->len));  }    ulint  dtype_get_max_size_low(  /*===================*/      ulint   mtype,      /*!< in: main type */      ulint   len)        /*!< in: length */  {      switch (mtype) {          case DATA_SYS:          case DATA_CHAR:          case DATA_FIXBINARY:          case DATA_INT:          case DATA_FLOAT:          case DATA_DOUBLE:          case DATA_MYSQL:          case DATA_VARCHAR:          case DATA_BINARY:          case DATA_DECIMAL:          case DATA_VARMYSQL:          case DATA_POINT:              return(len);          case DATA_VAR_POINT:          case DATA_GEOMETRY:          case DATA_BLOB:              break;          default:              ut_error;      }        return(ULINT_MAX);  }

3.2 小結

根據上面計算方式的梳理，在 InnoDB Server 層計算長度的公式，可以寫成這樣的偽代碼：

rec_max_size = 5;  /*    innodb header 中固定的兩個值    DB_TRX_ID: 6 Byte    DB_ROLL_PTR: 7 Byte  */  rec_max_size += 6+7;    /* rem0rec.h:70  # define REC_NODE_PTR_SIZE    4    dict0dict.cc:2536  rec_max_size + REC_NODE_PTR_SIZE >= page_ptr_max  */  rec_max_size += 4;    rec_max_size += (number_null_field + 7) / 8; /*number_null_field表示允許為null的字段數*/    for (int i = 0; i < n_fileds; i++) {      /* 定長字段 */      if (field.type is fixed) {          rec_max_size += filed.length;          /*char需要 x4個位元組的 utf8mb4類型*/          continue;      }        /* 變長字段 */      field_ext_max_size = 1;      if (field.type is variable) {          field_max_size = field.length > 40 ? 40 : field.length;      }        rec_max_size += field_max_size + field_ext_max_size;  }

通過上述計算，需要滿足 rec_max_size < 8126，即可通過 InnoDB 層的檢查。

四、總結

1. 必須在 MySQL Server 層和 InnoDB 層同時滿足上述條件，才能建表成功； 2. 如果出現上述報錯情況，大部分是因為 varchar 等設置過大，建議可以將一些字段逐步縮小，或者用 text 進行代替