關(guān)鍵字: 字符集，utf8mb4，emoj

眾所周知，mysql的utf8是假的utf8，沒法存emoj等字符。要設(shè)置為utf8mb4...

問題

同事給了一段Update語句，更新某張表id=xxx的某個(gè)字段;

CREATE TABLE `table_name` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `xxx_id` int(11) NOT NULL,
  `description` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `end_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `max_xxx` int(11) NOT NULL DEFAULT '0',
  `max_xxx` int(11) NOT NULL DEFAULT '0',
  `xxx_generate_method` tinyint(4) NOT NULL,
  `xxx_generate_method` tinyint(4) NOT NULL,
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_table_name_xxx_id` (`xxx_id`),
  KEY `idx_table_name_end_time` (`end_time`),
  KEY `idx_table_name_start_time` (`start_time`)
) ENGINE=InnoDB AUTO_INCREMENT=5822 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

登陸跳板機(jī)，連接遠(yuǎn)程數(shù)據(jù)庫后，執(zhí)行sql，報(bào)錯(cuò): ERROR 1366 (HY000): Incorrect string value: '\xF0\x9F\x93\xA3Ev...'

\xF0\x9F\x93\xA3恰好是轉(zhuǎn)義之后的emoj

這張表所在的庫的字符集是utf8，但是表指定了是utf8mb4，字段沒有指定，僅指定了排序方式為 utf8mb4_unicode_ci

據(jù)說，字符集規(guī)則會(huì)按照 字段設(shè)置>表設(shè)置>庫設(shè)置的順序。

此處 這個(gè)字段沒有設(shè)置字符集，那應(yīng)該用表的字符集即*DEFAULT CHARSET=utf8mb4 *

(且經(jīng)過試驗(yàn)，如果COLLATE=utf8mb4_unicode_ci，那字符集不可能是utf8，只可能是utf8mb4，不然報(bào)錯(cuò)時(shí)會(huì)直接報(bào)錯(cuò))

下面補(bǔ)充一些mysql字符集的知識

查看庫級別的 字符集和編碼設(shè)置

SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';

Variable_name
character_set_client
character_set_connection
character_set_database
character_set_filesystem
character_set_results
character_set_server
character_set_system
character_sets_dir 

這都是干啥的？

這些變量是 MySQL 中與字符集相關(guān)的變量，用于控制不同環(huán)境中的字符集設(shè)置。以下是對每個(gè)變量的簡要說明：

1. character_set_client : 客戶端連接到 MySQL 服務(wù)器時(shí)所使用的字符集。
2. character_set_connection : 當(dāng)前連接的默認(rèn)字符集。它可以在客戶端連接時(shí)通過 SET NAMES 命令來設(shè)置。
3. character_set_database : 默認(rèn)數(shù)據(jù)庫的字符集。在創(chuàng)建數(shù)據(jù)庫時(shí)設(shè)置，新創(chuàng)建的表將繼承該字符集。
4. character_set_filesystem : 文件系統(tǒng)的默認(rèn)字符集。用于存儲(chǔ)文件名和路徑的字符集。
5. character_set_results : 返回給客戶端的結(jié)果集的字符集。
6. character_set_server : MySQL 服務(wù)器的默認(rèn)字符集。用于新建數(shù)據(jù)庫、表和列的默認(rèn)字符集。
7. character_set_system : MySQL 系統(tǒng)數(shù)據(jù)字典和內(nèi)部字符串的字符集。
8. character_sets_dir : MySQL 字符集定義文件的目錄路徑。

這些變量的設(shè)置是相互關(guān)聯(lián)的，通過調(diào)整它們的值可以控制 MySQL 在不同環(huán)境中的字符集行為。確保這些變量的值一致并與你的應(yīng)用程序和數(shù)據(jù)的字符集一致，可以確保正確地存儲(chǔ)、傳輸和顯示數(shù)據(jù)。

注意：在修改這些字符集相關(guān)的變量之前，請確保了解其含義和影響，并在備份數(shù)據(jù)的情況下謹(jǐn)慎操作。修改字符集設(shè)置可能會(huì)對現(xiàn)有數(shù)據(jù)和應(yīng)用程序產(chǎn)生影響。

一般說的字符集和排序規(guī)則，應(yīng)該主要看

SHOW VARIABLES LIKE 'character_set_database';
SHOW VARIABLES LIKE 'collation_database';

查看表級別的字符集和編碼設(shè)置

SHOW CREATE TABLE `your_table_name`;

能得到建表語句，看最后的 DEFAULT CHARSET

具體到table的column的字符集如何查看？

