How to Set MySQL database name, table name Case sensitivity, and mysql database name in windows and linux systems

How to Set MySQL database name, table name Case sensitivity, and mysql database name in windows and linux systems

In MySQL, databases and tables are actually directories and files under the Data Directory.

Therefore, the sensitivity of the operating system determines the case sensitivity of the database and table names. This means that the database and table names are case-insensitive in Windows and are case-sensitive in most types of Unix/Linux systems.

MySQL is case sensitive and can be controlled by the lower_case_table_names parameter in the configuration file.

WINDOWS:

Edit my. ini file, add lower_case_table_names = 0 under [mysqld] (Note: when it is 0, it is lowercase sensitive; when it is 1, it is lowercase insensitive; The default value is 1 ), mySql can define the table name according to the case-insensitive State of the SQL statement for table creation.

LINUX:

Edit/etc/my. in the cnf file, add the lower_case_table_names parameter in the [mysqld] section and set the corresponding value (NOTE: When the value is 0, it is lowercase sensitive; when the value is 1, it is lowercase insensitive; The default value is 0)

In linux, how does one make mysql tables case-insensitive to SQL statements?

Case Insensitive in Windows

Create table tab (
Id INT AUTO_INCREMENT,
Val VARCHAR (10 ),
Primary key (id)
);

Mysql> select * from tab;
Empty set (0.00 sec)

Mysql> select * from Tab;
Empty set (0.00 sec)

Mysql> select * from 'tab ';
Empty set (0.00 sec)

If you want to store and use the table name and database name on a hard disk in MySQL, which is determined by the lower_case_tables_name system variable, you can set it when starting mysqld. Lower_case_tables_name can use any of the following values:
Value Meaning
0 store the TABLE name and DATABASE name on the hard disk in both upper and lower case specified by the create table or create database statement. The name is case sensitive. This is the default setting in Unix systems. Note that if you use -- lower-case-table-names = 0 in a case insensitive file system and use different cases to access the MyISAM table name, the index may be damaged.
1. The table name is saved in lowercase on the hard disk. The table name is case sensitive. MySQL converts all table names to lowercase for storage and search. This behavior is also suitable for database names and table aliases. This value is the default value in Windows and Mac OS X systems.
2. The TABLE name and DATABASE name are saved on the hard disk using the case specified by the create table or create database statement, but MySQL converts them to lowercase for search. The name is case sensitive. Note: Applicable only to file systems that are case insensitive! The InnoDB table name is saved in lowercase, for example, lower_case_tables_name = 1.
In Windows and Mac OS X, the default value of lower_case_tables_name is 1.
If you only use MySQL on one platform, you do not need to change the lower_case_tables_name variable. However, if you want to transfer tables between platforms of file systems that are case sensitive, you may encounter difficulties. For example, in Unix, my_tables and MY_tables are two different tables, but in Windows, the two tables have the same names. To avoid data transfer problems caused by the case sensitivity of the database or table name, you can use two options:
• Lower_case_tables_name = 1 can be used in any system. The disadvantage of using this option is that when show tables or show databases is used, it cannot be seen whether the name is in upper or lower case.
• Use lower_case_tables_name = 0 in Unix and lower_case_tables_name = 2 in Windows. In this way, the Case sensitivity of the Database Name and table name can be retained. The disadvantage is that the database name and table name are always referenced in the correct case in Windows. If you transfer a query to Unix, since it is important to make it big and lowercase in Unix, it does not work if the case is incorrect.
Exception: if you are using an InnoDB table, set lower_case_tables_name to 1 on any platform to forcibly convert the name to lowercase.
Note that before setting lower_case_tables_name to 1 in Unix, you must convert the old database name and table name to lowercase before restarting mysqld .... Remaining full text>

Case sensitivity of mysql table names in linux

This has nothing to do with hibernate, MYSQL issues

1. After mysql is installed in Linux, the default value is: case-sensitive for table names, not case-sensitive for column names;
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.
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.