广告

扫码关注官方微信

扫码下载APP
返回顶部
首页 > 资讯 > 数据库 >技术分享 | MySQL 字段长度限制的计算方法
  • 884

0
分享到

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

技术分享|MySQL字段长度限制的计算方法 2015-02-24 04:02:40 884人浏览 猪猪侠
摘要

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

技术分享 | 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 进行代替；

您可能感兴趣的文档:
点击免费下载>>软考高级考试备考技巧/历年真题/备考精华资料

--结束END--

本文标题: 技术分享 | MySQL 字段长度限制的计算方法

本文链接: https://www.lsjlt.com/news/4670.html(转载时请注明来源链接)

有问题或投稿请发送至: 邮箱/279061341@qq.com    QQ/279061341

本篇文章演示代码以及资料文档资料下载

下载Word文档到电脑，方便收藏和打印～

下载Word文档
去做题
猜你喜欢
软考高级职称资格查询
软考职称历年真题下载
软考职称资料下载
热门wiki
近期文章
推荐阅读
热门问答
热门标签
编程网，编程工程师的家园，是目前国内优秀的开源技术社区之一，形成了由开源软件库、代码分享、资讯、协作翻译、讨论区和博客等几大频道内容，为IT开发者提供了一个发现、使用、并交流开源技术的平台。

  • 官方手机版

  • 微信公众号

  • 商务合作