java - 数据截断: Data too long for column 'Phone' at row 1

Question

我正在将数据插入到 MYSQL 数据库中的临时表中。我已将“Phone”列的大小指定为 VARCHAR(20)，并插入了恰好 10 个字符的数据，并且它是常量。

这是我的代码:

java.sql.PreparedStatement insertStmt = null;

                 String insertQuery = "insert into "+tableName+"(";
                 for(String col : columnList){
                             insertQuery+=col+",";
                         }
                insertQuery = insertQuery.substring(0,insertQuery.lastIndexOf(","));
                         insertQuery+=") values (";
                         for(String col : columnList){
                             insertQuery+="?,";
                         }
                insertQuery = insertQuery.substring(0,insertQuery.lastIndexOf(","));
                            insertQuery+=")";
                         insertStmt = mysqlConn.prepareStatement(insertQuery);

                while(rs.next())
                     {
                    for(int j=1;j<=columnList.size();j++)
                    {
                        insertStmt.setString(j,rs.getString(j).trim());
                        System.out.println(rs.getString(j).trim());
                    }
                    insertStmt.addBatch();
                    i++;

                    }
                insertStmt.executeBatch();  

                mysqlConn.setAutoCommit(true);
                rs = null;

我收到以下错误:

java.sql.BatchUpdateException: Data truncation: Data too long for column 'Phone' at row 1
Completed Temp tables 1&2
    at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1269)
    at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:955)
    at MultipleDataExtraction.addDataToTempTable(MultipleDataExtraction.java:132)
    at MultipleDataExtraction.addDataToFinalTempTable(MultipleDataExtraction.java:94)

表定义:

EmpNo varchar(15) YES NULL,   Name varchar(18) YES NULL,   Address varchar(40) YES NULL,   Phone varchar(20) YES NULL,   Age varchar(4) YES NULL,  DeptNo varchar(20) YES NULL

插入语句:

insert into temp__final_table(Name,Address,Phone,EmpNo,Age,DeptNo) values ('Clerk#000000959','Skye Norling','2112 Ravine Drive','7091581728','Clerk#000000959','26') 

请帮忙..

Answer 1

这是你的问题:

“2112 峡谷大道”

在插入查询中。

insert into temp__final_table(Name,Address,Phone,EmpNo,Age,DeptNo) values ('Clerk#000000959','Skye Norling','2112 Ravine Drive','7091581728','Clerk#000000959','26')

您正在电话字段中插入地址。

此外，'Clerk#000000959' 似乎是 EmpNo。您似乎将其插入到同一行的多列中。

此外，建议在必要时使用数字字段。您的 DeptNo 看起来是整数类型的完美候选者。您不需要对所有列使用 varchar。

希望这有帮助!!!