SHOW FULL COLUMNS FROM your_table_name;

在查詢結(jié)果中，查找 "Collation" 列。該列顯示每個(gè)列（字段）的字符集和排序規(guī)則。

請注意，"Collation" 列中的值表示字符集和排序規(guī)則的組合。常見的字符集包括 UTF-8（如 utf8mb4）和 Latin1（如 latin1）。

Collation 本意是?？保χ?，在數(shù)據(jù)庫中 是排序規(guī)則

這個(gè)字段的第一部分，其實(shí)已經(jīng)指明了字符集...所以SHOW FULL COLUMNS沒有必要再多一個(gè)字符集列

那么，問題何在呢？

起初搜到，需要在連接時(shí)指定為utf8mb4才可以

即 mysql --default-character-set=utf8mb4 -u root -h xxx.xxx.xx.xx -p密碼

但還是不行...

最后發(fā)現(xiàn)執(zhí)行 SET NAMES utf8mb4 后再執(zhí)行更新語句，成功！

根據(jù)報(bào)錯(cuò)信息，看起來在嘗試更新 xxxxx 表中的 xxxxxxx 字段時(shí)遇到了錯(cuò)誤。報(bào)錯(cuò)信息是 ERROR 1366 (HY000): Incorrect string value: '\xF0\x9F\x93\xA3Ev...' for column 'xxxxxxx' at row 1 ，這表明在該字段中插入了無效的字符。

盡管您已經(jīng)將該字段的字符集設(shè)置為 utf8mb4 ，支持存儲(chǔ)四字節(jié)的UTF-8字符，但有可能連接或會(huì)話的字符集設(shè)置仍然不兼容。為了確保字符集一致，您可以嘗試執(zhí)行以下操作：

1. 在更新語句之前，執(zhí)行以下SQL語句設(shè)置會(huì)話字符集為 utf8mb4 ：

SET NAMES utf8mb4;

1. 然后，再執(zhí)行更新語句：

UPDATE `xxxxx` SET `xxxxxxx` = '...' WHERE (`id` = 5136);

通過這樣的設(shè)置，會(huì)話的字符集將與字段的字符集一致，可以正常存儲(chǔ)包含四字節(jié)UTF-8字符的字符串。

如果問題仍然存在，請確保數(shù)據(jù)庫連接和客戶端工具的字符集設(shè)置與字段的字符集設(shè)置一致。另外，您還可以檢查MySQL服務(wù)器的全局字符集設(shè)置，以確保它也是 utf8mb4 。

SET NAMES utf8mb4什么?

"SET NAMES utf8mb4" 是一個(gè)MySQL命令，用于設(shè)置數(shù)據(jù)庫連接的字符集編碼為utf8mb4。在MySQL中，字符集決定了數(shù)據(jù)庫如何存儲(chǔ)和處理數(shù)據(jù)。

utf8mb4是一種字符集編碼，支持Unicode字符集的全部字符，包括一些較早版本的utf8字符集無法表示的一些字符。utf8mb4編碼可以處理各種語言的文字和符號，包括一些較為特殊的字符，如表情符號和特殊符號等。

通過將數(shù)據(jù)庫連接的字符集編碼設(shè)置為utf8mb4，可以確保數(shù)據(jù)庫可以正確地存儲(chǔ)、檢索和顯示各種字符，避免出現(xiàn)亂碼或字符丟失的問題。這在處理多語言或包含特殊字符的應(yīng)用程序中特別重要。

只對當(dāng)前連接有效嗎？

是的，"SET NAMES utf8mb4" 命令只對當(dāng)前數(shù)據(jù)庫連接有效。這意味著只有在執(zhí)行該命令后建立的數(shù)據(jù)庫連接會(huì)使用 utf8mb4 字符集編碼。

如果你在程序中使用了多個(gè)數(shù)據(jù)庫連接，每個(gè)連接都需要單獨(dú)執(zhí)行 "SET NAMES utf8mb4" 命令來設(shè)置字符集編碼。

另外，如果你希望永久地將數(shù)據(jù)庫的字符集編碼設(shè)置為 utf8mb4，你需要修改數(shù)據(jù)庫的默認(rèn)字符集配置。這樣，在新建的數(shù)據(jù)庫連接中不需要再手動(dòng)執(zhí)行 "SET NAMES utf8mb4" 命令，因?yàn)檫B接會(huì)默認(rèn)使用數(shù)據(jù)庫的字符集配置。請注意，在修改數(shù)據(jù)庫的默認(rèn)字符集之前，請確保備份數(shù)據(jù)和相應(yīng)的預(yù)防措施。

