Shulou(Shulou.com)06/01 Report--

The following content mainly brings you the difference between the case and case of the MySQL field. The knowledge mentioned here, which is slightly different from books, is summed up by professional and technical personnel in the process of contact with users, and has a certain value of experience sharing. I hope to bring help to the majority of readers.

The data is moved from Oracle to MySQL. Because the previous Oracle is case-sensitive, the configuration of MySQL uses the default configuration, resulting in some data import failures, some unique key errors and conflicts.

Record the test process below.

Database version: MySQL 5.7.11

Quote from other people's blogs:

Proofreading rules generally have these characteristics:

Two different character sets cannot have the same proofreading rules.

Each character set has a default proofreading rule. For example, the default proofreading rule for utf8 is utf8_general_ci.

There are proofreading rule naming conventions: they start with their associated character set name, usually include a language name, and end with _ ci (case-insensitive), _ cs (case-sensitive), or _ bin (binary).

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

In production, the database uses utf8mb4 code and the verification rule is utf8mb4_unicode_ci, which is insensitive to case.

If you need case sensitivity, you need to change the collation to utf8mb4_bin.

Test results: after modifying the database configuration, it will not affect the existing tables, for example, to take effect, you need to modify the collation of specific columns. The priority is roughly as follows: columns > tables > databases > CVM

There are two ways to make a query case sensitive:

The first method is to change the column-level check rule to utf8mb4_bin

T table

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

Table T2: change the column alignment 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_cimysql > select * from T2bombomachusi + | 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 | +-+

The second method: do not modify the configuration, table structure, but use the following query statement:

T: (table not modified)

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

For the above explanation on distinguishing the case of the MySQL field, if you have more information to know, you can continue to pay attention to the innovation of our industry. If you need professional solutions, you can contact the pre-sale and after-sale ones on the official website. I hope this article can bring you some knowledge updates.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.