MySQL 欄位內容區分大小寫

MySQL 欄位內容區分大小寫

資料由Oracle 遷入MySQL ，由於之前Oracle區分大小寫，MySQL的配置使用了預設配置，導致一些資料匯入失敗，有的唯一鍵報錯，衝突。

將測試過程記錄在下面。

資料庫版本：MySQL 5.7.11

校對規則一般有這些特徵：

• 兩個不同的字元集不能有相同的校對規則。
• 每個字元集有一個預設校對規則。例如，utf8預設校對規則是utf8_general_ci。
• 存在校對規則命名規範：它們以其相關的字元集名開始，通常包括一個語言名，並且以_ci（大小寫不敏感）、_cs（大小寫敏感）或_bin（二元）結束。

查看支援的校正規則：

mysql> SHOW COLLATION  like  'utf8%' ; + --------------------------+---------+-----+---------+----------+---------+ | Collation                | Charset | Id  |  Default  | Compiled | Sortlen | + --------------------------+---------+-----+---------+----------+---------+ | utf8_general_ci          | utf8    |  33 | Yes     | Yes      |       1 | | utf8_bin                 | utf8    |  83 |         | Yes      |       1 | | utf8_unicode_ci          | utf8    | 192 |         | Yes      |       8 | ... | utf8mb4_general_ci       | utf8mb4 |  45 | Yes     | Yes      |       1 | | utf8mb4_bin              | utf8mb4 |  46 |         | Yes      |       1 | | utf8mb4_unicode_ci       | utf8mb4 | 224 |         | Yes      |       8 | | utf8mb4_icelandic_ci     | utf8mb4 | 225 |         | Yes      |       8 |

查看本地的校正規則：

mysql> show  global  variables  like  '%coll%' ; + ----------------------+--------------------+ | Variable_name        | Value              | + ----------------------+--------------------+ | collation_connection | utf8mb4_unicode_ci | | collation_database   | utf8mb4_unicode_ci | | collation_server     | utf8mb4_unicode_ci | + ----------------------+--------------------+

生產中資料庫使用的編碼為utf8mb4, 校正規則為 utf8mb4_unicode_ci，對大小寫不敏感

如果需要大小寫敏感，需要將定序修改為utf8mb4_bin.

測試後結果：修改資料庫配置後，不會對已經存在的表造成影響，如要生效需要修改特定列的定序。優先順序大概是這樣：列>表>資料庫>伺服器

有兩種方法使查詢區分大小寫：

第一種方法為修改列層級的校正規則為utf8mb4_bin

T表

CREATE  TABLE  `T` (   ` name `  varchar (20)  COLLATE  utf8mb4_unicode_ci  DEFAULT  NULL ) ENGINE=InnoDB  DEFAULT  CHARSET=utf8mb4  COLLATE =utf8mb4_unicode_ci mysql>  select  *  from  T; + ------+ |  name  | + ------+ | YOU  | | You  | | you  | | you  | | yOU  | + ------+

T2表：將列校對規則修改為utf8mb4_bin

CREATE  TABLE  `T2` (   ` name `  varchar (20)  CHARACTER  SET  utf8mb4  COLLATE  utf8mb4_bin  DEFAULT  NULL ) ENGINE=InnoDB  DEFAULT  CHARSET=utf8mb4  COLLATE =utf8mb4_unicode_ci mysql>  select  *  from  T2; + ------+ |  name  | + ------+ | yOU  | | you  | + ------+

查詢：

T：（未區分大小寫）

mysql>  select  *  from  T  where  name  =  'you' ; + ------+ |  name  | + ------+ | YOU  | | You  | | you  | | you  | | yOU  | + ------+

T2:（已經區分大小寫）

mysql>  select  *  from  T2  where  name  =  'you' ; + ------+ |  name  | + ------+ | you  | + ------+

第二種方法：  不修改配置，表結構，而使用如下的查詢語句：

T：（未修改表）

mysql>  select  *  from  T  where  name  =  binary 'you' ; + ------+ |  name  | + ------+ | you  | | you  | + ------+