caca's Blog

道法自然

0%

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