mysql 8.0.13行大小太大(>8126)

6fe3ivhb  于 2021-06-15  发布在  Mysql
关注(0)|答案(2)|浏览(353)

我已经阅读了关于这个错误的所有其他问题。但是我找不到正确的解决方案来修复mysql server 8.0.13的错误。请帮忙。
下表的第255列是mediumtext

CREATE TABLE `guidatatable` (
   `id` INT NOT NULL AUTO_INCREMENT,
   `col01` MEDIUMTEXT NULL,
   `col02` MEDIUMTEXT NULL,
   `col03` MEDIUMTEXT NULL,
   `col04` MEDIUMTEXT NULL,
   `col05` MEDIUMTEXT NULL,
   `col06` MEDIUMTEXT NULL,
   `col07` MEDIUMTEXT NULL,
   `col08` MEDIUMTEXT NULL,
   `col09` MEDIUMTEXT NULL,
   `col10` MEDIUMTEXT NULL,
   `col11` MEDIUMTEXT NULL,
   `col12` MEDIUMTEXT NULL,
   `col13` MEDIUMTEXT NULL,
   `col14` MEDIUMTEXT NULL,
   `col15` MEDIUMTEXT NULL,
   `col16` MEDIUMTEXT NULL,
   `col17` MEDIUMTEXT NULL,
   `col18` MEDIUMTEXT NULL,
   `col19` MEDIUMTEXT NULL,
   `col20` MEDIUMTEXT NULL,
   `col21` MEDIUMTEXT NULL,
   `col22` MEDIUMTEXT NULL,
   `col23` MEDIUMTEXT NULL,
   `col24` MEDIUMTEXT NULL,
   `col25` MEDIUMTEXT NULL,
   `col26` MEDIUMTEXT NULL,
   `col27` MEDIUMTEXT NULL,
   `col28` MEDIUMTEXT NULL,
   `col29` MEDIUMTEXT NULL,
   `col30` MEDIUMTEXT NULL,
   `col31` MEDIUMTEXT NULL,
   `col32` MEDIUMTEXT NULL,
   `col33` MEDIUMTEXT NULL,
   `col34` MEDIUMTEXT NULL,
   `col35` MEDIUMTEXT NULL,
   `col36` MEDIUMTEXT NULL,
   `col37` MEDIUMTEXT NULL,
   `col38` MEDIUMTEXT NULL,
   `col39` MEDIUMTEXT NULL,
   `col40` MEDIUMTEXT NULL,
   `col41` MEDIUMTEXT NULL,
   `col42` MEDIUMTEXT NULL,
   `col43` MEDIUMTEXT NULL,
   `col44` MEDIUMTEXT NULL,
   `col45` MEDIUMTEXT NULL,
   `col46` MEDIUMTEXT NULL,
   `col47` MEDIUMTEXT NULL,
   `col48` MEDIUMTEXT NULL,
   `col49` MEDIUMTEXT NULL,
   `col50` MEDIUMTEXT NULL,
   `col51` MEDIUMTEXT NULL,
   `col52` MEDIUMTEXT NULL,
   `col53` MEDIUMTEXT NULL,
   `col54` MEDIUMTEXT NULL,
   `col55` MEDIUMTEXT NULL,
   `col56` MEDIUMTEXT NULL,
   `col57` MEDIUMTEXT NULL,
   `col58` MEDIUMTEXT NULL,
   `col59` MEDIUMTEXT NULL,
   `col60` MEDIUMTEXT NULL,
   `col61` MEDIUMTEXT NULL,
   `col62` MEDIUMTEXT NULL,
   `col63` MEDIUMTEXT NULL,
   `col64` MEDIUMTEXT NULL,
   `col65` MEDIUMTEXT NULL,
   `col66` MEDIUMTEXT NULL,
   `col67` MEDIUMTEXT NULL,
   `col68` MEDIUMTEXT NULL,
   `col69` MEDIUMTEXT NULL,
   `col70` MEDIUMTEXT NULL,
   `col71` MEDIUMTEXT NULL,
   `col72` MEDIUMTEXT NULL,
   `col73` MEDIUMTEXT NULL,
   `col74` MEDIUMTEXT NULL,
   `col75` MEDIUMTEXT NULL,
   `col76` MEDIUMTEXT NULL,
   `col77` MEDIUMTEXT NULL,
   `col78` MEDIUMTEXT NULL,
   `col79` MEDIUMTEXT NULL,
   `col80` MEDIUMTEXT NULL,
   `col81` MEDIUMTEXT NULL,
   `col82` MEDIUMTEXT NULL,
   `col83` MEDIUMTEXT NULL,
   `col84` MEDIUMTEXT NULL,
   `col85` MEDIUMTEXT NULL,
   `col86` MEDIUMTEXT NULL,
   `col87` MEDIUMTEXT NULL,
   `col88` MEDIUMTEXT NULL,
   `col89` MEDIUMTEXT NULL,
   `col90` MEDIUMTEXT NULL,
   `col91` MEDIUMTEXT NULL,
   `col92` MEDIUMTEXT NULL,
   `col93` MEDIUMTEXT NULL,
   `col94` MEDIUMTEXT NULL,
   `col95` MEDIUMTEXT NULL,
   `col96` MEDIUMTEXT NULL,
   `col97` MEDIUMTEXT NULL,
   `col98` MEDIUMTEXT NULL,
   `col99` MEDIUMTEXT NULL,
   `col100` MEDIUMTEXT NULL,
   `col101` MEDIUMTEXT NULL,
   `col102` MEDIUMTEXT NULL,
   `col103` MEDIUMTEXT NULL,
   `col104` MEDIUMTEXT NULL,
   `col105` MEDIUMTEXT NULL,
   `col106` MEDIUMTEXT NULL,
   `col107` MEDIUMTEXT NULL,
   `col108` MEDIUMTEXT NULL,
   `col109` MEDIUMTEXT NULL,
   `col110` MEDIUMTEXT NULL,
   `col111` MEDIUMTEXT NULL,
   `col112` MEDIUMTEXT NULL,
   `col113` MEDIUMTEXT NULL,
   `col114` MEDIUMTEXT NULL,
   `col115` MEDIUMTEXT NULL,
   `col116` MEDIUMTEXT NULL,
   `col117` MEDIUMTEXT NULL,
   `col118` MEDIUMTEXT NULL,
   `col119` MEDIUMTEXT NULL,
   `col120` MEDIUMTEXT NULL,
   `col121` MEDIUMTEXT NULL,
   `col122` MEDIUMTEXT NULL,
   `col123` MEDIUMTEXT NULL,
   `col124` MEDIUMTEXT NULL,
   `col125` MEDIUMTEXT NULL,
   `col126` MEDIUMTEXT NULL,
   `col127` MEDIUMTEXT NULL,
   `col128` MEDIUMTEXT NULL,
   `col129` MEDIUMTEXT NULL,
   `col130` MEDIUMTEXT NULL,
   `col131` MEDIUMTEXT NULL,
   `col132` MEDIUMTEXT NULL,
   `col133` MEDIUMTEXT NULL,
   `col134` MEDIUMTEXT NULL,
   `col135` MEDIUMTEXT NULL,
   `col136` MEDIUMTEXT NULL,
   `col137` MEDIUMTEXT NULL,
   `col138` MEDIUMTEXT NULL,
   `col139` MEDIUMTEXT NULL,
   `col140` MEDIUMTEXT NULL,
   `col141` MEDIUMTEXT NULL,
   `col142` MEDIUMTEXT NULL,
   `col143` MEDIUMTEXT NULL,
   `col144` MEDIUMTEXT NULL,
   `col145` MEDIUMTEXT NULL,
   `col146` MEDIUMTEXT NULL,
   `col147` MEDIUMTEXT NULL,
   `col148` MEDIUMTEXT NULL,
   `col149` MEDIUMTEXT NULL,
   `col150` MEDIUMTEXT NULL,
 `col151` MEDIUMTEXT NULL,
 `col152` MEDIUMTEXT NULL,
 `col153` MEDIUMTEXT NULL,
 `col154` MEDIUMTEXT NULL,
 `col155` MEDIUMTEXT NULL,
 `col156` MEDIUMTEXT NULL,
 `col157` MEDIUMTEXT NULL,
 `col158` MEDIUMTEXT NULL,
 `col159` MEDIUMTEXT NULL,
 `col160` MEDIUMTEXT NULL,
 `col161` MEDIUMTEXT NULL,
 `col162` MEDIUMTEXT NULL,
 `col163` MEDIUMTEXT NULL,
 `col164` MEDIUMTEXT NULL,
 `col165` MEDIUMTEXT NULL,
 `col166` MEDIUMTEXT NULL,
 `col167` MEDIUMTEXT NULL,
 `col168` MEDIUMTEXT NULL,
 `col169` MEDIUMTEXT NULL,
 `col170` MEDIUMTEXT NULL,
 `col171` MEDIUMTEXT NULL,
 `col172` MEDIUMTEXT NULL,
 `col173` MEDIUMTEXT NULL,
 `col174` MEDIUMTEXT NULL,
 `col175` MEDIUMTEXT NULL,
 `col176` MEDIUMTEXT NULL,
 `col177` MEDIUMTEXT NULL,
 `col178` MEDIUMTEXT NULL,
 `col179` MEDIUMTEXT NULL,
 `col180` MEDIUMTEXT NULL,
 `col181` MEDIUMTEXT NULL,
 `col182` MEDIUMTEXT NULL,
 `col183` MEDIUMTEXT NULL,
 `col184` MEDIUMTEXT NULL,
 `col185` MEDIUMTEXT NULL,
 `col186` MEDIUMTEXT NULL,
 `col187` MEDIUMTEXT NULL,
 `col188` MEDIUMTEXT NULL,
 `col189` MEDIUMTEXT NULL,
 `col190` MEDIUMTEXT NULL,
 `col191` MEDIUMTEXT NULL,
 `col192` MEDIUMTEXT NULL,
 `col193` MEDIUMTEXT NULL,
 `col194` MEDIUMTEXT NULL,
 `col195` MEDIUMTEXT NULL,
 `col196` MEDIUMTEXT NULL,
 `col197` MEDIUMTEXT NULL,
 `col198` MEDIUMTEXT NULL,
 `col199` MEDIUMTEXT NULL,
 `col200` MEDIUMTEXT NULL,
 `col201` MEDIUMTEXT NULL,
 `col202` MEDIUMTEXT NULL,
 `col203` MEDIUMTEXT NULL,
 `col204` MEDIUMTEXT NULL,
 `col205` MEDIUMTEXT NULL,
 `col206` MEDIUMTEXT NULL,
 `col207` MEDIUMTEXT NULL,
 `col208` MEDIUMTEXT NULL,
 `col209` MEDIUMTEXT NULL,
 `col210` MEDIUMTEXT NULL,
 `col211` MEDIUMTEXT NULL,
 `col212` MEDIUMTEXT NULL,
 `col213` MEDIUMTEXT NULL,
 `col214` MEDIUMTEXT NULL,
 `col215` MEDIUMTEXT NULL,
 `col216` MEDIUMTEXT NULL,
 `col217` MEDIUMTEXT NULL,
 `col218` MEDIUMTEXT NULL,
 `col219` MEDIUMTEXT NULL,
 `col220` MEDIUMTEXT NULL,
 `col221` MEDIUMTEXT NULL,
 `col222` MEDIUMTEXT NULL,
 `col223` MEDIUMTEXT NULL,
 `col224` MEDIUMTEXT NULL,
 `col225` MEDIUMTEXT NULL,
 `col226` MEDIUMTEXT NULL,
 `col227` MEDIUMTEXT NULL,
 `col228` MEDIUMTEXT NULL,
 `col229` MEDIUMTEXT NULL,
 `col230` MEDIUMTEXT NULL,
 `col231` MEDIUMTEXT NULL,
 `col232` MEDIUMTEXT NULL,
 `col233` MEDIUMTEXT NULL,
 `col234` MEDIUMTEXT NULL,
 `col235` MEDIUMTEXT NULL,
 `col236` MEDIUMTEXT NULL,
 `col237` MEDIUMTEXT NULL,
 `col238` MEDIUMTEXT NULL,
 `col239` MEDIUMTEXT NULL,
 `col240` MEDIUMTEXT NULL,
 `col241` MEDIUMTEXT NULL,
 `col242` MEDIUMTEXT NULL,
 `col243` MEDIUMTEXT NULL,
 `col244` MEDIUMTEXT NULL,
 `col245` MEDIUMTEXT NULL,
 `col246` MEDIUMTEXT NULL,
 `col247` MEDIUMTEXT NULL,
 `col248` MEDIUMTEXT NULL,
 `col249` MEDIUMTEXT NULL,
 `col250` MEDIUMTEXT NULL,
 `col251` MEDIUMTEXT NULL,
 `col252` MEDIUMTEXT NULL,
 `col253` MEDIUMTEXT NULL,
 `col254` MEDIUMTEXT NULL,
 `col255` MEDIUMTEXT NULL,
 `status` INT NULL,
 PRIMARY KEY (`id`))ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

