Skip to main content

mysql字符串最大长度

· 5 min read

本文主要是记录mysql各种类型的字符串受什么限制。

前言​

今天遇到一个特别的事情：把一个pdf的文档转成html然后存进mysql里面，所以我用了text 的字段来存。 结果读出来的时候发现少了一截。搜索了一番才发现text居然最大只能支持16kb的字节的内容。

字节和字符​

如果你写过php,你可以比较清晰地知道strlen("你好")和mb_strlen("你好")两者的区别。
如果是java的话，字节流的InputStream 和OutputStream 或者writer和reader这两个系列的区别你肯定也不陌生。

mysql字符串的长度与类型关系​

String Type Storage Requirements

In the following table, M represents the declared column length in characters for nonbinary string types and bytes for binary string types. L represents the actual length in bytes of a given string value.

Data TypeStorage Required
CHAR(M)The compact family of InnoDB row formats optimize storage for variable-length character
BINARY(M)M bytes, 0 <= M <= 255
VARCHAR(M), VARBINARY(M)L + 1 bytes if column values require 0 − 255 bytes, L + 2 bytes if values may require more than 255 bytes
TINYBLOB, TINYTEXTL + 1 bytes, where L < 28
BLOB, TEXTL + 2 bytes, where L < 216
MEDIUMBLOB, MEDIUMTEXTL + 3 bytes, where L < 224
LONGBLOB, LONGTEXTL + 4 bytes, where L < 232
ENUM('value1','value2',...)1 or 2 bytes, depending on the number of enumeration values (65,535 values maximum)
SET('value1','value2',...)1, 2, 3, 4, or 8 bytes, depending on the number of set members (64 members maximum)

来源

CHAR​

CHAR 最大是255个字符

用如下的sql创建256个字符的char类型字符串会报错误

ERROR 1074 (42000): Column length too big for column 'name' (max = 255); use BLOB or TEXT instead

CREATE TABLE `test123` ( `name` char(256)) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;
(gdb) bt
#0  my_error (nr=1074, MyFlags=0) at /home/dinosaur/Downloads/mysql-5.7.21/mysys/my_error.c:194
#1  0x0000000000f93e75 in Create_field::init (this=0x7fb9b8006740, thd=0x7fb9b8000b70, fld_name=0x7fb9b8006730 "name", fld_type=MYSQL_TYPE_STRING, fld_length=0x7fb9b8006738 "256", fld_decimals=0x0, fld_type_modifier=0, 
    fld_default_value=0x0, fld_on_update_value=0x0, fld_comment=0x7fb9b8002fe0, fld_change=0x0, fld_interval_list=0x7fb9b8003150, fld_charset=0x0, fld_geom_type=0, fld_gcol_info=0x0)
    at /home/dinosaur/Downloads/mysql-5.7.21/sql/field.cc:10962
#2  0x000000000163ae21 in add_field_to_list (thd=0x7fb9b8000b70, field_name=0x7fba3d30c460, type=MYSQL_TYPE_STRING, length=0x7fb9b8006738 "256", decimals=0x0, type_modifier=0, default_value=0x0, on_update_value=0x0, 
    comment=0x7fb9b8002fe0, change=0x0, interval_list=0x7fb9b8003150, cs=0x0, uint_geom_type=0, gcol_info=0x0) at /home/dinosaur/Downloads/mysql-5.7.21/sql/sql_parse.cc:5798
#3  0x000000000178e3f6 in MYSQLparse (YYTHD=0x7fb9b8000b70) at /home/dinosaur/Downloads/mysql-5.7.21/sql/sql_yacc.yy:6337
#4  0x000000000163d75a in parse_sql (thd=0x7fb9b8000b70, parser_state=0x7fba3d30d550, creation_ctx=0x0) at /home/dinosaur/Downloads/mysql-5.7.21/sql/sql_parse.cc:7131
#5  0x0000000001639f07 in mysql_parse (thd=0x7fb9b8000b70, parser_state=0x7fba3d30d550) at /home/dinosaur/Downloads/mysql-5.7.21/sql/sql_parse.cc:5469
#6  0x000000000162f0a3 in dispatch_command (thd=0x7fb9b8000b70, com_data=0x7fba3d30de00, command=COM_QUERY) at /home/dinosaur/Downloads/mysql-5.7.21/sql/sql_parse.cc:1458
#7  0x000000000162df32 in do_command (thd=0x7fb9b8000b70) at /home/dinosaur/Downloads/mysql-5.7.21/sql/sql_parse.cc:999
#8  0x0000000001770f97 in handle_connection (arg=0x570d510) at /home/dinosaur/Downloads/mysql-5.7.21/sql/conn_handler/connection_handler_per_thread.cc:300
#9  0x0000000001de0b41 in pfs_spawn_thread (arg=0x5749fc0) at /home/dinosaur/Downloads/mysql-5.7.21/storage/perfschema/pfs.cc:2190
#10 0x00007fba478aa6ba in start_thread (arg=0x7fba3d30e700) at pthread_create.c:333
#11 0x00007fba46d3341d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:109

varchar最大长度​

和char类似，想创建一个65532字符的varchar类型字段

CREATE TABLE `test123` ( `name` varchar(65533)) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;

结果也是一样的错误

