MYSQL单行长度不能超过 65535

报错:Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. 

mysql属于关系型,行式数据库, 规定单行长度不能超过65535一点都不过分。 你想当你超过65535后, 查询的iops怎么撑得住。


当我抓取日志入库的时候, 发现定义列太短了, 于是把varchar(50) 改为 varchar(150) .

 但是建表出错了。



MySQL要求一个行的定义长度不能超过65535。

(1)单个字段如果大于65535,则转换为TEXT 。

(2)单行最大限制为65535,这里不包括TEXT、BLOB。

所谓单行最大限制指的就是一张表中所有字段的所设置的长度不得超过65535字节,

例如一个表中有三个varchar字段长度30000,那么这个表的单行长度为:30000*3=90000,

大于65535则报错不能建表,这里乘以3是因为数据库用的utf8编码,3个字节表示一个字符。


报错sql

create table mw_logs
(
   MW_Name              VARCHAR(500),
   MW_Host              VARCHAR(500),
   LId                  Long,
   Log_Time             DATETIME,
   Req_QueryId          VARCHAR(150),
   UserId               VARCHAR(50),
   Web_ModuleId         VARCHAR(50),
   Web_Module_Name      VARCHAR(50),
   Log_Line             VARCHAR(500),
   Log_Level            VARCHAR(50),
   Log_Text             TEXT,
   Log_Error            VARCHAR(20000),
   Log_Stack            TEXT
)
ENGINE = InnoDB
row_format = compressed
PARTITION BY RANGE (TO_DAYS(Log_Time) ) (
    PARTITION p20180201 VALUES LESS THAN (TO_DAYS('20180201')),
    PARTITION p20180301 VALUES LESS THAN (TO_DAYS('20180301')),
    PARTITION p20180401 VALUES LESS THAN (TO_DAYS('20180401')),
    PARTITION p20180501 VALUES LESS THAN (TO_DAYS('20180501')),
    PARTITION p20180601 VALUES LESS THAN (TO_DAYS('20180601')),
    PARTITION p20180701 VALUES LESS THAN (TO_DAYS('20180701')),
    PARTITION p20180801 VALUES LESS THAN (TO_DAYS('20180801')),
    PARTITION p20180901 VALUES LESS THAN (TO_DAYS('20180901')),
    PARTITION p20181001 VALUES LESS THAN (TO_DAYS('20181001')),
    PARTITION p20181101 VALUES LESS THAN (TO_DAYS('20181101')),
    PARTITION p20181201 VALUES LESS THAN (TO_DAYS('20181201')),
    PARTITION p20190101 VALUES LESS THAN (TO_DAYS('20190101')),
    PARTITION p20190201 VALUES LESS THAN (TO_DAYS('20190201')),
    PARTITION p20190301 VALUES LESS THAN (TO_DAYS('20190301')),
    PARTITION p20190401 VALUES LESS THAN (TO_DAYS('20190401')),
    PARTITION p20190501 VALUES LESS THAN (TO_DAYS('20190501')),
    PARTITION p20190601 VALUES LESS THAN (TO_DAYS('20190601')),
    PARTITION p20190701 VALUES LESS THAN (TO_DAYS('20190701')),
    PARTITION p20190801 VALUES LESS THAN (TO_DAYS('20190801')),
    PARTITION p20190901 VALUES LESS THAN (TO_DAYS('20190901')),
    PARTITION p20191001 VALUES LESS THAN (TO_DAYS('20191001')),
    PARTITION p20191101 VALUES LESS THAN (TO_DAYS('20191101')),
    PARTITION p20191201 VALUES LESS THAN (TO_DAYS('20191201')),
    PARTITION p20200101 VALUES LESS THAN (TO_DAYS('20200101')),
    PARTITION pMAXVALUE VALUES LESS THAN(MAXVALUE)
);


修改为

create table mw_logs
(
   MW_Name              VARCHAR(500),
   MW_Host              VARCHAR(500),
   LId                  Long,
   Log_Time             DATETIME,
   Req_QueryId          VARCHAR(150),
   UserId               VARCHAR(50),
   Web_ModuleId         VARCHAR(50),
   Web_Module_Name      VARCHAR(50),
   Log_Line             VARCHAR(500),
   Log_Level            VARCHAR(50),
   Log_Text             TEXT,
   Log_Error            VARCHAR(10000),
   Log_Stack            TEXT
)
ENGINE = InnoDB
row_format = compressed
PARTITION BY RANGE (TO_DAYS(Log_Time) ) (
    PARTITION p20180201 VALUES LESS THAN (TO_DAYS('20180201')),
    PARTITION p20180301 VALUES LESS THAN (TO_DAYS('20180301')),
    PARTITION p20180401 VALUES LESS THAN (TO_DAYS('20180401')),
    PARTITION p20180501 VALUES LESS THAN (TO_DAYS('20180501')),
    PARTITION p20180601 VALUES LESS THAN (TO_DAYS('20180601')),
    PARTITION p20180701 VALUES LESS THAN (TO_DAYS('20180701')),
    PARTITION p20180801 VALUES LESS THAN (TO_DAYS('20180801')),
    PARTITION p20180901 VALUES LESS THAN (TO_DAYS('20180901')),
    PARTITION p20181001 VALUES LESS THAN (TO_DAYS('20181001')),
    PARTITION p20181101 VALUES LESS THAN (TO_DAYS('20181101')),
    PARTITION p20181201 VALUES LESS THAN (TO_DAYS('20181201')),
    PARTITION p20190101 VALUES LESS THAN (TO_DAYS('20190101')),
    PARTITION p20190201 VALUES LESS THAN (TO_DAYS('20190201')),
    PARTITION p20190301 VALUES LESS THAN (TO_DAYS('20190301')),
    PARTITION p20190401 VALUES LESS THAN (TO_DAYS('20190401')),
    PARTITION p20190501 VALUES LESS THAN (TO_DAYS('20190501')),
    PARTITION p20190601 VALUES LESS THAN (TO_DAYS('20190601')),
    PARTITION p20190701 VALUES LESS THAN (TO_DAYS('20190701')),
    PARTITION p20190801 VALUES LESS THAN (TO_DAYS('20190801')),
    PARTITION p20190901 VALUES LESS THAN (TO_DAYS('20190901')),
    PARTITION p20191001 VALUES LESS THAN (TO_DAYS('20191001')),
    PARTITION p20191101 VALUES LESS THAN (TO_DAYS('20191101')),
    PARTITION p20191201 VALUES LESS THAN (TO_DAYS('20191201')),
    PARTITION p20200101 VALUES LESS THAN (TO_DAYS('20200101')),
    PARTITION pMAXVALUE VALUES LESS THAN(MAXVALUE)
);

  • 3
    点赞
  • 12
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值