Mysql Case-sensitive issues

MYSQL queries are case insensitive by default, for example:

CREATE TABLE T1 (NAME VARCHAR (10));

For this table, by default, the results of the following two queries are the same:

SELECT * from T1 WHERE name= ' ABC '; SELECT * from T1 WHERE name= ' ABC ';

That is, case-insensitive, while database design may require case sensitivity, the workaround is to use binary markers when building a table.

CREATE TABLE T1 (NAME VARCHAR () BINARY);

Or use the binary keyword:

SELECT * from T1 WHERE NAME = BINARY ' ABC '; SELECT * from T1 WHERE NAME is like BINARY '%abc% ';

In addition, in MySQL, databases and tables correspond to directories and files in those directories. Thus, the sensitivity of the operating system determines the case sensitivity of database and table naming. This means that the database and table names are case-insensitive in Windows and are case-sensitive in most types of Unix systems.

Although the database and table names in Windows are case-insensitive, you should not use different casing in the same query to refer to a given database and table. The following query will not work because it references a table in my_table and my_table:

SELECT * from my_table WHERE my_table.col=1;

The alias of the column name and column is case-insensitive in all cases.

The alias of the table is case-sensitive. The following query will not work because it uses a and a to refer to aliases:

SELECT col_name from Tbl_name as a WHERE a.col_name = 1 OR a.col_name = 2;

If it is difficult to memorize the letter capitalization of the database and table names, it is recommended to adopt a consistent convention, such as always creating databases and tables in lower case letters.

Another way to avoid this problem is to start mysqld with the-o lower_case_table_names=1 parameter. By default, this option is 1 in Windows and 0 in Unix. If Lower_case_table_names is 1, MySQL will convert all table names to lowercase when storing and locating. (Starting with MySQL 4.0.2, this option also applies to database names.) Note that when you change this option, you must first convert the old table names to lowercase letters before starting mysqld.

You can also add a line to the MySQL configuration file My.ini:

Lower_case_table_names = 0

Where 0: Case sensitive, 1: Case insensitive

MySQL under Linux database name, table name, column name, alias casing rules are like this:

1, database name and table name are strictly case-sensitive;

2, the table alias is strictly case-sensitive;

3, the column name and the alias of the column in all cases are ignored case;

4, variable names are also strictly case-sensitive;

MySQL is case insensitive under Windows.

Mysql Case Problem