技术分享 | MySQL 字段长度限制的计算方法

/* 下面是函数 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);
}