MySQL database case sensitivity issue

In MySQL, databases correspond to directories within the data directory. Each table in the database corresponds to at least one file in the database directory (or multiple files, depending on the storage engine). Therefore, the case sensitivity of the operating system you are using determines the case sensitivity of database and table names. This means that database and table names are case-sensitive in most Unix systems, but not in Windows. A notable exception is Mac OS X, which is based on Unix but uses a default file system type (HFS+) that is case-insensitive.

Table names are not case-sensitive in Windows, so after importing data, it is possible that all table names are lowercase. After importing from Windows to Linux, case issues are likely to occur when calling. For example:

First point: The program call table name is: codetc_Web;

Second point: After importing win, it becomes codetc_web;

The third point: After importing into Linux, it is still codetc_web. At this time, Linux will distinguish between uppercase and lowercase letters of the table name, which will cause the table to be unreadable.

Solution to the case problem of MySQL table names under Linux:

Modify my.cnf, which is usually located at: /etc/my.cnf, find the [mysqld] section in the file and add the following statement (note that if the statement already exists, change the value to 1):

lower_case_table_names=1

1. After installing MySQL in Linux, the default is: table names are case-sensitive, but column names are not case-sensitive;

2. After logging in with the root account, add lower_case_table_names=1 after [mysqld] in /etc/my.cnf and restart the MYSQL service. The setting is now successful: table names are not case sensitive;

Detailed explanation of the lower_case_table_names parameter:

lower_case_table_names = 0或1

0: case sensitive, 1: case insensitive

The case rules for MySQL database names, table names, column names, and aliases under Linux are as follows:

1. Database names and table names are strictly case-sensitive;

2. The table alias is strictly case-sensitive;

3. Column names and column aliases are case-insensitive in all cases;

4. Variable names are also strictly case-sensitive;

MySQL is not case-sensitive under Windows. If you need to distinguish the case of field values ​​when querying, you need to set the BINARY attribute for the query field value. There are several ways to set it:

(1) Setting at creation time:

CREATE TABLE T(A VARCHAR(10) BINARY);

(2) Use alter to modify:

ALTER TABLE `tablename` MODIFY COLUMN `cloname` VARCHAR(45) BINARY;

(3) Directly check the BINARY item in the MySQL table editor.

To make MySQL query case sensitive, you can:

select * from table_name where binary a like 'a%'  
select * from table_name where binary a like 'A%'

You can also mark it when creating a table

create table table_name( 
   a varchar (20) binary
)

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM. If you want to learn more about this, please check out the following links