擴充閱讀
MySQL View
MySQL truncate table 與 delete 清空表的區別和坑
MySQL Ruler mysql 日常開發規範
MySQL datetime timestamp 以及如何自動更新，如何實現範圍查詢
MySQL 06 mysql 如何實現類似 oracle 的 merge into
MySQL 05 MySQL入門教程（MySQL tutorial book）
MySQL 04- EMOJI 表情與 UTF8MB4 的故事
MySQL Expression 1 of ORDER BY clause is not in SELECT list,references column
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，依然有部分亂碼。
比如：
💔✊💓💖😧😯 I Love this!
用命令列檢視，資料庫變成了：
�✊���� I Love this!
部分亂碼？ what's up？
java 程式斷點
在 java 應用中進行斷點，發現內容是對的。
mysql 命令列插入
我們直接在 mysql 命令列執行插入：
insert into comment (content) values ('💔✊💓💖😧😯 I Love this!');
MD，發現無法執行，這條路走不通。
命令列終端不支援 emoji 表情。
亂碼的原因
這個亂碼是因為 mysql 終端導致的，還是別的原因？
我們測試一下，使用 java 程式對內容進行查詢。
測試程式碼
@RunWith(SpringRunner.class)
@SpringBootTest(classes = Application.class)
public class CommentServiceTest {
@Autowired
private CommentService commentService;
@Test
public void selectTest() {
Comment comment = commentService.selectById(20);
System.out.println(comment);
}
}
測試效果
日誌如下：
Comment{id=20, content=💔✊💓💖😧😯 I Love this!, ackFlag=N, createTime=Sun Aug 15 10:13:56 CST 2021, updateTime=Sun Aug 15 10:13:56 CST 2021}
可以發現，儲存的資料本身是沒有問題的。
是命令列終端的問題。
命令列的問題
已有的命令列
一開始使用的是 windows10 自帶的 cmd，發現不行。
使用 cmder 命令列，還是不行。
powershell
我們測試下微軟商店的 Terminal 命令列，也就是新版本的 powershell。
cd D:\tools\mysql\mysql-5.7.24-winx64\bin
.\mysql -uroot -p
登入後執行查詢，結果如下：
💔✊💓💖😧😯 I Love this!
糾結了半天的亂碼，經確認是命令列終端的問題。
參考資料
MySQL的utf8、utf8mb4、編碼問題詳解
mysql字符集utf8mb4失效踩坑
Mysql UTF-8mb4字符集的問題