本文關(guān)鍵字：字符集、建庫建表一、數(shù)據(jù)庫和字符集1. 建庫時指定創(chuàng)建數(shù)據(jù)庫時，顯式指定字符集和排序規(guī)則，同時，當(dāng)切換到當(dāng)前數(shù)據(jù)庫后，參數(shù) character_set_database，collation_database 分別被覆蓋為當(dāng)前顯式指定的字符集和排序規(guī)則。舉個簡單例子，創(chuàng)建數(shù)據(jù)庫 ytt_new2，顯式指定字符集為 latin1，同時排序規(guī)則為 latin1_bin。之后在切換到數(shù)據(jù)庫 ytt_new2 后，對應(yīng)的系統(tǒng)參數(shù)也被修改。mysql>?create?database?ytt_new2?default?character?set?latin1?collate?latin1_bin;Query?OK,?1?row?affected?(0.03?sec)mysql>?use?ytt_new2Database?changedmysql>?select?@@character_set_database,??@@collation_database;+--------------------------+----------------------+|?@@character_set_database?|?@@collation_database?|+--------------------------+----------------------+|?latin1???????????????????|?latin1_bin???????????|+--------------------------+----------------------+1 row in set (0.00 sec)2. 改庫時指定改庫類似建庫，效果一樣。但需要注意的一點是，修改庫字符集與排序規(guī)則后，之前基于這個庫創(chuàng)建的各種對象，還是沿用老的字符集與排序規(guī)則。

舉個例子，對存儲過程的影響：

--?簡單寫個存儲過程DELIMITER $$USE `ytt_new2`$$DROP PROCEDURE IF EXISTS `sp_demo`$$CREATE?DEFINER=`root`@`%`?PROCEDURE?`sp_demo`(????IN?f1?VARCHAR(10),????IN?f2?VARCHAR(10)????)BEGIN????DECLARE?v1?VARCHAR(20);????SET?v1?=?CONCAT(f1,f2);????SELECT?v1?AS?result; END$$DELIMITER ;查看這個存儲過程的字符集，這里看到排序規(guī)則是 latin1_bin，對應(yīng)的字符集是 latin1，和數(shù)據(jù)庫 ytt_new2 一致。mysql>?show?create?procedure?sp_demo\G***************************?1.?row?***************************???????????Procedure:?sp_demo...??Database?Collation:?latin1_bin1 row in set (0.00 sec)--?那接下來改掉數(shù)據(jù)庫的字符集為?UTF8mysql>?alter?database?ytt_new2?character?set?utf8?collate?utf8_general_ci;Query OK, 1 row affected, 2 warning (0.02 sec)mysql>?select?@@character_set_database,??@@collation_database;+--------------------------+----------------------+|?@@character_set_database?|?@@collation_database?|+--------------------------+----------------------+|?utf8?????????????????????|?utf8_general_ci??????|+--------------------------+----------------------+1?row?in?set?(0.00?sec)--?再查看存儲過程?sp_demo?的字符集，還是之前的。mysql>?show?create?procedure?sp_demo\G***************************?1.?row?***************************???????????Procedure:?sp_demo...??Database?Collation:?latin1_bin1 row in set (0.00 sec)-- 此時，調(diào)用存儲過程，字符集不對，報編碼錯誤。mysql>?call?sp_demo('我','你');ERROR?1366?(HY000):?Incorrect?string?value:?'\xE6\x88\x91'?for?column?'f1'?at?row?1--?改變存儲過程字符集只能刪除重建，重新執(zhí)行下之前的存儲過程代碼，再次調(diào)用，結(jié)果就正常了。mysql>?call?sp_demo('我','你');+--------+|?result?|+--------+|?我你???|+--------+1 row in set (0.00 sec)Query OK, 0 rows affected, 2 warnings (0.01 sec)

3. 參數(shù)的指定

