關鍵字: 字符集，utf8mb4，emoj

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

問題

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

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;

登陸跳板機，連接遠程數據庫后，執(zhí)行sql，報錯: ERROR 1366 (HY000): Incorrect string value: '\xF0\x9F\x93\xA3Ev...'

\xF0\x9F\x93\xA3恰好是轉義之后的emoj

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

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

此處 這個字段沒有設置字符集，那應該用表的字符集即*DEFAULT CHARSET=utf8mb4 *

(且經過試驗，如果COLLATE=utf8mb4_unicode_ci，那字符集不可能是utf8，只可能是utf8mb4，不然報錯時會直接報錯)

下面補充一些mysql字符集的知識

查看庫級別的 字符集和編碼設置

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 中與字符集相關的變量，用于控制不同環(huán)境中的字符集設置。以下是對每個變量的簡要說明：

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

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

注意：在修改這些字符集相關的變量之前，請確保了解其含義和影響，并在備份數據的情況下謹慎操作。修改字符集設置可能會對現有數據和應用程序產生影響。

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

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

查看表級別的字符集和編碼設置

SHOW CREATE TABLE `your_table_name`;

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

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

SHOW FULL COLUMNS FROM your_table_name;

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

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

Collation 本意是?？?，校對之意，在數據庫中 是排序規(guī)則

這個字段的第一部分，其實已經指明了字符集...所以SHOW FULL COLUMNS沒有必要再多一個字符集列

那么，問題何在呢？

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

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

但還是不行...

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

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

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

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

SET NAMES utf8mb4;

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

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

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

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

SET NAMES utf8mb4什么?

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

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

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

只對當前連接有效嗎？

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

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

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

結論

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

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

設置mysql數據庫的字符集和編碼設置（謹慎操作，一般不要亂改）

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

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

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

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

這將顯示當前的字符集和編碼設置。

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

   ALTER DATABASE your_database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

   替換 your_database_name 為你要修改的數據庫名稱。

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

ALTER TABLE your_table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

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

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

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

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

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

請注意，修改字符集和編碼設置可能會影響現有數據和應用程序，因此在進行任何更改之前，請確保備份數據庫并謹慎操作。

如何 修改數據庫的默認字符集配置

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

1. 登錄到你的MySQL數據庫服務器。
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. 添加或修改以下兩行來設置默認字符集編碼為 utf8mb4： [mysqld] character-set-server=utf8mb4 collation-server=utf8mb4_unicode_ci 上述配置將字符集設置為 utf8mb4，同時使用了 utf8mb4_unicode_ci 校對規(guī)則。你也可以選擇其他適合你的校對規(guī)則。
5. 保存并關閉配置文件。
6. 重新啟動MySQL服務以使配置生效，可以使用適合你的操作系統(tǒng)的命令，例如：
   • 在Linux上使用： sudo systemctl restart mysql
   • 在Windows上使用： net stop MySQL net start MySQL
7. 現在，新建的數據庫連接將默認使用 utf8mb4 字符集編碼。

請注意，修改數據庫的默認字符集可能會對現有的數據庫和數據產生影響。在執(zhí)行這些步驟之前，請確保備份數據并采取相應的預防措施。

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

最新評論