MySQL table name ignores case

MySQL table name ignores case

Problem Description:
A development colleague in the Linux downgrade a program always error said can not find the table, but landed mysql,show tables view is already created this table!! As follows:

1234567891011121314151617181920212223

mysql> show tables;+-------------------------------------------+| Tables_in_huan_db                         |+-------------------------------------------+| advertisement                             || message_queue                             || message_sys_user                          || message_user                              || opHistory                                 || opHistory_queue                           || opHistory_queue_result_log                || opHistory_queue_send_fail_log             |+-------------------------------------------+8 rowsinset(0.00 sec)mysql> descopHistory;ERROR 1146 (42S02): Table‘haun_db.opHistory‘doesn‘t existmysql> desc opHistory_queue;ERROR 1146 (42S02): Table ‘haun_db.opHistory_queue‘ doesn‘t existmysql> descopHistory_queue_result_log;ERROR 1146 (42S02): Table‘haun_db.opHistory‘doesn‘t existmysql> desc opHistory_queue_send_fail_log;ERROR 1146 (42S02): Table ‘haun_db.opHistory_queue_send_fail_log‘ doesn‘t exist

This table is clearly present, why can't the program find the table??

Reason Lookup:
because MySQL is the default for Linux to differentiate table name casing . MySQL case sensitivity setting is determined by the parameter lower_case_table_names, where:
1) lower_case_table_names = 0
Case-sensitive (that is, case sensitivity to table names), which is the default setting. When this is set, the table names created in MySQL do not have any effect on uppercase letters, and can be read and referenced normally. The variable lower_case_file_system indicates whether the file system on which the data directory resides is case-sensitive, where: on indicates the case of file names is not sensitive, off is sensitive.

12345678

mysql> show variables like"%case%";+------------------------+-------+| Variable_name          | Value |+------------------------+-------+| lower_case_file_system | ON|| lower_case_table_names | 0     |+------------------------+-------+2 rowsinset(0.00 sec)

2) Lower_case_table_names = 1
Case insensitive (that is, insensitive to table name capitalization). When this is set, the table name is saved in lowercase on the hard disk, and MySQL converts all table names to lowercase storage and lookup tables. This behavior is also appropriate for database names and table aliases .
That is, after MySQL is set to non-partition case, when you create a library or a table, you create it with uppercase letters, and when you create it, you are forced to save it in lowercase!

12345678

mysql> show variables like"%case%";+------------------------+-------+| Variable_name          | Value |+------------------------+-------+| lower_case_file_system | OFF || lower_case_table_names | 1     |+------------------------+-------+2 rowsinset(0.00 sec)

The above error is because:
MySQL is the default case-sensitive setting when creating this uppercase table in MySQL before development, which is case insensitive.
Later, the operation of the colleague and MySQL to the non-partition of the size of the set, at this time, MySQL is mandatory to save the table in lowercase, so after the development of the program again in the reference to this table, will be an error saying that there is no!

Workaround:
1) First adjust the Lower_case_table_names parameter to 0 in my.cnf, and change it to case-sensitive.
2) then mysqladmin-uroot-p shutdown to shut down the database in Safe mode
3) Log in to MySQL database, "ALTER TABLE old table name rename new table name" To change the old table with uppercase letters to a lowercase table name
4) Finally, you can start MySQL again!

==========================================================
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 alias of the table is strictly case-sensitive;
3) The alias of the column name and column is ignored in all cases;
4) Variable names are also strictly case-sensitive;
5) MySQL is not case-sensitive under Windows, but the default is case-sensitive under Linux.
6) If you want to distinguish the case of a field value at query time, the field value needs to be set to the binary property, which can be set in several ways:
A) settings at creation time:
CREATE TABLE T (A VARCHAR (Ten) BINARY);
b) Modify with alter

So in different operating systems in order to make the program and the database can be normal operation, the best way is to design the table in the time of the lower case!!

Modify MySQL for case-insensitive settings:
[[email protected] ~]# mysqladmin-uroot-p shutdown//Shut down the database in Safe mode
[[email protected] ~]# CAT/ETC/MY.CNF//Add the following line settings
.....
[Mysqld]
Lower_case_table_names=1
.....

[[email protected] ~]#/etc/init.d/mysql start//startup MySQL
========================================================

MySQL table name case sensitivity rules
1) under Linux:
Database name and table name are strictly case-sensitive;
Table aliases are strictly case-sensitive;
The alias of the column name and column is ignored in all cases;
Variable names are also strictly case-sensitive;
2) under Windows:
are case insensitive
3) under Mac OS (non-UFS volume):
are case insensitive

Parameter Description (lower_case_table_names)
LUnix the default value for Lower_case_table_names is 0, the default value under Windows is 1, and the default value under Mac OS x is 2.

Parameter values	Explain
0	Save the table name and database name on your hard disk using the uppercase and lowercase letters specified by the CREATE TABLE or CREATE DATABASE statement. The name comparison is case sensitive. On a case insensitive operating system such as Windows or Mac OS X we cannot set this parameter to 0 if--lowercase-table-names is coerced to 0 on case-insensitive file systems, and the MyISAM table name is accessed with a different case. May cause index corruption.
1	Table names are saved in lowercase on the hard disk, and the name comparisons are not case sensitive. MySQL converts all table names to lowercase on the storage and lookup tables. This behavior is also appropriate for database names and table aliases. The value is the default value for Windows.
2	The table name and database name are saved on the hard disk using the uppercase and lowercase letters specified by the CREATE TABLE or CREATE DATABASE statement, but MySQL converts them to lower case on the lookup table. Name comparisons are not case sensitive, that is, they are saved by case, and are compared in lowercase. Note: Applies only on file systems that are not case sensitive! InnoDB table names are saved in lowercase.

Converting from case sensitivity to insensitive method
If the original libraries and tables are case-sensitive and want to be converted to case insensitive, there are 3 main steps to make:
1) Export the database data through mysqldump.
2) Change Lower_case_tables_name = 1 in my.cnf and restart the MySQL database.
3) Import the exported data into the MySQL database.

Precautions
In order to avoid case-based problems, a recommended naming convention is to define the database, tables, and columns with all lowercase letters and underscores, without using any uppercase letters.
Lower_case_tables_name=1 can be used in any system. The disadvantage of using this option is that when you use show tables or show databases, you cannot see whether the name is originally capitalized or lowercase.
Note that in Unix, the old database name and table name must be converted to lowercase before restarting mysqld before lower_case_tables_name = 0 before Lower_case_tables_name is set to 1.

This article turns from: https://www.cnblogs.com/kevingrace/p/6150748.html vanity

MySQL table name ignores case