參數(shù) character_set_database 和 collation_database 如果沒有指定，默認(rèn)繼承服務(wù)器端對應(yīng)參數(shù) character_set_server 和 collation_server。mysql>?select?@@character_set_server?charset,??@@collation_server?collation????->?union?all????->?select?@@character_set_database,??@@collation_database;+---------+--------------------+|?charset?|?collation??????????|+---------+--------------------+|?utf8mb4?|?utf8mb4_0900_ai_ci?||?utf8mb4?|?utf8mb4_0900_ai_ci?|+---------+--------------------+2 rows in set (0.00 sec)那這種情況下，建庫或者改庫時不指定具體的字符集和排序規(guī)則，默認(rèn)繼承這兩個參數(shù)：mysql>?show?create?database?ytt_new3\G***************************?1.?row?***************************???????Database:?ytt_new3Create?Database:?CREATE?DATABASE?`ytt_new3`?/*!40100?DEFAULT?CHARACTER?SET?utf8mb4?COLLATE?utf8mb4_0900_ai_ci?*/?/*!80016?DEFAULT?ENCRYPTION='N'?*/1 row in set (0.00 sec)二、表和字符集1. 建表時指定(顯式設(shè)置)同建庫一樣，顯式指定字符集和排序規(guī)則，優(yōu)先級最高，以指定的值為準(zhǔn)。--?創(chuàng)建新庫?ytt_new4mysql>?create?database?ytt_new4;Query?OK,?1?row?affected?(0.02?sec)mysql>?use?ytt_new4;Database changed--?創(chuàng)建新表?t1,?字符集?latin1,?排序規(guī)則?latin1_binmysql>?create?table?t1(a1?int)?charset?latin1?collate?latin1_bin;Query OK, 0 rows affected (0.05 sec)mysql>?show?create?table?t1\G***************************?1.?row?***************************???????Table:?t1Create?Table:?CREATE?TABLE?`t1`?(??`a1`?int?DEFAULT?NULL)?ENGINE=InnoDB?DEFAULT?CHARSET=latin1?COLLATE=latin1_bin1 row in set (0.00 sec)

2. 繼承設(shè)置(隱式轉(zhuǎn)換)

默認(rèn)繼承所屬數(shù)據(jù)庫級別的字符集和排序規(guī)則。這里需要注意的是所屬數(shù)據(jù)庫，不是當(dāng)前數(shù)據(jù)庫。--?當(dāng)前數(shù)據(jù)庫?ytt_new4.mysql>?use?ytt_new4;Database?changed--?表?t2?屬于當(dāng)前數(shù)據(jù)庫?ytt_new4mysql>?create?table?t2(a1?int);Query OK, 0 rows affected (0.05 sec)--?查看表?t2?字符集和排序規(guī)則mysql>?show?create?table?t2\G***************************?1.?row?***************************???????Table:?t2Create?Table:?CREATE?TABLE?`t2`?(??`a1`?int?DEFAULT?NULL)?ENGINE=InnoDB?DEFAULT?CHARSET=utf8mb4?COLLATE=utf8mb4_0900_ai_ci1 row in set (0.00 sec)--?創(chuàng)建表?t3，不過屬于數(shù)據(jù)庫?ytt_new5mysql>?create?database?ytt_new5?character?set?gbk;Query?OK,?1?row?affected?(0.03?sec)mysql>?create?table?ytt_new5.t3?(id?int);Query OK, 0 rows affected (0.04 sec)--?查看表?t3?字符集，和數(shù)據(jù)庫?ytt_new5?一致mysql>?show?create?table?ytt_new5.t3\G***************************?1.?row?***************************???????Table:?t3Create?Table:?CREATE?TABLE?`t3`?(??`id`?int?DEFAULT?NULL)?ENGINE=InnoDB?DEFAULT?CHARSET=gbk1 row in set (0.01 sec)

3. 視圖

視圖其實就是虛擬的表，所以視圖的字符集也劃在表這塊簡單介紹下。視圖的字符集完全依賴客戶端的字符集設(shè)置。比如：mysql>?set?names?gbk;Query?OK,?0?rows?affected?(0.00?sec)--?此時視圖?v_t3?編碼為?gbkmysql>?create?view?v_t3?as?select?*?from?t3;Query?OK,?0?rows?affected?(0.01?sec)mysql>?set?names?gb18030;Query OK, 0 rows affected (0.00 sec)--?此時視圖?v_t31?編碼為?gb18030mysql>?create?view?v_t3_1?as?select?*?from?t3;Query OK, 0 rows affected (0.01 sec)--?查看這兩個視圖的編碼mysql>?show?create?view?v_t3\G***************************?1.?row?***************************????????????????View:?v_t3?????????Create?View:?CREATE?ALGORITHM=UNDEFINED?DEFINER=`root`@`localhost`?SQL?SECURITY?DEFINER?VIEW?`v_t3`?AS?select?`t3`.`a1`?AS?`a1`?from?`t3`character_set_client:?gbkcollation_connection:?gbk_chinese_ci1 row in set (0.01 sec)mysql>?show?create?view?v_t3_1\G***************************?1.?row?***************************????????????????View:?v_t3_1?????????Create?View:?CREATE?ALGORITHM=UNDEFINED?DEFINER=`root`@`localhost`?SQL?SECURITY?DEFINER?VIEW?`v_t3_1`?AS?select?`t3`.`a1`?AS?`a1`?from?`t3`character_set_client:?gb18030collation_connection:?gb18030_chinese_ci1 row in set (0.00 sec)

