Fields stored in MySQL are case-insensitive, did you know?

##00 Brief review

I have written an article before about the case sensitivity of mysql tables. In fact, the content stored in fields in mysql is not case-sensitive. This article will briefly summarize it.

Want to review:

MySQL’s capitalization rules for database names, table names, column names, and aliases under Linux are as follows:

1. Database name and table name It is strictly case-sensitive;

2. Table aliases are strictly case-sensitive;

3. Column names and column aliases are case-insensitive in all cases. ;

4. Field content is case-insensitive by default.

01 An example

Simple example:

CREATE TABLE `tb_user` (
	`id` BIGINT (20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '用户id',
	`username` VARCHAR (50) NOT NULL COMMENT '用户名',
	PRIMARY KEY (`id`)
) ENGINE = INNODB DEFAULT CHARSET = utf8 COMMENT = '用户表';


INSERT INTO `u2s`.`tb_user` (`id`, `username`) VALUES ('1', 'user');
INSERT INTO `u2s`.`tb_user` (`id`, `username`) VALUES ('2', 'User');
INSERT INTO `u2s`.`tb_user` (`id`, `username`) VALUES ('3', 'USER');

Use the query statement to query users whose username is all lowercase

user, and the result is All three records have been queried.

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

As a brief explanation through this example, the field content is case-insensitive by default.

02 Solution

**Because the field content is not case-sensitive by default, that is, it is not case-sensitive. **So the solution is to add verification rules for field content.

Use mysql's

BINARY keyword to make the search case-sensitive.

Add the BINARY keyword in the query sql

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

This method is relatively simple, without changing the table structure, just add the keyword when needed Add keywords before the fields that distinguish the query. This method also has shortcomings. Every time you write a query, you must pay attention to adding keywords, and there may be a lot of code that needs to be changed.

Restrict when creating the table

CREATE TABLE `tb_user1` (
	`id` BIGINT (20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '用户id',
	`username` VARCHAR (50) BINARY NOT NULL COMMENT '用户名',
	PRIMARY KEY (`id`)
) ENGINE = INNODB DEFAULT CHARSET = utf8 COMMENT = '用户表';


mysql> show create table tb_user1;
tb_user1 | CREATE TABLE `tb_user1` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '用户id',
  `username` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '用户名',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户表'
1 row in set

Or use

CREATE TABLE `tb_user2` (
	`id` BIGINT (20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '用户id',
	`username` VARCHAR (50) NOT NULL COMMENT '用户名',
	`info` VARCHAR (100) NOT NULL COMMENT '详情描述',
	PRIMARY KEY (`id`)
) ENGINE = INNODB DEFAULT CHARSET = utf8 COLLATE=utf8_bin COMMENT = '用户表';

mysql> show create table tb_user2;
tb_user2 | CREATE TABLE `tb_user2` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '用户id',
  `username` varchar(50) COLLATE utf8_bin NOT NULL COMMENT '用户名',
  `info` varchar(100) COLLATE utf8_bin NOT NULL COMMENT '详情描述',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='用户表'

Use

NGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_binAll settings of varchar type in the field will be case-sensitive. The details of these two viewing tables are essentially adding COLLATE utf8_bin to the fields.

03 Summary

The case of field values ​​is controlled by the collation rules of mysql. When it comes to proofreading rules, we have to talk about character sets. A character set is a set of symbols and encodings, and a collation rule is a set of rules for comparing characters within a character set. Generally speaking, a collation rule starts with the name of its associated character set, usually including 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 the string as binary data, case-sensitive.

2) utf8_general_ci: utf8_genera_ci is not case sensitive, ci is the abbreviation of case insensitive, that is, case insensitive.

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

Note: The 5.7 version of my machine does not support the utf8_general_cs character set, and an error was reported during creation.

Through the content of the previous article and this article, everyone has a certain understanding of the case sensitivity of MySQL. In actual development, it is best to use lowercase letters for library and table names. Please note that Capitalization problem of field storage content. And make the configuration of mysql in the local development environment consistent with the configuration of mysql on the server to prevent some weird problems from occurring due to inconsistent environments.

Have you encountered any weird problems during development? Welcome to leave a message and share.

For more MySQL related technical articles, please visit the MySQL Tutorial column to learn!

The above is the detailed content of Fields stored in MySQL are case-insensitive, did you know?. For more information, please follow other related articles on the PHP Chinese website!