MYSQL数据库更新text字段时出现Row size too large报错应付措施（mysql更新字段为null）

25-01-29 22

针对MYSQL数据库更新text字段时出现Rowsizetoolarge报错应付措施和mysql更新字段为null这两个问题，本篇文章进行了详细的解答，同时本文还将给你拓展1118-Rowsizeto

针对MYSQL数据库更新text字段时出现Row size too large报错应付措施和mysql更新字段为null这两个问题，本篇文章进行了详细的解答，同时本文还将给你拓展1118 - Row size too large (> 8126). Changing some columns to TEXT or BLOB ma....... 处理方法、ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.、Index column size too large. The maximum column size is 767 bytes、MSSQL数据库获取TEXT字段的内容长度等相关知识，希望可以帮助到你。

本文目录一览：

MYSQL数据库更新text字段时出现Row size too large报错应付措施（mysql更新字段为null）
1118 - Row size too large (> 8126). Changing some columns to TEXT or BLOB ma....... 处理方法
ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.
Index column size too large. The maximum column size is 767 bytes
MSSQL数据库获取TEXT字段的内容长度

MYSQL数据库更新text字段时出现Row size too large报错应付措施（mysql更新字段为null）

《MysqL数据库更新text字段时出现Row size too large报错应付措施》要点：
本文介绍了MysqL数据库更新text字段时出现Row size too large报错应付措施，希望对您有用。如果有疑问，可以联系我们。

起因：
团购开发申报说更新时出错.

更新sql如下：

代码如下:

UPDATE table_name d SET d.column_name='aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'
WHERE d.ID=100976;

报错信息如下：
Error Code : 1118
Row size too large. The maximum row size for the used table type,not counting BLOBs,is 8126. You have to change some columns to TEXT or BLOBs
疑惑：
更新字段只涉及 column_name字段,且该字段是TEXT类型.

个人之前理解是：
TEXT的内容在 Dynamic的table format下是存在off-page中的,不会占用row size的计算.
Barracuda 对应row_format ( dynamic,compress),其中dynamic下text的所有内容都是off-page存放的 (点击查看)
Antelope 对应row_format (compact,redundant),其中compact下的text是存786B在row中,超过部分存在off-page
而服务器配置是 innodb_file_format = Barracuda
照理说所有table用的都是 dynamic 结构.
但是! 原因如下,摘自文档：
To preserve compatibility with those prior versions,tables created with the InnoDB Plugin use the prefix format,unless one of ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMpressed is specified (or implied) on the CREATE TABLE command.
也便是说,建表时不显示指定 row_format = dynamic,即使 innodb_file_format = Barracuda 表的row-format还是 compact

所以总结为一句话便是：如果某个表的text字段很多建议建表时加上 row_format = dynamic
当然,回过头来MysqL的报错也是有误导性的,bug库中也对confirm了这个bug(点击查看),并在5.1.61中优化了报错提示.

小编PHP培训学院每天发布《MysqL数据库更新text字段时出现Row size too large报错应付措施》等实战技能，PHP、MysqL、LINUX、APP、JS,CSS全面培养人才。

1118 - Row size too large (> 8126). Changing some columns to TEXT or BLOB ma....... 处理方法

执行脚本设置临时解决方案：

set global innodb_file_per_table =ON;
SET GLOBAL innodb_file_format = barracuda;
set GLOBAL innodb_strict_mode = 0;
#SHOW GLOBAL VARIABLES LIKE ''%innodb_file%'';

也可以在mysql配置文件中设置，设置完成后，重启mysql服务。

ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.

MySQL 版本 5.6.35

在一个长度为 512 字符的字段上创建 unique key 报错

CREATE DATABASE dpcs_metadata DEFAULT CHARACTER SET utf8;
select * from information_schema.SCHEMATA;
+--------------+--------------------+----------------------------+------------------------+----------+
| CATALOG_NAME | SCHEMA_NAME        | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH |
+--------------+--------------------+----------------------------+------------------------+----------+
| def          | information_schema | utf8                       | utf8_general_ci        | NULL     |
| def          | dpcs_metadata      | utf8                       | utf8_general_ci        | NULL     |
| def          | mysql              | utf8                       | utf8_bin               | NULL     |
| def          | performance_schema | utf8                       | utf8_general_ci        | NULL     |
| def          | test               | utf8                       | utf8_bin               | NULL     |
+--------------+--------------------+----------------------------+------------------------+----------+
5 rows in set (0.00 sec)