4. 觸發(fā)器

觸發(fā)器基于表，所以觸發(fā)器也歸類到表這塊。其實觸發(fā)器的編碼規(guī)則和視圖一樣。也是依賴客戶端的設(shè)定。比如一個簡單的觸發(fā)器：--?客戶端編碼為?utf8mysql>?set?names?utf8;Query OK, 0 rows affected, 1 warning (0.00 sec)--?觸發(fā)器的編碼也繼承同樣的客戶端編碼mysql>?create?trigger?tr_after_insert_t3?after?insert?on?t3?for?each?row??insert?into?t4?values(new.a1);Query OK, 0 rows affected (0.01 sec)mysql>?show?create?trigger?tr_after_insert_t3\G***************************?1.?row?***************************???????????????Trigger:?tr_after_insert_t3??????????????sql_mode:SQL?Original?Statement:?CREATE?DEFINER=`root`@`localhost`?TRIGGER?`tr_after_insert_t3`?AFTER?INSERT?ON?`t3`?FOR?EACH?ROW?insert?into?t4?values(new.a1)??character_set_client:?utf8??collation_connection:?utf8_general_ci????Database?Collation:?utf8mb4_0900_ai_ci???????????????Created:?2020-03-09?11:32:23.941 row in set (0.00 sec)三、列和字符集1. 顯式指定同數(shù)據(jù)庫和表一樣，列也可以顯式指定特定的字符集和排序規(guī)則。雖說是可以這樣做，但是非常不推薦，最主要原因是每個列字符集不一樣，導(dǎo)致寫入和檢索都得額外的編寫 SQL。--?創(chuàng)建新庫?ytt_new6mysql>?create?database?ytt_new6?character?set?latin1;Query?OK,?1?row?affected?(0.02?sec)mysql>?use?ytt_new6;Database changed-- 在 ytt_new6 下創(chuàng)建表 t1，擁有字段 a1,a2,a3 分別給定不同的字符集和排序規(guī)則mysql>?create?table?t1(?a1?char(10)?charset?latin1?collate?latin1_bin,???????????????????????a2?char(10)?charset?gbk?collate?gbk_bin,???????????????????????a3?char(10)?charset?utf8mb4?collate?utf8mb4_bin);Query?OK,?0?rows?affected?(0.05?sec)mysql>?show?create?table?t1\G***************************?1.?row?***************************???????Table:?t1Create?Table:?CREATE?TABLE?`t1`?(??`a1`?char(10)?CHARACTER?SET?latin1?COLLATE?latin1_bin?DEFAULT?NULL,??`a2`?char(10)?CHARACTER?SET?gbk?COLLATE?gbk_bin?DEFAULT?NULL,??`a3`?char(10)?CHARACTER?SET?utf8mb4?COLLATE?utf8mb4_bin?DEFAULT?NULL)?ENGINE=InnoDB?DEFAULT?CHARSET=latin11 row in set (0.00 sec)那接下來簡單插入一條記錄，需要為每列添加 introduer，否則報錯。--?沒有顯式指定每列字符集，報錯mysql>?insert?into?t1?values?('character?','字符集合','字符集合');ERROR 1366 (HY000): Incorrect string value: '\xAC\xA6\xE9\x9B\x86\xE5...' for column 'a2' at row 1--?為每列顯式添加?Introducermysql>?insert?into?t1?values?(_latin1?'character?',_gbk?'字符集合',_utf8mb4?'字符集合');Query OK, 1 row affected (0.01 sec)查詢出來最麻煩，因為每次查詢出來的結(jié)果只能有一個字符集，字符集兼容的列可以一起檢索；不兼容的列得分開檢索。舉個例子，我想簡單的 SELECT *拿出所有記錄，結(jié)果發(fā)現(xiàn)有一個字段據(jù)顯示不正常。mysql>?select?*?from?t1;+-----------+------------------+--------------+|?a1????????|?a2???????????????|?a3???????????|+-----------+------------------+--------------+|?character?|?瀛楃?闆嗗悎??????|?字符集合?????|+-----------+------------------+--------------+1 row in set (0.00 sec)所以針對這種情形，該怎么檢索數(shù)據(jù)呢？必須得對單個字段檢索或者是對兼容的列一起檢索。--?以字符集?GBK?輸出列?a1?a2，由于?a1?是保存的是字母，所以兼容輸出。mysql>?set?names?gbk;Query OK, 0 rows affected (0.00 sec)mysql>?select?a1,a2?from?t1;+-----------+--------------+|?a1????????|?a2???????????|+-----------+--------------+|?character?|?字符集合?????|+-----------+--------------+1?row?in?set?(0.00?sec)--?以字符集utf8mb4和gbk不兼容，得單獨輸出列a3。或者單獨輸出a2.mysql>?set?names?utf8mb4;Query OK, 0 rows affected (0.00 sec)mysql>?select?a3?from?t1;+--------------+|?a3???????????|+--------------+|?字符集合?????|+--------------+1 row in set (0.00 sec)

