拓展閱讀 MySQL View MySQL truncate table 與 delete 清空表的區別和坑 MySQL Ruler mysql 日常開發規範 MySQL datetime timestamp 以及如何自動更新，如何實現範圍查詢 MySQL 06 mysql 如何實現類似 oracl ...

拓展閱讀

emoji

想在 mysql 資料庫插入 emoji 表情，結果報錯：

### Cause: java.sql.SQLException: Incorrect string value: '\xF0\x9F\x92\x8B' for column 'name' at row 1

錯誤原因很多小伙伴也知道，mysql 種的 utf8 和 java 的 utf-8 並不是完全對等的。

應該指定 mysql 的編碼為 utf8mb4 才是正確的。

修改編碼

查看編碼

show variables like 'character_set_database'; # 查看資料庫編碼
show create table comment; # 查看表編碼

修改資料庫 & 表編碼

可以在原來的基礎上直接修改：

alter database <資料庫名> character set utf8mb4; # 修改資料庫
alter table <表名> character set utf8mb4; # 修改表
alter table <表名> change <欄位名> <欄位名> <類型> character set utf8mb4; # 修改欄位

建表時指定

drop database echo_blog;
CREATE DATABASE echo_blog DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

drop table comment;
create table comment
(
    id int unsigned auto_increment comment '主鍵' primary key,
    create_time timestamp default CURRENT_TIMESTAMP not null comment '創建時間',
    update_time timestamp default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP comment '更新時間'
) comment '評論信息' ENGINE=Innodb default charset=UTF8MB4 auto_increment=1;

亂碼問題

修改完成之後，插入成功。

但是資料庫中全部是 ??? 之類的內容，而不是表情內容。

到底哪裡出問題了呢？

mysql 編碼問題

查看 mysql 編碼

SHOW VARIABLES WHERE Variable_name LIKE 'character_set_%' OR Variable_name LIKE 'collation%';

如下：

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                                               |
| character_sets_dir       | D:\tools\mysql\mysql-5.7.24-winx64\share\charsets\ |
| collation_connection     | utf8mb4_unicode_ci                                 |
| collation_database       | utf8mb4_unicode_ci                                 |
| collation_server         | utf8mb4_unicode_ci                                 |
+--------------------------+----------------------------------------------------+

屬性說明：

character_set_client

主要用來設置客戶端使用的字元集。通俗的講就是mysql把客戶端傳遞過來的數據都當成是utf8mb4

character_set_connection

主要用來設置連接資料庫時的字元集，如果程式中沒有指明連接資料庫使用的字元集類型則按照這個字元集設置。

character_set_database

主要用來設置預設創建資料庫的編碼格式，如果在創建資料庫時沒有設置編碼格式，就按照這個格式設置。

character_set_filesystem

文件系統的編碼格式，把操作系統上的文件名轉化成此字元集，即把 character_set_client轉換character_set_filesystem，預設binary是不做任何轉換的。

character_set_results

資料庫給客戶端返回時使用的編碼格式，如果沒有指明，使用伺服器預設的編碼格式。通俗的講就是mysql發送個客戶端的數據是utf8mb4的

character_set_server

伺服器安裝時指定的預設編碼格式，這個變數建議由系統自己管理，不要人為定義。

character_set_system

資料庫系統使用的編碼格式，這個值一直是utf8，不需要設置，它是為存儲系統元數據的編碼格式。

character_sets_dir

這個變數是字元集安裝的目錄。

Mysql的字元集內部處理

1.mysql Server收到請求時將請求數據從 character_set_client 轉換為 character_set_connection

2.進行內部操作前將請求數據從 character_set_connection 轉換為內部操作字元集,步驟如下

　　A. 使用每個數據欄位的 CHARACTER SET 設定值；

　　B. 若上述值不存在，則使用對應數據表的字元集設定值

　　C. 若上述值不存在，則使用對應資料庫的字元集設定值；

　　D. 若上述值不存在，則使用 character_set_server 設定值。

3.最後將操作結果從內部操作字元集轉換為 character_set_results

臨時修改配置

上面的配置都可以通過命令臨時修改：

