文章详情

登录

短信预约信息系统项目管理师 报名、考试、查分时间动态提醒

选择考试省份

  • 北京
  • 上海
  • 天津
  • 重庆
  • 河北
  • 山东
  • 辽宁
  • 黑龙江
  • 吉林
  • 甘肃
  • 青海
  • 河南
  • 江苏
  • 湖北
  • 湖南
  • 江西
  • 浙江
  • 广东
  • 云南
  • 福建
  • 海南
  • 山西
  • 四川
  • 陕西
  • 贵州
  • 安徽
  • 广西
  • 内蒙
  • 西藏
  • 新疆
  • 宁夏
  • 兵团

请输入下面的图形验证码

提交验证

短信预约提醒成功

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

2015-02-24 04:49

关注

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

一、MySQL 限制回顾

之前在《MySQL Text 字段的限制》一文中讲过了关于 MySQL 在 Server 层和 InnoDB 层的限制，但是限制的算法没有详细展开，这里进行补充说明，先回顾一下 MySQL 的两个限制

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    
  --> mysql_create_frm        
   --> pack_header

在 pack_header 中存在这部分代码：

if (reclength > (ulong) file->max_record_length()) 
{
  my_error(ER_TOO_BIG_ROWSIZE, 
            MYF(0),
            static_cast(file->max_record_length()));
  DBUG_RETURN(1);
}

重点在于 reclength 的值是否 大于65535。因此了解一下 reclength 的计算过程：


if (field->offset + data_offset + length > reclength)
  reclength= field->offset + data_offset + length;

 
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; 
}
	

 
data_offset= (create_info->null_bits + 7) / 8;    


 
length=field->pack_length;
 


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;   
  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; 

total_length = 0;
for (int i = 0; i < n_fileds; i++) {
    total_length += calc_pack_length(field_type, length)
}

	

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.

通过对报错信息的搜索，其判断方式如下：



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 的定义如下：


page_rec_max = srv_page_size == UNIV_PAGE_SIZE_MAX
              ? REC_MAX_DATA_SIZE - 1
              : page_get_free_space_of_empty(comp) / 2;


ulong srv_page_size = UNIV_PAGE_SIZE_DEF; 




# define UNIV_PAGE_SIZE_DEF	(1 << UNIV_PAGE_SIZE_SHIFT_DEF)


# define UNIV_PAGE_SIZE_SHIFT_DEF	14


# define UNIV_PAGE_SIZE_MAX	(1 << UNIV_PAGE_SIZE_SHIFT_MAX)


# define UNIV_PAGE_SIZE_SHIFT_MAX	16


# define REC_MAX_DATA_SIZE	16384

从上面的源码定义中可以看到：

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 的定义如下：


UNIV_INLINE
ulint
page_get_free_space_of_empty(

    ulint   comp)       
{
    if (comp) { 
        return((ulint)(UNIV_PAGE_SIZE
                   - PAGE_NEW_SUPREMUM_END
                   - PAGE_DIR
                   - 2 * PAGE_DIR_SLOT_SIZE));
    } 

    return((ulint)(UNIV_PAGE_SIZE
               - PAGE_OLD_SUPREMUM_END
               - PAGE_DIR
               - 2 * PAGE_DIR_SLOT_SIZE));
}


# define UNIV_PAGE_SIZE		((ulint) srv_page_size)   



# define PAGE_NEW_SUPREMUM_END (PAGE_NEW_SUPREMUM + 8)



# define PAGE_NEW_SUPREMUM	(PAGE_DATA + 2 * REC_N_NEW_EXTRA_BYTES + 8)


# define PAGE_DATA	(PAGE_HEADER + 36 + 2 * FSEG_HEADER_SIZE)


# define	PAGE_HEADER	FSEG_PAGE_DATA


# define FSEG_PAGE_DATA		FIL_PAGE_DATA


# define FIL_PAGE_DATA		38U


# define FSEG_HEADER_SIZE	10


# define REC_N_NEW_EXTRA_BYTES	5


# define	PAGE_DIR		FIL_PAGE_DATA_END


# define FIL_PAGE_DATA_END	8


# define	PAGE_DIR_SLOT_SIZE	2

如上所示，page_get_free_space_of_empty(comp) 返回的值为 16252，即 page_rec_max = 16252 / 2，刚好等于8126，其实从上面的报错结果(> 8126)也可以推测出来。

接下来我们看一下一条记录实际长度（rec_max_size）的计算方式:

我们把代码精简一下，其计算过程如下：




rec_max_size = comp 
        ? REC_N_NEW_EXTRA_BYTES  
        : REC_N_OLD_EXTRA_BYTES; 
	             
rec_max_size += UT_BITS_IN_BYTES(new_index->n_nullable);



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;
    
    
    
    
    
    field_max_size = dict_col_get_fixed_size(col, comp);  

	
    
    

    if (field_max_size && field->fixed_len != 0) {
        
        ut_ad(!field->prefix_len
              || field->fixed_len == field->prefix_len);
        
        field_ext_max_size = 0;
        goto add_field_size;
    }
    
    

    field_max_size = dict_col_get_max_size(col);
    field_ext_max_size = field_max_size < 256 ? 1 : 2;
    
    if (field->prefix_len) { 
	
        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)) {
         
	

	

        field_max_size = BTR_EXTERN_LOCAL_STORED_MAX_SIZE;
        field_ext_max_size = 1;
    }
    
    if (comp) {
        
        rec_max_size += field_ext_max_size;
    }
	add_field_size:
	rec_max_size += field_max_size;
    
    
    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,    
    ulint           comp)   
{
    return(dtype_get_fixed_size_low(col->mtype, col->prtype, col->len,
                    col->mbminmaxlen, comp));
    
}


UNIV_INLINE
ulint
dtype_get_fixed_size_low(

	ulint	mtype,		
	ulint	prtype,		
	ulint	len,		
	ulint	mbminmaxlen,	
	ulint	comp)		
{
	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 
	// 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 
			if (DATA_MBMINLEN(mbminmaxlen)
			    == DATA_MBMAXLEN(mbminmaxlen)) {
				return(len);
			}
		}
# else 
		return(len);
# endif 
		
	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)    
{
   return(dtype_get_max_size_low(col->mtype, col->len));
}

ulint
dtype_get_max_size_low(

    ulint   mtype,      
    ulint   len)        
{
    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;

rec_max_size += 6+7;


rec_max_size += 4; 

rec_max_size += (number_null_field + 7) / 8; 

for (int i = 0; i < n_fileds; i++) {
    
    if (field.type is fixed) {
        rec_max_size += filed.length; 
		
        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 层的检查

四、总结

必须在 MySQL Server 层和 InnoDB 层同时满足上述条件，才能建表成功；
2.如果出现上述报错情况，大部分是因为 varchar 等设置过大，建议可以将一些字段逐步缩小，或者用 text 进行代替；

阅读原文内容投诉

免责声明：

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题

  • 2024年上半年信息系统项目管理师第二批次真题及答案解析（完整版）

    难度     801人已做
    查看

  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     348人已做
    查看

  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     311人已做
    查看

  • 2024年上半年软考高项第一、二批次真题考点汇总（完整版）

    难度     432人已做
    查看

  • 2024年上半年系统架构设计师考试综合知识真题

    难度     220人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机

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

技术分享 | MySQL 字段长度限制的计算方法
数据库2015-02-24

技术分享 | MySQL TEXT 字段的限制

技术分享 | MySQL TEXT 字段的限制
数据库2020-11-24
位置：首页-资讯-数据库
咦！没有更多了？去看看其它编程学习网 内容吧
首页课程
资料下载问答资讯