2. 隱式轉(zhuǎn)換

這種方式，是最推薦的，也是最長的方式，所有列繼承表的字符集，不單獨指定。--?建表?t2，指定字符集為?gbk.mysql>?create?table?t2(a1?varchar(10),a2?varchar(10))?charset?gbk;Query OK, 0 rows affected (0.05 sec)mysql>?show?create?table?t2\G***************************?1.?row?***************************???????Table:?t2Create?Table:?CREATE?TABLE?`t2`?(??`a1`?varchar(10)?DEFAULT?NULL,??`a2`?varchar(10)?DEFAULT?NULL)?ENGINE=InnoDB?DEFAULT?CHARSET=gbk1 row in set (0.01 sec)但是這里有一個需要注意的點，如果此時對表進(jìn)行字符集變更，那表的列依然保留原來的字符集。例如：mysql>?alter?table?t2?charset?utf8;Query?OK,?0?rows?affected,?1?warning?(0.01?sec)Records:?0??Duplicates:?0??Warnings:?1mysql>?show?create?table?t2\G***************************?1.?row?***************************???????Table:?t2Create?Table:?CREATE?TABLE?`t2`?(??`a1`?varchar(10)?CHARACTER?SET?gbk?DEFAULT?NULL,??`a2`?varchar(10)?CHARACTER?SET?gbk?DEFAULT?NULL)?ENGINE=InnoDB?DEFAULT?CHARSET=utf81 row in set (0.00 sec)如果想把表里字段的字符集也改了，應(yīng)該用以下語句更改表字符集。mysql>?alter?table?t2?convert?to?character?set?utf8;Query?OK,?0?rows?affected,?1?warning?(0.09?sec)Records: 0 Duplicates: 0 Warnings: 1mysql>?show?create?table?t2\G***************************?1.?row?***************************???????Table:?t2Create?Table:?CREATE?TABLE?`t2`?(??`a1`?varchar(10)?DEFAULT?NULL,??`a2`?varchar(10)?DEFAULT?NULL)?ENGINE=InnoDB?DEFAULT?CHARSET=utf81 row in set (0.00 sec)那到這兒，我們已經(jīng)了解了字符集對數(shù)據(jù)庫，表以及列的使用以及相關(guān)影響。大致總結(jié)下，這篇我詳細(xì)介紹了字符集在 MySQL 數(shù)據(jù)庫，表以及列相關(guān)對象處理時的注意事項，并且舉例說明。希望對大家有幫助。

---

關(guān)于 MySQL 的技術(shù)內(nèi)容，你們還有什么想知道的嗎？趕緊留言告訴小編吧！

想看更多技術(shù)好文，點個“在看”吧！

總結(jié)

以上是生活随笔為你收集整理的mysql 不排序_第08期：有关 MySQL 字符集的注意事项的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。