SET character_set_client = utf8mb4;
SET character_set_connection = utf8mb4;
SET character_set_database = utf8mb4;
SET character_set_results = utf8mb4;
SET character_set_server = utf8mb4;

SET collation_connection = utf8mb4_unicode_ci;
SET collation_database = utf8mb4_unicode_ci;
SET collation_server = utf8mb4_unicode_ci;

當然，也可以通過修改 my.ini 配置文件。

修改 mysql 伺服器配置文件

比如 windows 下個人的 mysql 安裝目錄為：D:\tools\mysql\mysql-5.7.24-winx64

那就在下麵創建 my.ini（如果沒有的話）。

內容如下：

[mysql]
# 設置mysql客戶端預設字元集
default-character-set=utf8mb4
 
[mysqld]
# 設置3306埠
port=3306
# 允許最大連接數
max_connections=20
# 服務端使用的字元集預設為8比特編碼的latin1字元集
character-set-server=utf8mb4
# 創建新表時將使用的預設存儲引擎
default-storage-engine=INNODB
 
collation-server=utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'

character-set-client-handshake = FALSE
explicit_defaults_for_timestamp=true
 
[client]
default-character-set=utf8mb4

修改完成後需要重啟 mysql 服務。

可以在 bin 下執行 mysqld restart。這個實踐下來只初始化了部分編碼。

個人實在 windows services（服務）下，把 mysql 服務進行了重新啟動。

jdbc 配置

druid 數據源配置

spring:
  datasource:
    druid:
      username: root
      password: xxxxxx
      url: jdbc:mysql://localhost:3306/echo_blog?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=UTC
      driver-class-name: com.mysql.jdbc.Driver
      connection-init-sqls: set names utf8mb4;

官方資料

https://dev.mysql.com/doc/connectors/en/connector-j-reference-charsets.html

Notes
For Connector/J 8.0.12 and earlier: In order to use the utf8mb4 character set for the connection, the server MUST be configured with character_set_server=utf8mb4; if that is not the case, when UTF-8 is used for characterEncoding in the connection string, it will map to the MySQL character set name utf8, which is an alias for utf8mb3.

For Connector/J 8.0.13 and later:

When UTF-8 is used for characterEncoding in the connection string, it maps to the MySQL character set name utf8mb4.

If the connection option connectionCollation is also set alongside characterEncoding and is incompatible with it, characterEncoding will be overridden with the encoding corresponding to connectionCollation.

Because there is no Java-style character set name for utfmb3 that you can use with the connection option charaterEncoding, the only way to use utf8mb3 as your connection character set is to use a utf8mb3 collation (for example, utf8_general_ci) for the connection option connectionCollation, which forces a utf8mb3 character set to be used, as explained in the last bullet.

Warning
Do not issue the query SET NAMES with Connector/J, as the driver will not detect that the character set has been changed by the query, and will continue to use the character set configured when the connection was first set up.

說明：

提示
mysql-connector-java 版本在8.0.12之前的，包括8.0.12，服務端必須設置character_set_server=utf8mb4;如果不是的話，就算設置了characterEncoding=UTF-8，照樣會被設置為MYSQL的 utf8字元集，也就是utf8mb3。

對於8.0.13和以後的版本，如果設置了characterEncoding=UTF-8，他會映射到MYSQL的utf8mb4字元集。

如果connectionCollation 也和characterEncoding一起設置了，但是不相容，characterEncoding會被connectionCollation的設置覆蓋掉。

由於沒有Java-Style的utfmb3對應的字元集名稱可以用在connection選項charaterEncoding上，唯一的設置utf8mb3的方式就是在連接選項設置utf8mb3 collation（例如utf8_general_ci），這會強制使用utf8mb3字元集，正如上文所述。

警告
不要通過Connector發起SET NAMES指令，因為driver不會檢測字元集是不是被查詢語句改動，並且當連接第一次建立之後，會繼續使用當時的字元集設置。

可以發現 jdbc 中的配置 connection-init-sqls: set names utf8mb4; 這句話是沒啥用的。

建議老老實實的修改 mysql 服務端的配置。

依然亂碼

這個時候 java 客戶端保存 emoji，依然有部分亂碼。

比如：

MySQL 04-EMOJI 表情與 UTF8MB4 的故事