Play Framework1.4.2引入MariaDB使用utf8mb4编码

最近挺忙的,一直没时间做其他事情,这两天好不容易解决了几个问题,于是把问题的经过整理一下,希望遇到类似问题的小伙伴能快速解决。

步骤一:Play Framework引入MariaDB,这个相对简单,只要从maven上下载一个类库然后修改application.conf即可。

1
2
3
4
5
db.default.url=jdbc:mariadb://ip/db?useUnicode=yes&characterEncoding=utf8mb4&connectionCollation=utf8mb4_general_ci&zeroDateTimeBehavior=convertToNull
db.default.driver=org.mariadb.jdbc.Driver
db.default.user= 用户
db.default.pass= 密码
jpa.default.dialect=org.hibernate.dialect.MySQLDialect

最后一句不加的话,play!运行的时候会报错。
步骤二:接下来就是修改MariaDB的编码了。

1
2
3
4
5
6
7
8
9
10
11
12
13
vim /etc/my.cnf  

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
init-connect='SET NAMES utf8mb4'
collation_server=utf8mb4_unicode_ci
character_set_server=utf8mb4
character-set-client-handshake = FALSE

重启mysqld,service mysqld restart,查看是否生效

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';  
+--------------------------+--------------------+
| Variable_name | Value |
+--------------------------+--------------------+
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
| collation_connection | utf8mb4_unicode_ci |
| collation_database | utf8mb4_unicode_ci |
| collation_server | utf8mb4_unicode_ci |
+--------------------------+--------------------+
10 rows in set (0.00 sec)

步骤三:就是将之前的数据表都刷成utf8mb4_unicode_ci编码

1
2
3
ALTER SCHEMA `db`  DEFAULT COLLATE utf8mb4_general_ci ;  
SELECT CONCAT('alter table ',table_name,' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;') FROM INFORMATION_SCHEMA.tables
WHERE TABLE_SCHEMA='库名' and table_type='BASE TABLE' into outfile '/data/altertable.sql';

这个语句可以生成语句,然后在mysql中执行即可

1
mysql -u用户名 -p表名 实力名称  < /data/altertable.sql

字段类型的修改可以参考下方语句

1
2
3
4
SELECT CONCAT('ALTER TABLE `', table_name, '` MODIFY `', column_name, '` ', DATA_TYPE, '(', CHARACTER_MAXIMUM_LENGTH, ') CHARACTER SET UTF8 COLLATE utf8_general_ci', (CASE WHEN IS_NULLABLE = 'NO' THEN ' NOT NULL' ELSE '' END), ';')FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = '库名' AND DATA_TYPE = 'varchar' AND (
CHARACTER_SET_NAME != 'utf8'
OR
COLLATION_NAME != 'utf8_general_ci') ;

至此,所有的操作都已完成。

参考网址:
https://mathiasbynens.be/notes/mysql-utf8mb4
http://blog.flect.co.jp/labo/2012/07/playmysql-283f.html