但这让我犯了个错误-

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

我也尝试过其他的解决方案，比如调整my.ini文件，比如增加innodb\u log\u文件大小到任何程度，比如2g、5g，但这些调整都没有成功。
另外，我不想仅仅关闭innodb\u strict\u模式，因为我不认为关闭验证是唯一的解决方案。mysql server 8.0.13版的任何其他解决方案。

mysqlinnodbmysql-8.0

2条答案

按热度按时间
daupos2t

daupos2t1#

你设计了一个愚蠢的table，已经达到了尺寸限制，对不起！当您有这样的“数组”方案时，您应该进行规范化—sql是为您设计的。
你得把table弄平。而不是 (id, val1, val2, val3, val4, ...) ，你想要什么 (id, 1, val), (id, 2, val), (id, 3, val), ... . 使用这个方案，您不会达到大小限制，并且您有一个更惯用的模式。
你还需要考虑到 status 字段，所以我们最终得到这样的结果：

CREATE TABLE `guidatatable` (
   `id`     INT NOT NULL AUTO_INCREMENT,
   `status` INT NULL,

   PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE TABLE `guidatatable_values` (
   `id`       INT NOT NULL,
   `colNum`   INT NOT NULL,
   `colValue` MEDIUMTEXT NOT NULL,

   PRIMARY KEY (`id`, `colNum`)
) ENGINE=InnoDB;

一定要确保 guidatatable_values.id 外键指的是 guidatatable.id （我一下子就忘了最好的语法）。
数据转换的具体示例；这是：

id  col01   col02   col03   status
 1  Hello   World   !         42
 2  How     Are     You       99
 3  I       Am      Fine      168

变成这样：

id   status
  1   42
  2   99
  3   168

 id   colNum   colValue
  1   1        Hello
  1   2        World
  1   3        !
  2   1        How
  2   2        Are
  2   3        You
  3   1        I
  3   2        Am
  3   3        Fine

如您所见，表中存储的所有语义和关系信息都保持不变；刚刚重组了。
一般来说，一旦达到表的最大大小，就知道表太大了，可能设计得更好。这在大型应用程序中尤其可能发生，在这些应用程序中，遗留需求导致表不断增长……同样，这通常是无法充分设计新特性的一个例子。

赞(0）分享  回复(0）举报  2021-06-16
a1o7rhls

a1o7rhls2#

mysql有一个最大行大小。
因此，最好的选择可能是将表数据规范化为以下内容。

CREATE TABLE guidatatable (
    id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT
  , col_row_position INT UNSIGNED NOT NULL 
  , col_text MEDIUMTEXT NULL
  , col_position INT UNSIGNED NOT NULL 
  , guidatatable_status VARCHAR(255)
  , KEY(col_row_position, col_position)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

这还处理mysql更好的最大数据包大小。

赞(0）分享  回复(0）举报  2021-06-16
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备2022004421号-2 NULL123 https://www.null123.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com