MySQL JDBC表情編碼配置

在MySQL中，表情符號需要4個(gè)字節(jié)的空間存儲，因此如果某個(gè)列需要兼容表情存儲，需要設(shè)置該列的字符集為utf8mb4:

1? ALTER TABLE `db`.`tablename`?

2? CHANGE COLUMN `content` `content` VARCHAR(45) CHARACTER SET 'utf8mb4' NULL DEFAULT NULL COMMENT '' ;

另外，根據(jù)Mysql的文檔：

Setting the Character Encoding

The character encoding between client and server is automatically detected upon connection. You specify the encoding on the server using the character_set_server for server versions 4.1.0 and newer, and character_set system variable for server versions older than 4.1.0. The driver automatically uses the encoding specified by the server. For more information, see Server Character Set and Collation.

For example, to use 4-byte UTF-8 character sets with Connector/J, configure the MySQL server with character_set_server=utf8mb4, and leave characterEncoding out of the Connector/J connection string. Connector/J will then autodetect the UTF-8 setting.

To override the automatically detected encoding on the client side, use the characterEncoding property in the URL used to connect to the server.

也就是說，沒辦法在JDBC客戶端直接配置編碼方式，因此需要修改mysql服務(wù)器的如下屬性：

1? set? character_set_server=utf8mb4

要永久生效，在my.ini配置文件中配置：

1? character_set_server=utf8mb4

同時(shí)，JDBC Url的characterEncoding去掉：

1? datasource.jdbcUrl=jdbc:mysql://127.0.0.1:4360/mydb?useUnicode=true&autoReconnect=true&allowMultiQueries=true

總結(jié)一下，為了在Java端支持表情，需要提供4個(gè)字節(jié)存儲的編碼方案，具體步驟如下：?

1) 設(shè)置對應(yīng)列的編碼為utf8mb4

2) 設(shè)置服務(wù)器編碼為utf8mb4

3) JDBC Url中的characterEncoding不配置。?

（該屬性不支持utf8mb4,配置了非utf8mb4將導(dǎo)致無法寫入表情，因此要留空，不配置）