How to enable SQL Server-compatible configuration for database name Case sensitivity

Source: Internet
Author: User

Set the global variable lower_case_table_names to 1.

Lab:
1. lower_case_tables_name = 0 (default in Linux)
Start mysql directly and create tables MyTable and mytable in mytest
Mysql> use mytest;
Mysql> create table MyTable (id int not null, name varchar (10), dt date );
Mysql> create table mytable (id int not null, name varchar (10), dt date );
Mysql> show tables;
+ ------------------ +
| Tables_in_mytest |
+ ------------------ +
| MyTable |
| Mytable |
+ ------------------ +
By default, you can create both mytable and MyTable tables.

2. lower_case_tables_name = 1 (we need to set this case)
Go to the etc directory and edit the my. cnf file.
[Root @ MYSQLServer etc] # vi my. cnf
Find [mysqld]
Add a row at the end of its global variable: lower_case_table_names = 1
Save, exit, and restart mysql
[Root @ MYSQLServer etc] # service mysqld restart;
Go to mysql
[Root @ MYSQLServer mysql] # bin/mysql
Mysql> use mytest;
Mysql> select * from MyTable;
Mysql> select * from mytable;
The results of the two queries are the same. Both queries are mytable tables, but MyTable tables cannot be found (as you can see, but cannot be found)
Let's try again to create a MyTable table.
Mysql> create table MyTable (id int not null, name varchar (10), dt date );
ERROR 1050 (42S01): Table 'mytable' already exists
The table already exists.

Similarly, we can try to create the MyTest Library
Mysql> create database MyTest;
ERROR 1007 (HY000): Can't create database 'mytest'; database exists
You will also be prompted that the database already exists.

In this case, table names and database names are case insensitive.

Note: Before setting lower_case_tables_name to 1 in Unix, you must first convert the old database name and table name to lowercase before restarting mysqld. Otherwise, the old table cannot be found.
(In the above experiment, MyTable cannot be found when lower_case_tables_name = 1)

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.