MySQL field content is case sensitive

MySQL field content is case sensitive

Data is migrated from Oracle to MySQL. Since Oracle is case-sensitive in the past, the default configuration is used for MySQL configuration, resulting in some data import failures, and some unique keys report errors and conflicts.

Record the test procedure below.

Database Version: MySQL 5.7.11

Proofreading rules generally have these features:

• Two different character sets cannot have the same verification rules.
• Each character set has a default verification rule. For example, the default verification rule for utf8 is utf8_general_ci.
• There is a naming convention for proofreading rules: they start with their relevant Character Set names, usually include a language name, and are case-insensitive (_ ci), _ cs (case-sensitive) or _ bin (Binary) ends.

View the supported verification rules:

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 |

View local verification rules:

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

The encoding used by the database in production is utf8mb4, and the validation rule is utf8mb4_unicode_ci, Which is case insensitive.

To be case sensitive, change the sorting rule to utf8mb4_bin.

Test Results: After the database configuration is modified, the existing tables are not affected. to take effect, you need to modify the sorting rules for specific columns. The priority is probably like this: column> table> database> Server

There are two ways to make the query case sensitive:

The first method is to modify the column-level validation rule to utf8mb4_bin.

T table

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  | + ------+

Table T2: Modify the column checking rule to 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  | + ------+

Query:

T: (Case Insensitive)

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

T2: (case sensitive)

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

Method 2: Use the following query statement instead of modifying the configuration and table structure:

T: (Tables not modified)

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