MySQL SQL 错误 1406 列的数据太长
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8878779/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Error 1406 Data too long for column
提问by Frank Martin
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?
我试图在 MySQL 中执行下面的查询，但每次都会收到 SQL 错误 1406 Data too long for column error。列数据类型是长文本。有任何想法吗？
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étails</a></li><li>Panavision Prague - <a href="#" id="offices_linkPanavisionPrague">Détails</a></li><li>Panavision Manchester - <a href="#" id="offices_linkPanavisionManchester">Détails</a></li><li>Panavision Alga & Cinecam - <a href="#" id="offices_linkPanavisionAlga">Détails</a></li><li>Panavision Rhône-Alpes - <a href="#" id="offices_linkPanavisionRhoneAlpes">Détails</a></li><li>Panavision Marseille - <a href="#" id="offices_linkPanavisionMarseille">Détails</a></li><li>Panavision Pologne - <a href="#" id="offices_linkPanavisionPoland">Détails</a></li><li>Panavision Dublin - <a href="#" id="offices_linkPanavisionDublin">Détails</a></li><li>Panavision Belgique - <a href="#" id="offices_linkPanavisionBelgium">Détails</a></li></ul></div><!-- #offices_ownedOfficesContactDetails --></div><!-- #offices_mapControlPanel --><div class="cf"></div>' WHERE `my_table`.`id` = 27;
Thanks, here's the result from SHOW CREATE TABLE
谢谢，这是 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;
[SOLVED] Thanks for your help everyone, I deleted the HTML comments and it worked.
[已解决] 感谢大家的帮助，我删除了 HTML 评论并且它起作用了。
回答by álvaro González
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:
除了明显可能出现不同列的错误（您的错误消息似乎没有提到确切的列，这很奇怪），以下是手册对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.
最大长度为 4,294,967,295 或 4GB (232 – 1) 个字符的 TEXT 列。如果该值包含多字节字符，则有效最大长度较小。LONGTEXT 列的有效最大长度还取决于客户端/服务器协议中配置的最大数据包大小和可用内存。每个 LONGTEXT 值都使用一个四字节长度的前缀进行存储，该前缀指示值中的字节数。
You can verify maximum packet size with this query:
您可以使用此查询验证最大数据包大小：
SELECT @@max_allowed_packet
If it happens to be a small value, see this related answerfor further info.
如果它恰好是一个小值，请参阅此相关答案以获取更多信息。
回答by AnthonyBlake
There must be an issue with your column datatype.
您的列数据类型一定有问题。
The string you are inserting (via update) is 1,875 which is nowhere near the limit of longtext.
您插入的字符串（通过更新）是 1,875，远不及长文本的限制。
Take a look at this "pour l''Europe et l''Afrique" in your insert string. It may be causing you issues, try to insert the data without the "'" character.
看看插入字符串中的“pour l''Europe et l''Afrique”。它可能会导致您出现问题，请尝试插入没有“'”字符的数据。
could you try to insert a string of 1,875 characters, perhaps "aaaaaaa....". this would rule out a problem with the contents of the string you are inserting.
你能不能尝试插入一个 1,875 个字符的字符串，也许是“aaaaaaa....”。这将排除您插入的字符串内容有问题。
回答by Dau
I think you make the content filed as the varchar type. but for store the long text you have make it the texttype. this type not required any length
我认为您将内容归档为 varchar 类型。但是为了存储长文本，您将其text设为类型。这种类型不需要任何长度