結(jié)論

可能是：數(shù)據(jù)庫是utf8，即便我在連接時(shí)指定 --default-character-set=utf8mb4 ，依然會(huì)用utf8，必須要通過 SET NAMES utf8mb4 再設(shè)置一次

為了驗(yàn)證，通過 mysql --default-character-set=utf8mb4 -u root -h xxx.xxx.xx.xx -p密碼 連接mysql，select 剛才更新成功的那條記錄，發(fā)現(xiàn)emoj無法正常顯示； 執(zhí)行 SET NAMES utf8mb4 后，再select能正?？吹絜moj

設(shè)置mysql數(shù)據(jù)庫的字符集和編碼設(shè)置（謹(jǐn)慎操作，一般不要亂改）

要設(shè)置 MySQL 數(shù)據(jù)庫的字符集和編碼設(shè)置，你可以采取以下步驟：

1. 登錄到 MySQL 數(shù)據(jù)庫服務(wù)器，可以使用以下命令： mysql -u your_username -p 替換 your_username 為你的數(shù)據(jù)庫用戶名。

在登錄后，可以執(zhí)行以下命令來查看當(dāng)前的字符集和編碼設(shè)置：

SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';

這將顯示當(dāng)前的字符集和編碼設(shè)置。

1. 若要修改數(shù)據(jù)庫的字符集和編碼設(shè)置，可以執(zhí)行以下命令（以utf8mb4為例）：

   ALTER DATABASE your_database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

   替換 your_database_name 為你要修改的數(shù)據(jù)庫名稱。

如果需要修改特定表的字符集和編碼設(shè)置，可以執(zhí)行以下命令（以utf8mb4為例）：

ALTER TABLE your_table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

替換 your_table_name 為你要修改的表名稱。

如果你希望新創(chuàng)建的表默認(rèn)采用特定的字符集和編碼，可以在創(chuàng)建表時(shí)指定：

CREATE TABLE your_table_name (
  ...
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

替換 your_table_name 為你要?jiǎng)?chuàng)建的表名稱，并根據(jù)需要修改其他表的列和選項(xiàng)。

1. 重啟 MySQL 服務(wù)以使更改生效，具體的重啟方式取決于你的操作系統(tǒng)和安裝方式。

請注意，修改字符集和編碼設(shè)置可能會(huì)影響現(xiàn)有數(shù)據(jù)和應(yīng)用程序，因此在進(jìn)行任何更改之前，請確保備份數(shù)據(jù)庫并謹(jǐn)慎操作。

如何 修改數(shù)據(jù)庫的默認(rèn)字符集配置

要修改數(shù)據(jù)庫的默認(rèn)字符集配置，你需要執(zhí)行以下步驟：

1. 登錄到你的MySQL數(shù)據(jù)庫服務(wù)器。
2. 執(zhí)行以下命令來打開MySQL的配置文件（通常是my.cnf或my.ini）： sudo nano /etc/mysql/my.cnf 如果你使用的是Windows系統(tǒng)，則路徑可能是 C:\ProgramData\MySQL\MySQL Server X.X\my.ini ，其中X.X代表你的MySQL版本號。
3. 在配置文件中找到 [mysqld] 部分。
4. 添加或修改以下兩行來設(shè)置默認(rèn)字符集編碼為 utf8mb4： [mysqld] character-set-server=utf8mb4 collation-server=utf8mb4_unicode_ci 上述配置將字符集設(shè)置為 utf8mb4，同時(shí)使用了 utf8mb4_unicode_ci 校對規(guī)則。你也可以選擇其他適合你的校對規(guī)則。
5. 保存并關(guān)閉配置文件。
6. 重新啟動(dòng)MySQL服務(wù)以使配置生效，可以使用適合你的操作系統(tǒng)的命令，例如：
   • 在Linux上使用： sudo systemctl restart mysql
   • 在Windows上使用： net stop MySQL net start MySQL
7. 現(xiàn)在，新建的數(shù)據(jù)庫連接將默認(rèn)使用 utf8mb4 字符集編碼。

請注意，修改數(shù)據(jù)庫的默認(rèn)字符集可能會(huì)對現(xiàn)有的數(shù)據(jù)庫和數(shù)據(jù)產(chǎn)生影響。在執(zhí)行這些步驟之前，請確保備份數(shù)據(jù)并采取相應(yīng)的預(yù)防措施。

以上就是詳解MySQL中的字符集和排序規(guī)則的詳細(xì)內(nèi)容，更多關(guān)于MySQL字符集和排序規(guī)則的資料請關(guān)注腳本之家其它相關(guān)文章！