ERROR 1074 (42000): Column length too big for column 'name' (max = 16383); use BLOB or TEXT instead

(gdb) bt
#0  my_error (nr=1074, MyFlags=0) at /home/dinosaur/Downloads/mysql-5.7.21/mysys/my_error.c:194
#1  0x00000000016c9998 in prepare_blob_field (thd=0x7fb9b8000b70, sql_field=0x7fb9b8006840) at /home/dinosaur/Downloads/mysql-5.7.21/sql/sql_table.cc:4715
#2  0x00000000016c6a33 in mysql_prepare_create_table (thd=0x7fb9b8000b70, error_schema_name=0x7fb9b8006728 "test", error_table_name=0x7fb9b8006168 "test123", create_info=0x7fba3d30c6b0, alter_info=0x7fba3d30c600, 
    tmp_table=false, db_options=0x7fba3d30b080, file=0x7fb9b8006ac0, key_info_buffer=0x7fba3d30c170, key_count=0x7fba3d30c16c, select_field_count=0) at /home/dinosaur/Downloads/mysql-5.7.21/sql/sql_table.cc:3721
#3  0x00000000016cac22 in create_table_impl (thd=0x7fb9b8000b70, db=0x7fb9b8006728 "test", table_name=0x7fb9b8006168 "test123", error_table_name=0x7fb9b8006168 "test123", path=0x7fba3d30c180 "./test/test123", 
    create_info=0x7fba3d30c6b0, alter_info=0x7fba3d30c600, internal_tmp_table=false, select_field_count=0, no_ha_table=false, is_trans=0x7fba3d30c3da, key_info=0x7fba3d30c170, key_count=0x7fba3d30c16c)
    at /home/dinosaur/Downloads/mysql-5.7.21/sql/sql_table.cc:5131
#4  0x00000000016cb884 in mysql_create_table_no_lock (thd=0x7fb9b8000b70, db=0x7fb9b8006728 "test", table_name=0x7fb9b8006168 "test123", create_info=0x7fba3d30c6b0, alter_info=0x7fba3d30c600, select_field_count=0, 
    is_trans=0x7fba3d30c3da) at /home/dinosaur/Downloads/mysql-5.7.21/sql/sql_table.cc:5417
#5  0x00000000016cb9a2 in mysql_create_table (thd=0x7fb9b8000b70, create_table=0x7fb9b80061a0, create_info=0x7fba3d30c6b0, alter_info=0x7fba3d30c600) at /home/dinosaur/Downloads/mysql-5.7.21/sql/sql_table.cc:5463
#6  0x00000000016335be in mysql_execute_command (thd=0x7fb9b8000b70, first_level=true) at /home/dinosaur/Downloads/mysql-5.7.21/sql/sql_parse.cc:3248
#7  0x000000000163a31c in mysql_parse (thd=0x7fb9b8000b70, parser_state=0x7fba3d30d550) at /home/dinosaur/Downloads/mysql-5.7.21/sql/sql_parse.cc:5582
#8  0x000000000162f0a3 in dispatch_command (thd=0x7fb9b8000b70, com_data=0x7fba3d30de00, command=COM_QUERY) at /home/dinosaur/Downloads/mysql-5.7.21/sql/sql_parse.cc:1458
#9  0x000000000162df32 in do_command (thd=0x7fb9b8000b70) at /home/dinosaur/Downloads/mysql-5.7.21/sql/sql_parse.cc:999
#10 0x0000000001770f97 in handle_connection (arg=0x570d510) at /home/dinosaur/Downloads/mysql-5.7.21/sql/conn_handler/connection_handler_per_thread.cc:300
#11 0x0000000001de0b41 in pfs_spawn_thread (arg=0x5749fc0) at /home/dinosaur/Downloads/mysql-5.7.21/storage/perfschema/pfs.cc:2190
#12 0x00007fba478aa6ba in start_thread (arg=0x7fba3d30e700) at pthread_create.c:333
#13 0x00007fba46d3341d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:109

(gdb) p sql_field->length 
$2 = 262132
static bool prepare_blob_field(THD *thd, Create_field *sql_field)
{
  DBUG_ENTER("prepare_blob_field");

  if (sql_field->length > MAX_FIELD_VARCHARLENGTH &&      // sql_field->length = 262132
      !(sql_field->flags & BLOB_FLAG))
  {
    /* Convert long VARCHAR columns to TEXT or BLOB */
    char warn_buff[MYSQL_ERRMSG_SIZE];

    if (sql_field->def || thd->is_strict_mode())      // 严格模式下会打印errorERROR 1074 (42000): Column length too big for 
    {                                                   // column 'name' (max = 16383); use BLOB or TEXT instead
      my_error(ER_TOO_BIG_FIELDLENGTH, MYF(0), sql_field->field_name,
               static_cast<ulong>(MAX_FIELD_VARCHARLENGTH /            // MAX_FIELD_VARCHARLENGTH = 65535
                                  sql_field->charset->mbmaxlen));     // sql_field->charset->mbmaxlen = 4
      DBUG_RETURN(1);
    }
    ...
  }

也就是严格模式下，varchar 最大是65535字节的内容，改成varchar(16383)看看

mysql> CREATE TABLE `test123` ( `name` varchar(16383)) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.26 sec)

ok,没有问题