数据截断:第 1 行“电话"列的数据太长 [英] Data truncation: Data too long for column 'Phone' at row 1

问题描述

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

I am inserting data into temp table in MYSQL database.I have given size of 'Phone' column as VARCHAR(20) and have inserted data of exactly 10 character and it is constant.

这是我的代码:

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') 

请帮忙..

推荐答案

这是你的问题:

'2112 Ravine Drive'

在插入查询中.

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

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

You are inserting address in the phone field.

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

Also, 'Clerk#000000959' seems to be the EmpNo. You seem to be inserting it in multiple columns in the same row.

此外，建议在必要时使用 numeric 字段.您的 DeptNo 看起来很适合整数类型.您不需要对所有列都使用 varchar.

Also, a suggestion would be to use numeric fields where necessary. Your DeptNo looks like a perfect candidate for integer type. You don't need to use varchar for all columns.

希望这有帮助！！！

这篇关于数据截断:第 1 行“电话"列的数据太长的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！