MySQL character types are case sensitive

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

Recommend

Vue implements login type switching

This article example shares the specific code of ...

MySQL query specifies that the field is not a number and comma sql

Core SQL statements MySQL query statement that do...

How to build your own Nexus private server in Linux

This article describes how to build a Nexus priva...

Advantages and disadvantages of common MySQL storage engines

Table of contents View all storage engines InnoDB...

Detailed explanation of Nginx static service configuration (root and alias instructions)

Static files Nginx is known for its high performa...

Understanding and using callback functions in JavaScript

Table of contents Overview What are callbacks or ...

docker run -v mounts data volumes abnormally, and the container status is always restarting

Problems: After adding the -v parameter to docker...

Vue.js implements simple folding panel

This article example shares the specific code of ...

Detailed example of HTML element blocking Flash

Copy code The code is as follows: wmode parameter...

Vue2/vue3 routing permission management method example

1. There are generally two methods for Vue routin...

Example of converting JS one-dimensional array into three-dimensional array

Today I saw a friend asking a question in the Q&a...

MySQL isolation level detailed explanation and examples

Table of contents 4 isolation levels of MySQL Cre...

Common pitfalls of using React Hooks

React Hooks is a new feature introduced in React ...

Detailed tutorial on using the tomcat8-maven-plugin plugin in Maven

I searched a lot of articles online but didn'...

CSS+HTML to realize the top navigation bar function

Implementation of navigation bar, fixed top navig...