MySQL table name and column name Case sensitivity

When I first started using mysql, I first wanted to differentiate the case sensitivity of column names. I found many methods on the Internet, so

1. After mysql is installed in linux, the default value is: case-sensitive for table names, not case-sensitive for column names;

2. 2. log on to the/etc/my. add lower_case_table_names = 1 after [mysqld] In cnf and restart the MYSQL service. The setting is successful: The table name is case-insensitive;

Lower_case_table_names parameters:

Lower_case_table_names = 0

0: Case Sensitive, 1: case insensitive

In Linux, the database name, table name, column name, And alias are case-sensitive:

1. The database name and table name are case sensitive;

2. Table aliases are case sensitive;

3. The column name and column alias are case-insensitive in all cases;

4. Variable names are case sensitive;

MySQL is case insensitive in Windows.

Add lower_case_table_names = 1 after [mysqld] In my. ini and restart the MYSQL service. The setting is successful: The table name is case-insensitive;

Lower_case_table_names parameters:

Lower_case_table_names = 0

0: Case Sensitive, 1: case insensitive

3. If you want to query the case sensitivity of the time zone field value, you need to set the BINARY Attribute for the field value. You can set multiple methods:

A. set at creation:

Create table t (

A varchar (10) BINARY

);

B. Use alter to modify:

Alter table 'tablename' modify column 'cloname' VARCHAR (45) BINARY;

C. Select BINARY in mysql table editor.

Solve the garbled problem. For more details, refer to setting the character set.

Add the following sentence under [mysqld]

Default-character-set = utf8

However, after my own practice, if the modified table name is case sensitive, it indicates that after you create a table, it retains the original appearance of the table name when you create it, however, this table is case-insensitive when stored in the system background. That is to say, it is not allowed to create a table whose names are case-insensitive.

For example;

After the table name is changed to case-sensitive, that is, lower_case_table_names = 0, regardless of the system in which the table name is located, enter

CREATE TABLE TEST

Id int not null primary key,

Namevarchar not null

);

Then execute show tables;

The table name contains a TEST table, which is case-insensitive,

If you enter show tables like 't%'; the newly created table cannot be found;

Then you are creating another table,

Create table test

Id int not null primary key,

Namevarchar not null

);

The system will prompt you that the table test already exists and cannot be created.