use dpcs_metadata;

create table raw_log_meta_data(
       id bigint NOT NULL AUTO_INCREMENT,
       app_id varchar(64),
       user_id varchar(128),
       file_path varchar(512),
       device_id varchar(128),
       update_time DATETIME,
       PRIMARY KEY (id),
       UNIQUE KEY (user_id),
       UNIQUE KEY (file_path)
       ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

MySQL 用 1 到 2 个额外字节记录该字段的长度，当字段长度小于等于 255 时使用 1 个字节记录字段长度，当长度大于 255 时使用 2~4 个字节记录字段长度，字段 file_path 的长度为 512*3+2=1538 个字节，超过系统默认 767 字节数限制

select * from information_schema.character_sets where character_set_name in(''latin1'', ''utf8'', ''utf8mb4'');
+--------------------+----------------------+----------------------+--------+
| CHARACTER_SET_NAME | DEFAULT_COLLATE_NAME | DESCRIPTION          | MAXLEN |
+--------------------+----------------------+----------------------+--------+
| latin1             | latin1_swedish_ci    | cp1252 West European |      1 |
| utf8               | utf8_general_ci      | UTF-8 Unicode        |      3 |
| utf8mb4            | utf8mb4_general_ci   | UTF-8 Unicode        |      4 |
+--------------------+----------------------+----------------------+--------+
3 rows in set (0.00 sec)

根据上图所示，采用 utf8 编码的字段最大长度为 255 个字符时，255*3+1=756，是小于最大 767 限制的，可以创建成功，如下

create table raw_log_meta_data(
       id bigint NOT NULL AUTO_INCREMENT,
       app_id varchar(64),
       user_id varchar(128),
       file_path varchar(256),
       device_id varchar(128),
       update_time DATETIME,
       PRIMARY KEY (id),
       UNIQUE KEY (user_id),
       UNIQUE KEY (file_path)
       ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

create table raw_log_meta_data(
       id bigint NOT NULL AUTO_INCREMENT,
       app_id varchar(64),
       user_id varchar(128),
       file_path varchar(255),
       device_id varchar(128),
       update_time DATETIME,
       PRIMARY KEY (id),
       UNIQUE KEY (user_id),
       UNIQUE KEY (file_path)
       ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.19 sec)

desc raw_log_meta_data;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| app_id      | varchar(64)  | YES  |     | NULL    |                |
| user_id     | varchar(128) | YES  | UNI | NULL    |                |
| file_path   | varchar(255) | YES  | UNI | NULL    |                |
| device_id   | varchar(128) | YES  |     | NULL    |                |
| update_time | datetime     | YES  |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+
6 rows in set (0.01 sec)

根据文档所述

https://dev.mysql.com/doc/refman/5.6/en/create-index.html

Prefix support and lengths of prefixes (where supported) are storage engine dependent. For example, a prefix can be up to 767 bytes long for InnoDB tables or 3072 bytes if the innodb_large_prefix option is enabled. For MyISAM tables, the prefix length limit is 1000 bytes.

https://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_large_prefix

innodb_large_prefix

Property	Value
Command-Line Format	`--innodb-large-prefix[={OFF\|ON}]`
Introduced	5.6.3
System Variable	`innodb_large_prefix`
Scope	Global
Dynamic	Yes
Type	Boolean
Default Value	`OFF`

Enable this option to allow index key prefixes longer than 767 bytes (up to 3072 bytes) for InnoDB tables that use DYNAMIC or COMPRESSED row format. (Creating such tables also requires the option values innodb_file_format=barracuda and innodb_file_per_table=true.) See Section 14.6.1.6, “Limits on InnoDB Tables” for maximums associated with index key prefixes under various settings.

For tables that use REDUNDANT or COMPACT row format, this option does not affect the permitted index key prefix length.

https://dev.mysql.com/doc/refman/5.6/en/innodb-restrictions.html

By default, the index key prefix length limit is 767 bytes. See Section 13.1.13, “CREATE INDEX Syntax”. For example, you might hit this limit with a column prefix index of more than 255 characters on a TEXT or VARCHAR column, assuming a utf8mb3 character set and the maximum of 3 bytes for each character. When theinnodb_large_prefix configuration option is enabled, the index key prefix length limit is raised to 3072 bytes for InnoDB tables that use DYNAMIC or COMPRESSED row format.

根据以上可知，如果需要在长度大于 255 字符的字段上创建索引，需要修改以下 3 个参数

1. innodb_file_format=barracuda

2. innodb_file_per_table=true

3. ROW_FORMAT=DYNAMIC or COMPRESSED

mysql> show variables like ''innodb_large_prefix'';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| innodb_large_prefix | OFF   |
+---------------------+-------+
1 row in set (0.00 sec)

mysql> show variables like ''innodb_file_format'';
+--------------------+----------+
| Variable_name      | Value    |
+--------------------+----------+
| innodb_file_format | Antelope |
+--------------------+----------+
1 row in set (0.00 sec)

mysql> show variables like ''innodb_file_per_table'';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.00 sec)

mysql> set global innodb_large_prefix=''on'';
Query OK, 0 rows affected (0.00 sec)

mysql> set global innodb_file_format=''Barracuda'';
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like ''innodb_large_prefix'';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| innodb_large_prefix | ON    |
+---------------------+-------+
1 row in set (0.00 sec)

mysql> show variables like ''innodb_file_format'';
+--------------------+-----------+
| Variable_name      | Value     |
+--------------------+-----------+
| innodb_file_format | Barracuda |
+--------------------+-----------+
1 row in set (0.00 sec)

create table raw_log_meta_data(
       id bigint NOT NULL AUTO_INCREMENT,
       app_id varchar(64),
       user_id varchar(128),
       file_path varchar(512),
       device_id varchar(128),
       update_time DATETIME,
       PRIMARY KEY (id),
       UNIQUE KEY (user_id),
       UNIQUE KEY (file_path)
       ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
Query OK, 0 rows affected (0.29 sec)

Index column size too large. The maximum column size is 767 bytes

高春辉、王春生、朱峰：关于开源创业的 15 件小事

Index column size too large. The maximum column size is 767 bytes 博客分类：数据库

Index column size too large. The maximum column size is 767 bytes

5.6 及以上高版本 MySQL innodb 引擎创建表或者添加索引的时候报 Index column size too large. The maximum column size is 767 bytes

低版本可能是正常的

原因主要是高版本的数据库为了性能很多东西做了限制不合理的字段长度不合理的查询方式都会干掉

如果改配置的话配置改了之后字符集等也需要设置

而且对 innodb 性能应该有所损耗

可以把作为索引的那个 column 改小一点就可以了

确实需要大字段索引可以参考：https://help.aliyun.com/knowledge_detail/41707.html

MSSQL数据库获取TEXT字段的内容长度

SQL Server数据库如何获取TEXT字段的内容长度的方法，是通过DATALENGTH函数来实现的，接下来我们就通过DATALENGTH函数的语法及使用实例来了解一下这部分内容。

DATALENGTH用来返回任何表达式所占用的字节数。

语法：DATALENGTH ( expression )

参数：expression，任何类型的表达式。

返回类型：int

注释：DATALENGTH 对 varchar、varbinary、text、image、nvarchar 和 ntext
数据类型特别有用，因为这些数据类型可以存储可变长度数据。NULL 的DATALENGTH 的结果是 NULL

--------------------------------------------------------------------------------

返回字符表达式中的字符数。如果字符串中包含前导空格和尾随空格，则函数会将它们包含在计数内。LEN 对相同的单字节和双字节字符串返回相同的值。

LEN(character_expression)，字符串类型的表达式

关于MYSQL数据库更新text字段时出现Row size too large报错应付措施和mysql更新字段为null的问题我们已经讲解完毕，感谢您的阅读，如果还想了解更多关于1118 - Row size too large (> 8126). Changing some columns to TEXT or BLOB ma....... 处理方法、ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.、Index column size too large. The maximum column size is 767 bytes、MSSQL数据库获取TEXT字段的内容长度等相关内容，可以在本站寻找。

本文标签：