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;​

​