MySQL database case sensitivity cracking

We all know that in MySQL databases, databases correspond to their tables in those directories and related files, therefore, the sensitivity of the relevant operating system determines the case sensitivity of the database and table naming. This means that the database and table names are case sensitive on Unix, while the case sensitivity is ignored on Win32.

Database and table name

In MySQL, databases and tables correspond to directories and files under those directories. Therefore, the sensitivity of the internal operating system determines the case sensitivity of databases and tables. This means that the database and table names are case-sensitive on Unix, while the case sensitivity is ignored on Win32.

Note: On Win32, although MySQL Databases and table names are case-insensitive, you should not reference a given database or table in the same query using different cases. The following query will not work because it references a table as my_table and MY_TABLE:

1.

 
 

  
  
MySQL> SELECT * FROM my_table WHERE MY_TABLE.col=1; 
 
 

2. Column name

The column names are case-insensitive in all cases.

3. Table alias

Table aliases are case sensitive. The following query will not work because it uses a and A to reference aliases:

1.

 
 

  
  
MySQL> SELECT col_name FROM tbl_name AS a 
 
 

2.

 
 

  
  
WHERE a.col_name = 1 OR A.col_name = 2; 
 
 

4. Column alias

The column alias is case-insensitive.

5. String comparison and pattern matching

By default, MySQL database search is case insensitive (although some character sets never ignore MySQL Case sensitivity, such as Czech ). This means that if you search by col_name LIKE 'a % ', you will get all column values starting with a or. If you want to make this search case sensitive, use INDEX (col_name, "A") = 0 to check A prefix. Or if the column value must be "A", use STRCMP (col_name, "A") = 0.

Simple comparison operations (> =,>, =, <, <=, sort, and aggregation) are based on the "Sort value" of each character ". Characters with the same sorting value (such as E and e) are considered to be the same character!

LIKE is compared to the upper-case values of each character ("E" = "e ").

If you want a column to always be considered MySQL case-sensitive, declare it as BINARY.

For example

 
 

  
  
1.Mysql> SELECT "E"="e","E"=BINARY "e";   
  
  
2.+---------+----------------+| "E"="e" | "E"=BINARY "e"   
  
  
|+---------+----------------+| 1 | 0 |+---------+----------------+ 
 
 

The above content describes the case sensitivity of the MySQL database. I hope it will help you in this regard.