首頁  >  文章  >  資料庫  >  MySQL中儲存的資料查詢的時候怎麼區分大小寫

MySQL中儲存的資料查詢的時候怎麼區分大小寫

王林
王林轉載
2023-05-31 16:26:313440瀏覽

    場景描述

    今天在將Hive 表同步到MySQL 之後,其中有一列是唯一列,但在MySQL 中查詢的時候 countdistinct count 查詢出來的數值是不一樣的,這麼來看的話是有重複的資料(按理說不應該的,因為在Hive 中,這兩個數值是一樣的),那麼將重複的資料查出來看了一下,發現是大小寫的問題,然後查了一下,發現MySQL 資料庫預設情況下,字串欄位的所有相關運算是大小寫"不敏感"的。

    這一點與其它流行的資料庫都不相同。

    解決方法

    1. 查詢時指定大小寫敏感

    MySQL 允許在查詢的時候指定以大小寫敏感方式,需要使用關鍵字BINARY,查詢如下:

    SELECT * FROM student WHERE BINARY name = 'ZhangSan';
    --或者
    SELECT * FROM student WHERE name = BINARY 'ZhangSan';

    很多時候當發現MySQL 資料庫有上述問題時,系統已經運行了一段時間,如果採用方法二或方法三的代價可能會很大。

    使用此方法最大的好處便是可以快速實現功能。

    但是這個方法也存在很大的限制:如此可能因為無法使用索引而導致查詢效能下降。

    原因很好理解,因為此時針對查詢欄位的索引也是按照大小寫不敏感方式建立的。

    除非資料量不大,或是在你的應用程式中不在乎這點效能上的損失,那麼只能選擇方法二或方法三了。

    2. 定義表格結構時指定欄位大小寫敏感

    在建立表格時指定特定的欄位大小寫敏感,範例如下:

    CREATE TABLE student (
      ...
      name VARCHAR(64) BINARY NOT NULL,
      ...
    
    )

    關鍵字 BINARY 指定name 欄位大小寫敏感。

    如此在查詢時就算不使用 BINARY 關鍵字,查詢語句也是大小寫敏感的。

    在此基礎上建立的 name 相關的索引也是大小寫敏感的,也就能夠使用索引來提高效能。

    MySQL 允許在大多數字串類型上使用 BINARY 關鍵字,用於指明所有針對該欄位的運算是大小寫敏感的,更多資訊請參閱 MySQL 官方文件。

    這種方法使得設計者可以精確地控制每個欄位是否大小寫敏感。在許多系統的設計中,通常期望所有欄位都是大小寫敏感的,甚至大多數欄位都是如此。 MySQL 也提供了解決方案,這就要用到方法三。

    3. 修改排序規則(COLLATE)

    在MySQL 中執行show create table <tablename></tablename> 指令,可以看到一張表格的建表語句, example 如下:

    CREATE TABLE `table1` (
        `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
        `field1` text COLLATE utf8_general_ci NOT NULL COMMENT &#39;字段1&#39;,
        `field2` varchar(128) COLLATE utf8_general_ci NOT NULL DEFAULT &#39;&#39; COMMENT &#39;字段2&#39;,
        PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8_unicode_ci;

    大部分欄位我們都能看懂,但今天要看的是COLLATE 關鍵字。這個值後面對應的 utf8_general_ci 是什麼意思呢?下面我們就來了解一下。

    COLLATE是用來做什麼的?

    使用Navicat 開發的可能會比較眼熟,因為其中的選項中已經給出了答案:

    MySQL中儲存的資料查詢的時候怎麼區分大小寫

    所謂utf8_general_ci,其實是用來排序的規則。對於 MySQL 中那些字元類型的列,如VARCHAR,CHAR,TEXT 類型的列,都需要有一個 COLLATE 類型來告知 MySQL 如何對該列進行排序和比較。簡言之,COLLATE 會影響ORDER BY 語句的順序,會影響WHERE 條件中大於小於號篩選出來的結果,會影響DISTINCTGROUP BYHAVING 語句的查詢結果。另外,MySQL 建立索引的時候,如果索引列是字元類型,也會影響索引建立,只不過這種影響我們感知不到。總之,凡是涉及字元型別比較或排序的地方,都會和 COLLATE 有關

    涉及字串的各種運算其核心必然涉及到採用何種字元排序規則(COLLATE,也有翻譯為"核對")。 MySQL的字串運算是否區分大小寫,本質上取決於它所使用的COLLATE排序規則。

    utf8_general_ci 是一個具體的 COLLATE 取值。每個特定的 COLLATE 都對應唯一的字元集,可以看出該 COLLATE 對應字元集為 utf8。而與大小寫敏感問題相關的是其後綴 _ci,MySQL 官方文件對其的解釋是 Case Ignore 的縮寫,即大小寫不敏感。由於 MySQL 將 utf8_general_ci 指定為字元集 utf8 的預設 COLLATE,這也導致文章開頭所說的現象。同時,MySQL 也提供了其它的 COLLATE 取值選項,utf8_bin 是大小寫敏感的。事實上所有大小寫敏感的COLLATE 都以_bin_cs 為後綴,前者是Binary 的縮寫,後者是Case Sensitive 的縮寫。

    各種COLLATE的區別

    COLLATE 通常是和資料編碼(CHARSET)相關的,一般來說每種CHARSET 都有多種它所支援的COLLATE,並且每種CHARSET 都指定一種COLLATE 為預設值。例如 Latin1 編碼的預設 COLLATE 為 latin1_swedish_ci,GBK 編碼的預設 COLLATE 為 gbk_chinese_ci,utf8mb4 編碼的預設值為 utf8mb4_general_ci

    這裡順便講個題外話,MySQL 中有 utf8 和 utf8mb4 兩種編碼,在 MySQL 中請大家忘記 utf8,永遠使用 utf8mb4。這是 MySQL 的一個遺留問題,MySQL 中的 utf8 最多只能支援 3bytes 長度的字元編碼,對於一些需要佔據 4bytes 的文字,MySQL 的 utf8 就不支援了,要使用 utf8mb4 才行。

    很多COLLATE 都帶有_ci 字樣,這是Case Insensitive 的縮寫,即大小寫無關,也就是說 "A""a" 在排序和比較的時候是一視同仁的。 selection * from table1 where field1="a" 同樣可以把 field1 為 "A" 的值選出來。同時,對於那些 _cs 後綴的 COLLATE,則是 Case Sensitive,即大小寫敏感的。

    在 MySQL 中使用 show collat​​ion 指令可以檢視到 MySQL 所支援的所有 COLLATE。以 utf8mb4 為例,該編碼所支援的所有 COLLATE 如下圖所示。

    MySQL中儲存的資料查詢的時候怎麼區分大小寫

    圖中我們能看到很多國家的語言自己的排序規則。在國內比較常用的是 utf8mb4_general_ci(預設)、utf8mb4_unicode_ciutf8mb4_bin 這三個。讓我們來探究這三個的差異:

    UTF8mb4_bin的比較方式是將所有字元作為二進位字串,然後從最高位元到最低位元進行比較。所以很顯然它是區分大小寫的。

    而 utf8mb4_unicode_ci 和 utf8mb4_general_ci 對中文和英文來說,其實是沒有任何差別的。對於我們開發的國內所使用的系統來說,隨便選哪個都行。只是對於某些西方國家的字母來說,utf8mb4_unicode_ci 會比 utf8mb4_general_ci 更符合他們的語言習慣一些,general 是 MySQL 一個比較老的標準了。例如,德文字母"ß",在utf8mb4_unicode_ci 中是等價於"ss" 兩個字母的(這是符合德國人習慣的做法),而在utf8mb4_general_ci 中,它卻和字母"s" 等價。不過,這兩種編碼的那些微小的區別,對於正常的開發來說,很難感知。本身我們也很少直接用文字字段去排序,退一步說,即使這個字母排錯了一兩個,真的能給系統帶來災難性後果麼?從網路上找的各種貼文討論來說,更多人推薦使用 utf8mb4_unicode_ci,但是對於使用了預設值的系統,也並沒有非常排斥,並不認為有什麼大問題。結論:建議使用 utf8mb4_unicode_ci,對於已經用了 utf8mb4_general_ci 的系統,也沒有必要花時間改造。

    另外要注意的一點是,從 MySQL 8.0 開始,MySQL 預設的 CHARSET 已經不再是 Latin1 了,改為 utf8mb4 (參考連結),並且預設的 COLLATE 也改為了 utf8mb4_0900_ai_ci。 utf8mb4_0900_ai_ci 大體上就是unicode 的進一步細分,0900 指涉unicode 比較演算法的編號( Unicode Collat​​ion Algorithm version),ai 表示accent insensitive(發音無關),例如e,è, é, ê 和&euegrave;的。相關參考連結1,相關參考連結2

    COLLATE 設定層級及其優先權

    MySQL 資料庫允許在表格 與 列 三個層級上指定Collat​​ion。同時指定時,優先關係是:列 > 表 > 庫。

    設定COLLATE 可以在實例層級庫層級表格層級列層級、以及 SQL 指定。同時指定時,優先關係是:SQL 指定 > 列 > 表 > 庫 > 實例層級。

    • 實例層級的 COLLATE 設定就是 MySQL 設定檔或啟動指令中的 collat​​ion_connection 系統變數。

    • 庫層級設定 COLLATE 的語句如下:

    CREATE DATABASE <db_name> DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

    如果库级别没有设置 CHARSET 和 COLLATE,则库级别默认的 CHARSET 和 COLLATE 使用实例级别的设置。在 MySQL 8.0 以下版本中,你如果什么都不修改,默认的 CHARSET 是 Latin1,默认的 COLLATE 是 latin1_swedish_ci。从 MySQL 8.0 开始,默认的 CHARSET 已经改为了 utf8mb4,默认的 COLLATE 改为了 utf8mb4_0900_ai_ci。

    • 表级别的 COLLATE 设置,则是在 CREATE TABLE 的时候加上相关设置语句,例如:

    CREATE TABLE table_name (
    ……
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT = &#39;表注释&#39;;

    如果表级别没有设置 CHARSET 和 COLLATE,则表级别会继承库级别的 CHARSET 与 COLLATE。

    • 列级别的设置,则在 CREATE TABLE 中声明列的时候指定,例如

    CREATE TABLE (
    `field1` VARCHAR(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT &#39;&#39; COMMENT &#39;字段1&#39;,
    ……
    ) ……

    如果列级别没有设置 CHARSET 和 COLATE,则列级别会继承表级别的 CHARSET 与 COLLATE。

    • 最后,你也可以在写 SQL 查询的时候显示声明 COLLATE 来覆盖任何库表列的 COLLATE 设置,不太常用,了解即可:

    SELECT DISTINCT field1 COLLATE utf8mb4_general_ci FROM table1;
    
    SELECT field1, field2 FROM table1 ORDER BY field1 COLLATE utf8mb4_unicode_ci;

    如果全都显示设置了,那么优先级顺序是 SQL 语句 > 列级别设置 > 表级别设置 > 库级别设置 > 实例级别设置。

    也就是说列上所指定的 COLLATE可以覆盖表上指定的 COLLATE,表上指定的 COLLATE 可以覆盖库级别的 COLLATE。如果没有指定,则继承下一级的设置。

    即列上面没有指定 COLLATE,则该列的 COLLATE 和表上设置的一样。

    以上是MySQL中儲存的資料查詢的時候怎麼區分大小寫的詳細內容。更多資訊請關注PHP中文網其他相關文章!

    陳述:
    本文轉載於:yisu.com。如有侵權,請聯絡admin@php.cn刪除