Details the MySQL query string in case discrimination method

  • 2021-01-06 00:45:50
  • OfStack

If you insert two rows of values 'A' and 'a' into a column with a one-only constraint in mysql,Mysql will consider it the same, which is not the case in oracle. Is the default mysql field value case insensitive? This is a big headache. Use the client directly to query the database with sql. It was found that size was indeed insensitive.
It is found that collate (proofreading) needs to be set by querying the data.
collate rules:

*_bin: binary, case, sensitive, collation, binary, case *_cs: case sensitive collation, case sensitive *_ci: case insensitive collation, case insensitive

With respect to character sets and validation rules, mysql can:

1. Use character sets to store strings and support multiple character sets;
2. Use validation rules to compare strings. Multiple validation rules can be used for the same character set.
3. Mix and combine strings using different character sets or collation rules on the same server, the same database, or even the same table;
4. You can define different character sets and validation rules at any level (server, database, table, field, string).

Enforce case sensitivity

The binary keyword can be used in two ways:
Type 1: Make mysql queries case sensitive

select * from usertable where binary id='AAMkADExM2M5NjQ2LWUzYzctNDFkMC1h'; 

Type 2: Identify the table when it is being built

create table `usertable`( 
 `id` varchar(32) binary, 
 PRIMARY KEY (`id`) 


CREATE TABLE `usertable` ( 
 `id` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '', 
 PRIMARY KEY (`id`) 

In mysql, there are also capitalization issues:
(1) Keyword: case insensitive select * fRom table_name and select * from table_name
(2) Identifiers (such as database names and table names) : case-insensitive. select * from users and select * from uSers have the same effect if table users is present. This depends on the operating system. It is case sensitive on all Unit operating systems (except Mac OS which uses HFS+), and case insensitive on windows. (this one is not verified online, I'm windows server2003 are not case sensitive)
select m.* from users m where M.username = 'aa';
username as uname from users where id = 768;

Related articles: