MySQL character types are case sensitive

By default, MySQL character types are not case-sensitive, i.e. select * from t where name='AAA' is the same as ='aaa'. The following is a test example

(root@localhost)[hello]> create table test1(id int, name varchar(10));
(root@localhost)[hello]> insert into test1 values(1,'aaa'),(2,'AAA'),(3,'bbb'),(4,'BbB');
(root@localhost)[hello]> select * from test1;
+------+------+
| id | name |
+------+------+
| 1 | aaa |
| 2 | AAA |
| 3 | bbb |
| 4 | BbB |
+------+------+

(root@localhost)[hello]> select * from test1 where name = 'AAA';
+------+------+
| id | name |
+------+------+
| 1 | aaa |
| 2 | AAA |
+------+------+

(root@localhost)[hello]> select * from test1 where name = 'aaa';
+------+------+
| id | name |
+------+------+
| 1 | aaa |
| 2 | AAA |
+------+------+

It can be seen that there is no difference in the results of 'AAA' and 'aaa' after the where condition.

If you only want to find 'AAA', there are several ways to do it.
1. Add the binary keyword to sql

(root@localhost)[hello]> select * from test1 where binary name = 'AAA';
+------+------+
| id | name |
+------+------+
| 2 | AAA |
+------+------+

2. Modify the column definition

First view the definition of the original table

(root@localhost)[hello]> show create table test1\G
*************************** 1. row ***************************
  Table: test1
Create Table: CREATE TABLE `test1` (
 `id` int(11) DEFAULT NULL,
 `name` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

Modify the name column of table test1

alter table test1 modify column name varchar(10) character set utf8mb4 collate utf8mb4_bin default null;

collate utf8mb4_bin indicates that where filtering or order by sorting is case-sensitive

Now check the definition of test1

(root@localhost)[hello]> show create table test1\G
*************************** 1. row ***************************
  Table: test1
Create Table: CREATE TABLE `test1` (
 `id` int(11) DEFAULT NULL,
 `name` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

Then execute the query statement

(root@localhost)[hello]> select * from test1 where name='AAA';
+------+------+
| id | name |
+------+------+
| 2 | AAA |
+------+------+

Next, create a test2 table and you will find that the above statement to modify the column is actually equivalent to creating a table with varchar followed by binary.

(root@localhost)[hello]> create table test2(id int, name varchar(10) binary);
(root@localhost)[hello]> show create table test2\G
*************************** 1. row ***************************
  Table: test2
Create Table: CREATE TABLE `test2` (
 `id` int(11) DEFAULT NULL,
 `name` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

Here's how to set character case sensitivity

Set character case sensitivity at the database level

create

create database <db_name> default character set utf8mb4 collate utf8mb4_bin;

Revise

alter database <db_name> default character set utf8mb4 collate utf8mb4_bin;

Set character case sensitivity at table level

create

create table <tb_name> (
......
) engine=innodb default charset=utf8mb4 collate=utf8mb4_bin;

Revise

alter table <tb_name> engine=innodb default charset=utf8mb4 collate=utf8mb4_bin;

Set character case sensitivity at the column level

create

create table <tb_name> (
`field1` varchar(10) character set utf8mb4 collate utf8mb4_bin,
......
)

Revise

alter table <tb_name> modify column `field1` varchar(10) character set utf8mb4 collate utf8mb4_bin default null;

The inheritance relationship is column-->table-->library, and the priority is column>table>library

The above is the details of MySQL character type case sensitivity. For more information about MySQL character type case sensitivity, please pay attention to other related articles on 123WORDPRESS.COM!

You may also be interested in:

Notes on MySQL case sensitivity
How to set mysql to case insensitive
Analysis of problems caused by MySQL case sensitivity
How to solve the problem of case insensitivity in MySQL queries
MySQL database case sensitivity issue
Detailed explanation of MySQL table name case-insensitive configuration method
Linux system MySQL forgotten password, reset password, ignore the case of table and column names
How to distinguish uppercase and lowercase letters in strings when querying MySQL
MySql query case insensitive solution (two)
MySQL table name case selection

<<: Detailed description of the function of new in JS

>>: About the garbled problem caused by HTML encoding