設定 MariaDB / MySQL 真・UTF-8 編碼

Leon
·
(修改过)
·
IPFS
·
MariaDB 遺傳了 MySQL 的老問題,utf8 是假的 UTF-8,utf8mb4 才是真的 UTF-8,必須改一下 MariaDB 的設定檔才能正確的指定使用真・UTF-8 編碼。

MariaDB 遺傳了 MySQL 的老問題,utf8 是假的 UTF-8,utf8mb4 才是真的 UTF-8。

參照:

依照我的 CentOS 做出對應修改。

改 /etc/opt/rh/rh-mariadb102/my.cnf.d/mariadb-server.cnf:

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-server = utf8mb4
character-set-client-handshake = FALSE

[mariadb]
character_set_server = utf8mb4
character_set_client = utf8mb4

加上這些設定敘述。

重啟 MariaDB 服務:

> sudo systemctl restart rh-mariadb102-mariadb

確認一下狀態:

> sudo systemctl status rh-mariadb102-mariadb

進去 MariaDB 看一下現在的編碼:

show variables like 'char%';
show variables like 'collation%';

會看到像這樣滿滿的 utf8mb4

+--------------------------+---------+
| 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       |         |
+--------------------------+---------+

+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_general_ci |
| collation_database   | utf8mb4_general_ci |
| collation_server     | utf8mb4_general_ci |
+----------------------+--------------------+

檢視 database、table、column 的編碼

檢視 database 編碼:

SELECT default_character_set_name
FROM information_schema.SCHEMATA 
WHERE schema_name = "schemaname";

檢視 table 編碼:

SELECT CCSA.character_set_name
FROM information_schema.`TABLES` T, information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA
WHERE CCSA.collation_name = T.table_collation
    AND T.table_schema = "schemaname"
    AND T.table_name = "tablename";

檢視 column 編碼:

SELECT character_set_name
FROM information_schema.`COLUMNS` 
WHERE table_schema = "schemaname"
    AND table_name = "tablename"
    AND column_name = "columnname";

以上來自 〈How do I see what character set a MySQL database / table / column is?

CC BY-NC-ND 2.0 授权

喜欢我的作品吗?别忘了给予支持与赞赏,让我知道在创作的路上有你陪伴,一起延续这份热忱!

Leonhttps://editor.leonh.space/
  • 来自作者
  • 相关推荐

十分鐘自架 Drone CI

十分鐘自架 Gitea

Heroku 大逃殺之 Render