Solutions to problems that may easily occur in Mysql Case

The following articles mainly introduce how to solve some cases of Mysql. This article studies Mysql sensitivity in detail, the following is a detailed introduction to the specific content of this article. I hope you will have a better understanding of Mysql Case sensitivity issues after browsing.

1. 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 the database 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:

 
 

  
  
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:

 
 

  
  
Mysql> SELECT col_name FROM tbl_name AS a  
  
  
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 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:

 
 

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

The above content is a description of the Mysql Case sensitivity issue, hoping to help you in this regard.