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

Let's learn why the fields stored in MySQL are not case-sensitive. I believe you will benefit a lot after reading them. There are not many words in the text. I hope that why the fields stored in MySQL are not case-sensitive is what you want.

00 brief review

I wrote a previous article about mysql's sensitivity to table case. In fact, the contents of fields stored in mysql are not case-sensitive. This article gives a brief summary.

I would like to review:

The MySQL database name, table name, column name and alias case rules under Linux are as follows:

1. Database names and table names are strictly case-sensitive

2. Aliases for tables are strictly case-sensitive

3. Column names and column aliases ignore case in all cases

4. The content of the field is case-insensitive by default.

01 an example

Simple example:

CREATE TABLE `tb_ user` (`id`BIGINT (20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'user id', `username` VARCHAR (50) username', PRIMARY KEY (`id`)) ENGINE = INNODB DEFAULT CHARSET = utf8 COMMENT = 'user table'; INSERT INTO `u2s`.`tb _ user` (`id`, `username`) VALUES ('1users,' user'); INSERT INTO `u2s`.`tb _ user` (`id`, `username`) VALUES ('2users,' User') INSERT INTO `u2s`.`tb _ user` (`id`, `username`) VALUES ('3clients,' USER')

Use the query statement to query the user whose username is all lowercase user, and the result shows that all three records are found.

Mysql > SELECT username from tb_user where username = 'user';+-+ | username | +-+ | user | | User | | USER | +-+ 3 rows in set

This example simply illustrates that the content of the field is case-insensitive by default.

02 solution

* * because the content of the field is case-insensitive by default, that is, it is case-insensitive. * * so the solution is to add verification rules for field content.

Use the BINARY keyword of mysql to make the search case sensitive.

Add the BINARY keyword to the sql of the query

Mysql > select * from tb_user where BINARY username = 'user';+----+-+ | id | username | +-+-+ | 1 | user | +-+-+ 1 row in set

This approach is relatively simple, instead of changing the table structure, you just need to add keywords before the fields that need to distinguish the query. This approach also has its drawbacks. Every time you write a query, you should pay attention to adding keywords, and you may need to change a lot of code.

Restrict when creating a table

CREATE TABLE `user1` (`id` BIGINT (20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'user id', `username` VARCHAR (50) BINARY NOT NULL COMMENT' username', PRIMARY KEY (`id`)) ENGINE = INNODB DEFAULT CHARSET = utf8 COMMENT = 'user table'; mysql > show create table tb_user1 Tb_user1 | CREATE TABLE `user1` (`id` bigint (20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'user id', `username` varchar (50) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT' username', PRIMARY KEY (`id`) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=' user Table'1 row in set

Or use the

CREATE TABLE `user2` (`id` BIGINT (20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'user id', `username` VARCHAR (50) NOT NULL COMMENT' username', `info` VARCHAR (100) NOT NULL COMMENT 'details description', PRIMARY KEY (`id`)) ENGINE = INNODB DEFAULT CHARSET = utf8 COLLATE=utf8_bin COMMENT = 'user table'; mysql > show create table tb_user2 Tb_user2 | CREATE TABLE `user2` (`id` bigint (20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'user id', `username` varchar (50) COLLATE utf8_bin NOT NULL COMMENT' username', `info` varchar (100) COLLATE utf8_bin NOT NULL COMMENT 'details description', PRIMARY KEY (`id`) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT=' user Table'

Using NGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin makes all settings of type varchar in the field case sensitive. In both cases, the details of the table are essentially COLLATE utf8_bin added to the field.

03 summary

The case of field values is controlled by mysql's proofreading rules. When it comes to proofreading rules, you have to say character set. A character set is a set of symbols and codes, and proofreading rules are a set of rules used to compare characters within a character set. In general, a proofreading rule begins with its associated character set name, usually includes a language name, and ends with _ ci (case-insensitive), _ cs (case-sensitive), or _ bin (binary).

For example, the utf8 character set, as shown in the following table:

1) utf8_bin:utf8_bin stores each character in a string in binary data, case-sensitive.

2) utf8_general_ci:utf8_genera_ci is not case-sensitive, and ci is an abbreviation for case insensitive, that is, case-insensitive.

3) utf8_general_cs:utf8_general_cs is case-sensitive, and cs is the abbreviation of case sensitive, that is, case-sensitive.

Note: I do not support the utf8_general_cs character set when I use version 5.7. create an error.

After reading this article on why the fields stored in MySQL are case-insensitive, many readers will want to know more about it. For more industry information, you can follow our industry information section.

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.