插入UUID,出现Data truncation: Data too long for column 'id' at row 1

时间:2021-10-24 17:09:19
ssc.udf.register("getuuid", () => UUID.randomUUID().toString)
val stuPCountDF_tmp1
=ssc.sql("select * from stuPCountDF_tmp1 where stuId!='null' order by stuId").distinct()
.selectExpr(
"getuuid() as id","" ,....)

val dbInfo
= XMLUtil.getDBProperties()
val dbConn
= dbInfo._1
val dbProp
= dbInfo._2
val conn: Connection
= XMLUtil.getConnection
val basePsy
= conn.createStatement()
try {
basePsy.executeUpdate(s
"DELETE FROM edu_grade")
}
finally {
basePsy.close()
}
stuPCountDF_tmp1.repartition(
200).write.mode(SaveMode.Append).jdbc(dbConn, "psychology", dbProp)

结果出现:
Data truncation: Data too long for column 'id' at row 1
Caused by: org.springframework.dao.DataIntegrityViolationException: Could not execute JDBC batch update; nested exception is org.hibernate.exception.DataException: Could not execute JDBC batch update
at org.springframework.orm.hibernate3.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:639)
at

Caused by: java.sql.BatchUpdateException: Data truncation: Data too long for column 'id' at row 1
at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1269)
at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:955)
at

查询得知:

如题:错误并不是因为字段过长而导致出错的,

        1. 可能是因为数据库里的表设置的字符集不相同。

        2. 也可能真是数据库字段里数据库确实过长(我是遇到的这种情况)。

例如:在同一个数据库中,存在utf8的表,也存在gbk_chinese_ci的表。

解决办法:alter table `cms_activity_test` convert to character set gbk collate gbk_chinese_ci;(单独修改指定表的字符集)

补充:通过在网上查找资料,一般都是修改数据库MySQL的默认字符集和修改单个数据库的字符集

1。 在Mysql数据库目录下找到my.ini文件,然后Ctrl+F查找character-set,修改

插入UUID,出现Data truncation: Data too long for column 'id' at row 1
[client]

port=3306

[mysql]

default-character-set=utf8
插入UUID,出现Data truncation: Data too long for column 'id' at row 1

2. 修改单个数据库字符集,data目录下找到db.opt文件,然后修改

default-character-set=gbk
default-collation=gbk_chinese_ci

3. 用命令设置

插入UUID,出现Data truncation: Data too long for column 'id' at row 1
mysql> SET character_set_client = utf8 ;
mysql> SET character_set_connection = utf8 ;
mysql> SET character_set_database = utf8 ;
mysql> SET character_set_results = utf8 ;
mysql> SET character_set_server = utf8 ;
mysql> SET collation_connection = utf8 ;
mysql> SET collation_database = utf8 ;
mysql> SET collation_server = utf8 ;
插入UUID,出现Data truncation: Data too long for column 'id' at row 1

 4. 如果还嫌太麻烦了,就用一个蠢办法,如果整个数据库表不多且数据量不大的情况下,就将整个库导出,然后再去修改文件中字符集不一的表,最后再导入数据库。。。

    最后台:通过修改id的字段的长度解决