Java 数据截断：第 1 行“徽标”列的数据太长
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21522875/
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
Data truncation: Data too long for column 'logo' at row 1
提问by user3230425
I am trying to insert a photo into a BLOB column of a MySQL table, and I get an exception:
我正在尝试将照片插入 MySQL 表的 BLOB 列，但出现异常：
Data too long for column 'logo' at row 1.
Here is the JDBC:
这是JDBC：
int idRestaurant = 42;
String restoname= "test";
String restostatus= "test";
InputStream fileContent = getUploadedFile();
int fileSize = getUploadedFileSize();
Class.forName("com.mysql.jdbc.Driver");
try (Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/resto" , "root" , "" )) {
PreparedStatement ps = conn.prepareStatement("insert into restaurants (idRestaurant, restaurantName, status, logo) values(?,?,?,?)");
ps.setInt(1, idRestaurant);
ps.setString(2, restoname);
ps.setString(3, restostatus);
ps.setBinaryStream(4, fileContent, fileSize);
ps.executeUpdate();
conn.commit();
}
How do I solve this problem?
我该如何解决这个问题？
采纳答案by Aniket Kulkarni
You are trying to insert data that is larger than allowed for the column logo.
您正在尝试插入大于列所允许的数据logo。
Use following data types as per your need
根据需要使用以下数据类型
TINYBLOB : maximum length of 255 bytes
BLOB : maximum length of 65,535 bytes
MEDIUMBLOB : maximum length of 16,777,215 bytes
LONGBLOB : maximum length of 4,294,967,295 bytes
Use LONGBLOBto avoid this exception.
使用LONGBLOB以避免此异常。
回答by Saurabh Kachhia
Use data type LONGBLOBinstead of BLOBin your database table.
使用数据类型LONGBLOB而不是BLOB在您的数据库表中。
回答by 13ushm4n
Following solution worked for me. When connecting to the db, specify that data should be truncated if they are too long (jdbcCompliantTruncation). My link looks like this:
以下解决方案对我有用。连接数据库时，指定数据太长时应截断（jdbcCompliantTruncation）。我的链接是这样的：
jdbc:mysql://SERVER:PORT_NO/SCHEMA?sessionVariables=sql_mode='NO_ENGINE_SUBSTITUTION'&jdbcCompliantTruncation=false
If you increase the size of the strings, you may face the same problem in future if the string you are attempting to store into the DB is longer than the new size.
如果您增加字符串的大小，如果您尝试存储到数据库中的字符串长于新的大小，您将来可能会面临同样的问题。
EDIT: STRICT_TRANS_TABLES has to be removed from sql_mode as well.
编辑： STRICT_TRANS_TABLES 也必须从 sql_mode 中删除。
