SQL错误1406数据对于列而言太长 [英] SQL Error 1406 Data too long for column

问题描述

我正在尝试在MySQL中执行以下查询，但每次都出现SQL错误1406数据对于列错误而言时间过长的情况.列数据类型为longtext.有什么想法吗?

I am trying to execute the query below in MySQL but get the SQL error 1406 Data too long for column error every time. The column data type is longtext. Any ideas?

UPDATE `my_db`.`my_table` SET `content` = '<div id="primaryContent"><div id="offices_map"></div><!-- #offices_map --><div id="offices_mapControlPanel" class="cf"><ul id="offices_continentLinkList"><li><a href="#" rel="Africa">AFRIQUE</a></li><li><a href="#" rel="Asia">ASIE</a></li><li><a href="#" rel="Australasia">AUSTRALASIE</a></li><li><a href="#" rel="Europe" id="offices_europeLink" class="current">EUROPE</a></li><li><a href="#" rel="NorthAmerica">AMERIQUE DU NORD</a></li><li class="last"><a href="#" rel="SouthAmerica">AMERIQUE DU SUD</a></li></ul><ul id="offices_mapLegend"><li id="offices_mapLegendRedPointer">Bureaux Panavision</li><li id="offices_mapLegendYellowPointer">Agents Panavision</li></ul></div><!-- #offices_mapLegend --><div id="offices_ownedOfficesContactDetails" class="cf"><h2>Bureaux Panavision</h2><ul class="cf"><li class="first"><strong>Panavision Greenford</strong> - pour l''Europe et l''Afrique - <a href="#" id="offices_linkPanavisionGreenford">D&#233;tails</a></li><li>Panavision Prague - <a href="#" id="offices_linkPanavisionPrague">D&#233;tails</a></li><li>Panavision Manchester - <a href="#" id="offices_linkPanavisionManchester">D&#233;tails</a></li><li>Panavision Alga &amp; Cinecam - <a href="#" id="offices_linkPanavisionAlga">D&#233;tails</a></li><li>Panavision Rh&ocirc;ne-Alpes - <a href="#" id="offices_linkPanavisionRhoneAlpes">D&#233;tails</a></li><li>Panavision Marseille - <a href="#" id="offices_linkPanavisionMarseille">D&#233;tails</a></li><li>Panavision Pologne - <a href="#" id="offices_linkPanavisionPoland">D&#233;tails</a></li><li>Panavision Dublin - <a href="#" id="offices_linkPanavisionDublin">D&#233;tails</a></li><li>Panavision Belgique - <a href="#" id="offices_linkPanavisionBelgium">D&#233;tails</a></li></ul></div><!-- #offices_ownedOfficesContactDetails --></div><!-- #offices_mapControlPanel --><div class="cf"></div>' WHERE `my_table`.`id` = 27;

谢谢，这是SHOW CREATE TABLE的结果

Thanks, here's the result from SHOW CREATE TABLE

   CREATE TABLE `my_table` (  
  `content` longtext NOT NULL,  
  `cat` text NOT NULL,  
  `starter` int(1) NOT NULL,  
  `at` int(11) DEFAULT '0',  
  `table` varchar(60) DEFAULT NULL,  
  `id` int(11) NOT NULL AUTO_INCREMENT,  
  PRIMARY KEY (`id`)  
) ENGINE=MyISAM AUTO_INCREMENT=84 DEFAULT CHARSET=latin1 |

只需尝试这个较短的查询，其中不包含特殊/转义的字符，但我仍然会收到错误消息.

Just tried this shorter query which contains no special/escaped chars but I still get the error.

UPDATE `my_db`.`my_table` SET `contenu` = '<div id="primaryContent"><div id="offices_map"></div><!-- #offices_map --><div id="offices_mapControlPanel" class="cf"><ul id="offices_continentLinkList"><li><a href="#" rel="Africa">AFRIQUE</a></li><li><a href="#" rel="Asia">ASIE</a></li><li><a href="#" rel="Australasia">AUSTRALASIE</a></li><li><a href="#" rel="Europe" id="offices_europeLink" class="current">EUROPE</a></li><li><a href="#" rel="NorthAmerica">AMERIQUE DU NORD</a></li><li class="last"><a href="#" rel="SouthAmerica">AMERIQUE DU SUD</a></li></ul><ul id="offices_mapLegend"><li id="offices_mapLegendRedPointer">Bureaux Panavision</li><li id="offices_mapLegendYellowPointer">Agents Panavision</li></ul></div><!-- #offices_mapLegend -->' WHERE `my_table`.`id` = 27;

[已解决] 感谢大家的帮助，我删除了HTML注释，它起作用了.

[SOLVED] Thanks for your help everyone, I deleted the HTML comments and it worked.

推荐答案

除了很明显有可能在其他列中出错(您的错误消息似乎没有提到确切的列，这很奇怪)之外，这是什么该手册必须说一下 LONGTEXT :

Apart from the obvious possibility of getting an error for a different column (your error message does not seem to mention the exact column, which is weird), here's what the manual has to say about LONGTEXT:

一个TEXT列，最大长度为4,294,967,295或4GB(232 – 1) 人物.如果最大长度小于 多字节字符. LONGTEXT的有效最大长度 列还取决于配置的最大数据包大小 客户端/服务器协议和可用内存.每个LONGTEXT值是 使用四字节长度的前缀存储，该前缀指示 值中的字节.

A TEXT column with a maximum length of 4,294,967,295 or 4GB (232 – 1) characters. The effective maximum length is less if the value contains multi-byte characters. The effective maximum length of LONGTEXT columns also depends on the configured maximum packet size in the client/server protocol and available memory. Each LONGTEXT value is stored using a four-byte length prefix that indicates the number of bytes in the value.

您可以通过此查询验证最大数据包大小:

You can verify maximum packet size with this query:

SELECT @@max_allowed_packet

如果碰巧是一个很小的值，请参见此

If it happens to be a small value, see this related answer for further info.

这篇关于SQL错误1406数据对于列而言太长的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！