scala slick mysql utf8mb4 支持

语言 scala

sql包 slick 3.2.0

数据库 mysql

https://stackoverflow.com/questions/36741141/scala-slick-jdbc-encoding-configuration-with-typesafe-config/50153514

简代版 https://github.com/cclient/ScalaMysqlSync

数据库同步程序报错

java.sql.SQLException: Incorrect string value: 'xF0x9Fx87xB5xF0x9F...' for column 'content' at row 1

这个错误见的太多，emoji字符和utf-8不兼容所致，utf8mp4字符集支持emoji

源库(另外一家公司提供)改了表的字符集，未通知我方，从库和源库字符集不同，同步时部分字符报错

改mysql,改同步程序字符集即可

改mysql简单

ALTER TABLE table  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

难在改程序的同步集，这个是个大坑，不同语言，不同访问方式的解决方式不一样

该同步服务用scala实现，引用的slick，没有任何资料，用小众的东西就是遇到坑，很多时候得独立解决

传统java项目思路都是ini sql，不过ini方式不同。

试了两种方案

1 连接先执行，不生效

val DEST_DB = Database.forURL("jdbc:mysql://127.0.0.1:3306/table?characterEncoding=UTF-8&charset=utf8mb4", user = "root", password = "123456", driver = "com.mysql.jdbc.Driver")

ORIGIN_DB.run(sql"""SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci""".as[Int])

2连接初始化，这是蒙的，也不生效

val ORIGIN_DB = Database.forURL("jdbc:mysql://127.0.0.1:3306/table?charset=utf8mb4;INIT="SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci"", user = "root", password = "123456", driver = "com.mysql.jdbc.Driver",executor = AsyncExecutor("orgindb", minThreads=1, queueSize=10,maxThreads=10,maxConnections=10),keepAliveConnection=true)

暂时走到死胡同了

查slick文档也没有任何相关资料，但是注意到一个关键字 HikariCP

这是个sql连接池

之前开发spring-boot spring-cloud 项目接触过，也碰上过HikariCP utf8mb4的相关问题

见 http://www.cnblogs.com/zihunqingxin/p/8650470.html

https://github.com/brettwooldridge/HikariCP 支持 connectionInitSql 配置

spring-boot当时是通过解决

spring.datasource.hikari.connection-init-sql=SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci;

既然不能直接解决utf8mb4的问题，那通过HikariCP连接池，倒可能解决该问题

配置文件设置，官网上信息很少，得参照代码实现设置

https://github.com/slick/slick/blob/master/slick-hikaricp/src/main/scala/slick/jdbc/hikaricp/HikariCPJdbcDataSource.scala#L13

application.conf

mysql_orginal = {
  connectionPool = "HikariCP"
  driver = "com.mysql.jdbc.Driver"
  connectionInitSql ="SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci"
  url = "jdbc:mysql://127.0.0.1/table?useUnicode=true&characterEncoding=UTF-8"
  keepAliveConnection = true
  user = "root"
  password = "123456"
  numThreads = 4
  poolName="mysql_orginal_pool"
}

再 val ORIGIN_DB = Database.forConfig("mysql_orginal")

编译通过，但执行报错

java.lang.ExceptionInInitializerError
    at main.tables.LastActor$$anonfun$receive$1.applyOrElse(last.scala:53)
    at akka.actor.Actor.aroundReceive(Actor.scala:513)
    at akka.actor.Actor.aroundReceive$(Actor.scala:511)
    at main.tables.LastActor.aroundReceive(last.scala:31)
    at akka.actor.ActorCell.receiveMessage(ActorCell.scala:527)
    at akka.actor.ActorCell.invoke(ActorCell.scala:496)
    at akka.dispatch.Mailbox.processMailbox(Mailbox.scala:257)
    at akka.dispatch.Mailbox.run(Mailbox.scala:224)
    at akka.dispatch.Mailbox.exec(Mailbox.scala:234)
    at akka.dispatch.forkjoin.ForkJoinTask.doExec(ForkJoinTask.java:260)
    at akka.dispatch.forkjoin.ForkJoinPool$WorkQueue.runTask(ForkJoinPool.java:1339)
    at akka.dispatch.forkjoin.ForkJoinPool.runWorker(ForkJoinPool.java:1979)
    at akka.dispatch.forkjoin.ForkJoinWorkerThread.run(ForkJoinWorkerThread.java:107)
Caused by: java.lang.ClassNotFoundException: slick.jdbc.hikaricp.HikariCPJdbcDataSource$

错误表示连接池生效，只是找不到相关类

引入依赖

libraryDependencies +=  "com.typesafe.slick" %% "slick-hikaricp" % "3.2.0"

重新编译

执行 utf8mb4 问题解决

其他参考资料
http://slick.lightbend.com/doc/3.2.0/database.html#databaseconfig
https://github.com/slick/slick/blob/6ac40e3b7f241475a122eb575f7ce0286080dd52/doc/code/application.conf
https://github.com/slick/slick/blob/4674b977d9eeaf825d57808d48dbbfc37e47858c/doc/src/config.md