MYSQL-ERROR 1074/1118 表定义长度限制

Error Code: 1074/1118

Error Code: 1074. Column length too big for column 'a' (max = 21845); use BLOB or TEXT instead

翻译

错误代码：1074。列“a”的列长度太大（最大值=21845）；请改用BLOB或TEXT

Error Code: 1118. 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

翻译

错误代码：1118。行大小太大。所用表类型（不包括BLOB）的最大行大小为65535。这包括存储开销，请参阅手册。您必须将某些列更改为TEXT或BLOB

Error Code: 1074. Column length too big for column 'a' (max = 255); use BLOB or TEXT instead

翻译

错误代码：1074。列“a”的列长度太大（最大值=255）；请改用BLOB或TEXT

Error Code: 1118. Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=Compact or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.

翻译

错误代码：1118。行大小太大（>8126）。将某些列更改为TEXT或BLOB，或使用ROW_FORMAT=Compact或ROW_FORMT=COMPRESED可能会有所帮助。在当前的行格式中，768字节的BLOB前缀是内联存储的。

1.背景

mysql版本

select @@version;

# @@version

'5.7.41-log'

select @@SQL_MODE;

# @@SQL_MODE

'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION'

2.举例说明

compact行格式：

变长字段长度列表|NULL值列表|记录头信息|列1的值|列2的值|...|列n的值|

1、变长字段长度列表

数据类型：VARCHAR、TEXT、BLOB等

COMPACT将变长列的实际长度按照字段的顺序，逆序存储在变长字段长度列表里。

2、NULL值列表

3、记录头信息由固定的 5 个字节

    行定义最大长度是65535(字节)，不包括 BLOB/TEXT类型  但是包括「变长字段长度列表」和 「NULL 值列表」

2.1VARCHAR(M)M长度限制

2.1.1例子

列VARCHAR(65535)相同，行格式Compact相同，字符集latin1和utf8\utf8mb4比较

-- 错误示例

drop table if exists tbl_latin1;

CREATE TABLE tbl_latin1 (

       c1 VARCHAR(65535) not null

) ENGINE = InnoDB CHARACTER SET latin1 ROW_FORMAT=Compact;

Error Code: 1118. 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

drop table if exists tbl_utf8;

CREATE TABLE tbl_utf8 (

       c1 VARCHAR(65535) not null

) ENGINE = InnoDB CHARACTER SET utf8 ROW_FORMAT=Compact;

Error Code: 1074. Column length too big for column 'c1' (max = 21845); use BLOB or TEXT instead

drop table if exists tbl_utf8mb4;

CREATE TABLE tbl_utf8mb4 (

       c1 VARCHAR(65535) not null

) ENGINE = InnoDB CHARACTER SET utf8mb4 ROW_FORMAT=Compact;

Error Code: 1074. Column length too big for column 'c1' (max = 16383); use BLOB or TEXT instead

--正确示例

drop table if exists tbl_latin1;

CREATE TABLE tbl_latin1 (

       c1 VARCHAR(65533) not null

) ENGINE = InnoDB CHARACTER SET latin1 ROW_FORMAT=Compact;

0 row(s) a