Spark将DataFrame写入MySQL时遇到的问题

DataFrame如何写入MySQL

val host = "localhost"
val port = "3306"
val user = "user"
val password = "password"
val database = "test"
val table = "test"
val saveMode = SaveMode.Overwrite  // 支持4中写入方式Append、Overwrite、ErrorIfExists、Ignore

val prop = new java.util.Properties
prop.setProperty("user", user)
prop.setProperty("password", password)
prop.setProperty("driver", "com.mysql.jdbc.Driver")

df.write.mode(saveMode).jdbc(s"jdbc:mysql://$host:$port/$database", s"$table", prop)      

错误1：成功写入到MySQL，但是出现中文乱码

将最后一行里的url改成s”jdbc:mysql://host:

h

o

s

t

:

port/$database?useUnicode=true&characterEncoding=utf8”可解决中文乱码

错误2：Incorrect string value: ‘\xF0\x9F…’ for column ‘title’ at row 1的异常

修改url之后又报错：​

​Incorrect string value: '\xF0\x9F...' for column 'title' at row 1​

​

原因：​

​mysql中utf8字符MaxLen=3，但是DataFrame的字段title里某些字符转成utf8编码之后有4个字节​

​

解决方案：

1.在mysql的安装目录下找到my.ini,作如下修改：

[mysqld]

character-set-server=utf8mb4

[mysql]

default-character-set=utf8mb4      

修改后重启Mysql sudo service mysql restart

2.将已经建好的表也转换成utf8mb4

​

​alter table TABLE_NAME convert to character set utf8mb4 collate utf8mb4_bin;​

​