一、报错信息
SparkSql代码写入Mysql逻辑如下,代码会自动生成目标Mysql表,报错如图:
frame.createTempView("job_detail")
session.sql("select * from job_detail").show()
val url = "jdbc:mysql://XXXXX:3306/mydb?useSSL=false&useUnicode=true&characterEncoding=UTF-8"
val table = "mydb.job_detail_copy"
val properties = new Properties()
properties.setProperty("user","XXXX")
properties.setProperty("password","XXXX")
frame.write.mode(SaveMode.Append).jdbc(url,table,properties)
报错信息:
java.sql.BatchUpdateException: Incorrect string value: '\xE5\xBC\x80\xE5\x8F\x91...' for column 'job_name' at row 1
image.png
二、解决方案
原因:代码自动生成的目标表字段和表编码为latin1格式,导致数据写入报错
解决方案:修改目标表字段和表编码格式为utf8
//修改目标表所有字段编码格式为utf8
alter table job_detail_copy convert to character set utf8;
//修改目标表表编码格式为utf8
alter table mydb.job_detail_copy character set utf8;