Java MySQL 数据截断错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/73109/
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
MySQL Data Truncation Error
提问by
I'm working with a fairly simple database, from a Java application. We're trying to insert about 200k of text at a time, using the standard JDBC mysql adapter. We intermittently get a com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for column error.
我正在使用来自 Java 应用程序的相当简单的数据库。我们尝试使用标准的 JDBC mysql 适配器一次插入大约 200k 的文本。我们间歇性地得到一个 com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for column error。
The column type is longtext, and database collation is UTF-8. The error shows up using both MyISAM and InnoDB table engines. Max packet size has been set ot 1 GB on both client and server sides, so that shouldn't be causing a problem, either.
列类型为 longtext，数据库排序规则为 UTF-8。使用 MyISAM 和 InnoDB 表引擎时会出现该错误。客户端和服务器端的最大数据包大小都设置为 1 GB，因此这也不应该导致问题。
回答by Nicholas
Well you can make it ignore the error by doing an INSERT IGNORE which will just truncate the data and insert it anyway. (from http://dev.mysql.com/doc/refman/5.0/en/insert.html)
好吧，您可以通过执行 INSERT IGNORE 使其忽略错误，这只会截断数据并无论如何插入。（来自http://dev.mysql.com/doc/refman/5.0/en/insert.html）
If you use the IGNORE keyword, errors that occur while executing the INSERT statement are treated as warnings instead. For example, without IGNORE, a row that duplicates an existing UNIQUE index or PRIMARY KEY value in the table causes a duplicate-key error and the statement is aborted. With IGNORE, the row still is not inserted, but no error is issued. Data conversions that would trigger errors abort the statement if IGNORE is not specified. With IGNORE, invalid values are adjusted to the closest values and inserted; warnings are produced but the statement does not abort.
如果使用 IGNORE 关键字，则在执行 INSERT 语句时发生的错误将被视为警告。例如，如果没有 IGNORE，复制表中现有 UNIQUE 索引或 PRIMARY KEY 值的行会导致重复键错误并且语句被中止。使用 IGNORE，仍然不会插入该行，但不会发出错误。如果未指定 IGNORE，则会触发错误的数据转换会中止语句。使用 IGNORE，将无效值调整为最接近的值并插入；产生警告但语句不会中止。
回答by Aaron
It sounds to me like you are trying to put too many bytes into a column. I ran across a very similar error with MySQL last night due to a bug in my code. I meant to do
在我看来，您试图将太多字节放入一列。由于我的代码中的错误，我昨晚在 MySQL 中遇到了一个非常相似的错误。我的意思是
foo.status = 'inactive'
but had actually typed
但实际上已经打字
foo.state = 'inactive'
Where foo.state is supposed to be a two character code for a US State ( varchar(2) ). I got the same error as you. You might go look for a similar situation in your code.
其中 foo.state 应该是美国州（ varchar(2) ）的两个字符代码。我和你有同样的错误。您可能会在代码中寻找类似的情况。
回答by Avi
Check that your UTF-8 data is all 3-byte Unicode. If you have 4-byte characters (legal in Unicode and Java, illegal in MySQL 5), it can throw this error when you try to insert them. This is an issue that should be fixedin MySQL 6.0.
检查您的 UTF-8 数据是否都是 3 字节的 Unicode。如果您有 4 字节字符（在 Unicode 和 Java 中合法，在 MySQL 5 中非法），当您尝试插入它们时，它可能会抛出此错误。这是一个应该在 MySQL 6.0 中修复的问题。
回答by Szemere
I just hit this problem and solved it by removing all the non-standard ascii characters in my text (following the UTF-8 advice above).
我刚刚遇到了这个问题，并通过删除文本中的所有非标准 ascii 字符来解决它（遵循上面的 UTF-8 建议）。
I had the problem on a Debian 4, Java 5 system; but the same code worked fine with Ubuntu 9.04, Java 6. Both run MySql 5.
我在 Debian 4、Java 5 系统上遇到了问题；但相同的代码在 Ubuntu 9.04、Java 6 上运行良好。两者都运行 MySql 5。
回答by grigson
In mysql you can use MEDIUMTEXTor LONGTEXTfield type for large text data
在mysql中，您可以使用MEDIUMTEXT或LONGTEXT字段类型来处理大文本数据
