问题
JDBC操作MySQL数据库,当进行插入或更新操作的数据长度超过表字段的声明最大长度时,会报出以下错误,导致不能正常插入:
SQLException: Data truncation: Data too long for column 'diff' at row 2
但是当直接在MySQL客户端操作时,发现确实可以的,只不过会自动对插入的数据进行截断处理:
'diff'字段的长度为3,下面插入一个超长的数据'1234':
可以成功插入,并对数据进行了截断处理,插入结果为'123',不过MySQL发出了警告(warning)。
但是到了JDBC就是SQLException了。
解决
JDBC Driver作为MySQL Client与MySQL Server交互时,JDBC Driver默认会设置会话SQL_MODEL='STRICT_TRANS_TABLES',可以写一个sql语句:'select @@session.sql_mode;',executeQuery执行以下(详见最后源码),默认情况下JDBC的jdbcCompliantTruncation(是否截断)参数为true,修改为false即可解决。(MySQL本身也可以设置相关的配置,详见MySQL——SQL Mode详解)
将JDBC连接的URL改为:
jdbc:mysql://localhost:3306/table_name?jdbcCompliantTruncation=false
不过这样会存在另外一个问题,尝试更新int类型的数据,如果插入数据超长(也就是超过可以装载的大小),将插入字段的最大值:
所以最好的解决办法还是在程序中做判断+截断吧!
也算一种解决方法
问题的产生就是,JDBC并不能对超长的插入字段进行自动截断处理并顺利插入数据,所以如果在SQL中获得字段的声明长度,再用MySQL提供的left等截断函数,也能实现,不过需要子查询,效率可想而知。
获得某库某表某字段的声明长度SQL:
再加上LEFT()函数:
String update = "update wm_poi_dispatch_setting set diff=LEFT('4567',
(select CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'wm_poi_dispatch_setting' and table_schema = 'poi_test' and column_name = 'diff' limit 0,1)
) " + "where wm_poi_id = 1
";
效率过低,忽略。
源码
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
1 package test.jdbc; 2 3 import java.sql.*; 4 5 /** 6 * Created by zhengbin06 on 16/9/14. 7 */ 8 public class DataBaseTest { 9 public static Connection getConnection() throws SQLException, 10 java.lang.ClassNotFoundException { 11 Class.forName("com.mysql.jdbc.Driver"); 12 String url = "jdbc:mysql://localhost:3306/poi_test?jdbcCompliantTruncation=true"; 13 // String url = "jdbc:mysql://localhost:3306/poi_test?jdbcCompliantTruncation=false"; 14 String username = "root"; 15 String password = ""; 16 Connection con = DriverManager.getConnection(url, username, password); 17 return con; 18 } 19 20 public static void main(String args[]) { 21 try { 22 Connection con = getConnection(); 23 Statement sql_statement = con.createStatement(); 24 25 // String query = "select * from wm_poi_dispatch_setting"; 26 String query = "select @@session.sql_mode;"; 27 // String update = "update wm_poi_dispatch_setting set diff=LEFT('4567', (select CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'wm_poi_dispatch_setting' and table_schema = 'poi_test' and column_name = 'diff' limit 0,1)) " + 28 // "where wm_poi_id = 1"; 29 // sql_statement.executeUpdate(update); 30 ResultSet result = sql_statement.executeQuery(query); 31 32 // System.out.println("Student表中的数据如下:"); 33 // System.out.println("------------------------"); 34 // System.out.println("wm_poi_id" + " " + "logistics_id" + " " + "value" + " " + "diff"); 35 // System.out.println("------------------------"); 36 37 // while (result.next()) { 38 // int wm_poi_id = result.getInt("wm_poi_id"); 39 // int logistics_id = result.getInt("logistics_id"); 40 // int value = result.getInt("value"); 41 // String diff = result.getString("diff"); 42 // System.out.println(" " + wm_poi_id + " " + logistics_id + " " + value + " " + diff); 43 // } 44 System.out.println("|@@session.sql_mode|"); 45 while(result.next()) { 46 String sql_mode = result.getString("@@session.sql_mode"); 47 System.out.println(sql_mode); 48 } 49 sql_statement.close(); 50 con.close(); 51 } catch (java.lang.ClassNotFoundException e) { 52 System.err.print("ClassNotFoundException"); 53 System.err.println(e.getMessage()); 54 } catch (SQLException ex) { 55 System.err.println("SQLException: " + ex.getMessage()); 56 } 57 } 58 }