mysql Row size too large.

最新推荐文章于 2025-03-10 11:23:46 发布

CarloPan

最新推荐文章于 2025-03-10 11:23:46 发布

阅读量754

点赞数 1

文章标签： mysql

本文链接：https://blog.csdn.net/CarloPan/article/details/112669382

版权

本文探讨了在MySQL中遇到的Row size too large错误，原因在于表类型限制了最大行长度。通过实例说明如何将过长的VARCHAR字段转换为TEXT或BLOB，以确保数据存储。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

SQL 错误 [1118] [42000]: Row size too large.

The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs

org.jkiss.dbeaver.model.sql.DBSQLException: SQL 错误 [1118] [42000]: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs
   at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:134)
   at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeStatement(SQLQueryJob.java:488)
   at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.lambda$0(SQLQueryJob.java:425)
   at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:170)
   at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeSingleQuery(SQLQueryJob.java:417)
   at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.extractData(SQLQueryJob.java:775)
   at org.jkiss.dbeaver.ui.editors.sql.SQLEditor$QueryResultsContainer.readData(SQLEditor.java:2914)
   at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.lambda$0(ResultSetJobDataRead.java:111)
   at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:170)
   at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.run(ResultSetJobDataRead.java:109)
   at org.jkiss.dbeaver.ui.controls.resultset.ResultSetViewer$17.run(ResultSetViewer.java:3423)
   at org.jkiss.dbeaver.model.runtime.AbstractJob.run(AbstractJob.java:103)
   at org.eclipse.core.internal.jobs.Worker.run(Worker.java:63)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs
   at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
   at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
   at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
   at java.base/java.lang.reflect.Constructor.newInstance(Unknown Source)
   at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
   at com.mysql.jdbc.Util.getInstance(Util.java:408)
   at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:943)
   at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973)
   at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909)
   at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2527)
   at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680)
   at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2483)
   at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2441)
   at com.mysql.jdbc.StatementImpl.executeInternal(StatementImpl.java:845)
   at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:745)
   at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.execute(JDBCStatementImpl.java:338)
   at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:131)
   ... 12 more

-- type, not counting BLOBs, is 65535
8192 * 3 = 24576
CREATE TABLE `test_65535` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
`defitnition1` varchar(8192) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '定义',
-- `defitnition2` varchar(8192) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '定义',
-- `defitnition3` varchar(8192) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '定义',
-- `defitnition4` varchar(8192) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '定义',
-- `defitnition5` varchar(8192) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '定义',
-- `defitnition6` varchar(8192) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '定义',
-- `defitnition7` varchar(8192) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '定义',
-- `defitnition8` varchar(8192) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '定义',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='test_65535';

ALTER TABLE `test_65535` ADD `defitnition9` varchar(8192) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '定义'; -- 添加失败

ALTER TABLE `test_65535` ADD `defitnition9` varchar(2048) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '定义'; -- 添加成功