移动应用开发中后台mysql数据库的编码问题

时间:2023-01-10 11:42:15

 移动开发中,关于手机输入法中自带的Emoji表情,采用的是unicode6 和softbank 等编码格式,该格式为4字节;而mysql如果采用UTF-8 编码,只支持1-3个字节,两者编码格式不适, 往数据库中存储emoji表情时会引起存储错误, 错误信息类似如下:

Caused by: java.sql.SQLException: Incorrect string value: '\xF0\x9F\x98\x81' for column 'text' at row 1

 为了解决这个问题,从MySQL 5.5.3 开始,MySQL 支持一种utf8mb4 的字符集,这个字符集能够支持4 字节的 UTF8 编码的字符。 utf8mb4 字符集能够完美地向下兼容 utf8 字符串。

如果原数据库采用了UTF8的编码,则windows下可以通过如下方式进行编码转换。(Linux下采用此方法试过了不行,是由于转换编码之后,会影响mysql权限表中用户的密码,导致mysql无法启动或者登录不上,因此在linux系统中,只能通过编译安装的方式来安装mysql,安装完启动之前修改编码,则可以正常运行)


1.      备份数据库

2.      升级mysql5.5.3以上的版本

3.      修改数据库、数据表、字段的字符集编码和校验编码为utf8mb4

a) 修改数据库编码:ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE =utf8mb4_unicode_ci;

b) 修改数据表编码:ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATEutf8mb4_unicode_ci;

c) 修改字段编码: ALTER TABLE table_nameCHANGE column_name column_name VARCHAR(191) CHARACTER SET utf8mb4 COLLATEutf8mb4_unicode_ci;

其中在b)c)两步之前,如果表中有历史数据,字段数据类型为VARCHAR(255),并且该字段的历史数据的字符长度超过191,则需要先把该字段的数据类型修改为可以保存更大长度的。

这是因为使用utf8mb4编码后,一个原utf8编码中的VARCHAR(255)的字段,最多只能保存utf8mbs编码下的191个字符。

同理,如果待转换的数据表上某一列创建有索引,并且该列字符长度超过了191个字符,转换之前则需要把索引移除,因为utf8mb4下只能索引191个字符以下的列

 

4.      修改mysql server的配置文件

配置文件my.ini (Windows下默认路径为C:\ProgramData\MySQL\MySQL Server 5.5Linux下为/etc/my.cnf)中加入或者修改以下内容:

[client]

default-character-set = utf8mb4

 

[mysql]

default-character-set = utf8mb4

 

[mysqld]

character-set-server = utf8mb4

collation-server = utf8mb4_unicode_ci

 

5. 

把配置文件C:\Program Files\MySQL\MySQL Server5.5\share\charsets\Index.xml中的utf8编码这一段复制一下,然后将<charset name="utf8">改为<charset name="utf8mb4">,修改方法如下:

[修改前:]

<charset name="utf8">

 <family>Unicode</family>

 <description>UTF-8 Unicode</description>

 <alias>utf-8</alias>

 <collation name="utf8_general_ci"     id="33">

  <flag>primary</flag>

  <flag>compiled</flag>

 </collation>

 <collation name="utf8_bin"            id="83">

   <flag>binary</flag>

   <flag>compiled</flag>

 </collation>

</charset>

 

[修改后:]

<charset name="utf8mb4">

 <family>Unicode</family>

 <description>UTF-8 Unicode</description>

 <alias>utf-8</alias>

 <collation name="utf8_general_ci"     id="33">

  <flag>primary</flag>

  <flag>compiled</flag>

 </collation>

 <collation name="utf8_bin"            id="83">

   <flag>binary</flag>

   <flag>compiled</flag>

 </collation>

</charset>



修改后需重启mysql 服务,在客户端执行该命令:SET NAMES utf8mb4COLLATE utf8mb4_unicode_ci


6.      在应用服务器连接mysql服务器的jdbc连接中,加入连接参数,(?之后的内容):

jdbc:mysql://localhost:3306/databaseName?autoConnect=true&amp;useUnicode=true&amp;characterEncoding=utf-8

 

7.      更换应用服务器tomcat或者web应用lib文件夹下的mysqljdbc驱动版本,使用5.1.31或以上。


可参考http://www.hksilicon.com/kb/cn/articles/339026/MySQL-with-utf8mb4 和 http://dev.mysql.com/doc/refman/5.5/en/charset-unicode-upgrading.html



------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

附件中是centos7下可以使用的my.cnf配置文件。

需要手动创建一下配置文件:

/var/lib/mysqltmp

/var/log/mysql

同时修改它们的所有权为mysql用户和mysql用户组: sudo chown mysql:mysql -R /var/lib/mysqltmp


Centos7上修改防火墙配置:

firewall-cmd --zone=public --add-port=3306/tcp --permanent

命令含义:   --zone #作用域   --add-port=80/tcp  #添加端口,格式为:端口/通讯协议   --permanent   #永久生效,没有此参数重启后失效   重启防火墙   firewall-cmd --reload-----------

之后可以远程登录了。

_--------------------修改root用户密码

mysqladmin -u root password 'root' 

------------------------

给用户修改权限

GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'192.168.1.3' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;   

FLUSH   PRIVILEGES;