MySQL Case sensitive

Author:skate
Time:2015/03/11

MySQL Case sensitive

String case sensitivity is related to MySQL database name, table name, field name, and field value.

1. Related to database names, table names, stored procedures, and triggers
The case sensitivity of controlling database names and table names in MySQL is controlled by the parameter lower_case_table_names.
is 0 o'clock ;
Represents case-sensitive, and the name of the table and database is saved on the hard disk by 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 the
The case-insensitive file system enforces--lowercase-table-names to 0 and accesses the MyISAM table name with a different case.
May cause index corruption.

is 1 o'clock ;
Indicates that the name is converted to lowercase, and the name comparison is not sensitive to case. 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.

is 2 o'clock ;
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
Convert to lowercase on the lookup table. Name comparisons are not case sensitive, that is, they are saved by case, and are compared in lowercase. Note: only on the size
Write non-sensitive file system on use! InnoDB table names are saved in lowercase.

If you use the InnoDB table, in order to avoid the case sensitive problem, you can put Lower_case_table_names=1

Change the lower_case_table_names from 0 to 1
Before you set Lower_case_table_names to 1 o'clock, change the database name and table name to lowercase before restart your mysqld.
Mysql> RENAME TABLE T1 to T1;

2. Related to Field names
Field names are case-insensitive

3. Related to field values
The case of the field value is controlled by the MySQL collate. When it comes to collate, you have to say character sets. The character set is a set of symbols and encodings, collate
is a set of rules for comparing characters within a character set, such as a rule that defines a relationship such as ' A ' < ' B '. Different character sets have a variety of proofing rules, generally
, collate begins with its associated character set name, usually including a language name, and is _ci (case insensitive), _cs (case sensitive)
Or _bin (two yuan) ended.

such as the UTF8 character set:
Utf8_general_ci, which is not case sensitive, this is the default collate of the UTF8 character set;
Utf8_general_cs indicates case-sensitive,
Utf8_bin represents a binary comparison and is also case-sensitive.

View information about the character set for a database
Mysql> Show variables like '%character% ';
+--------------------------+----------------------------------+
| variable_name | Value |
+--------------------------+----------------------------------+
| character_set_client | UTF8 |
| character_set_connection | UTF8 |
| Character_set_database | UTF8 |
| Character_set_filesystem | binary |
| Character_set_results | UTF8 |
| Character_set_server | UTF8 |
| Character_set_system | UTF8 |
| Character_sets_dir | /usr/local/mysql/share/charsets/|
+--------------------------+----------------------------------+
8 rows in Set (0.00 sec)

View database collation related information
mysql> show variables like '%collation% ';
+----------------------+-----------------+
| variable_name        | value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_ database   | Utf8_general_ci |
| collation_server     | utf8_general_ci |
+----------------------+-----------------+
3 rows in Set (0.01 sec)

Test is the database created by default
Mysql> Show create DATABASE test;
+----------+---------------------------------------------------------------+
| Database | Create Database |
+----------+---------------------------------------------------------------+
| Test | CREATE DATABASE ' test '/*!40100 DEFAULT CHARACTER SET UTF8 */| |
+----------+---------------------------------------------------------------+
1 row in Set (0.00 sec)

TESTX is a database of specified character sets and collation
Mysql> CREATE DATABASE testx default character set UTF8 collate utf8_bin;
Query OK, 1 row Affected (0.00 sec)

Mysql> Show CREATE Database testx;
+----------+---------------------------------------------------------------------------------+
| Database | Create Database |
+----------+---------------------------------------------------------------------------------+
| Testx | CREATE DATABASE ' testx '/*!40100 DEFAULT CHARACTER SET UTF8 COLLATE utf8_bin * |
+----------+---------------------------------------------------------------------------------+
1 row in Set (0.00 sec)

Case2 is a table created by default
Mysql> Show CREATE TABLE case2;
+-------+------------------------------------------------------------------------------------------------------ ------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------ ------------------+
| Case2 | CREATE TABLE ' Case2 ' (
' A ' int (one) DEFAULT NULL,
' B ' varchar (TEN) DEFAULT NULL
) Engine=innodb DEFAULT Charset=utf8 |
+-------+------------------------------------------------------------------------------------------------------ ------------------+
1 row in Set (0.00 sec)

Case4 is a table of the specified character set and collate
Mysql> CREATE TABLE Case4 (a int), B varchar (TEN)) default character set UTF8 collate utf8_bin;;
Query OK, 0 rows affected (0.12 sec)

Mysql> Show CREATE TABLE case4;
+-------+------------------------------------------------------------------------------------------------------ ----------------------------------------------------+
| Table |                                                                                                                                             Create Table |
+-------+------------------------------------------------------------------------------------------------------ ----------------------------------------------------+
| Case4 | CREATE TABLE ' Case4 ' (
' A ' int (one) DEFAULT NULL,
' B ' varchar (ten) COLLATE Utf8_bin DEFAULT NULL
) engine=innodb DEFAULT Charset=utf8 Collate=utf8_bin |
+-------+------------------------------------------------------------------------------------------------------ ----------------------------------------------------+
1 row in Set (0.00 sec)

We can specify whether it is case-sensitive when creating a database or table, and if not specified, the statement-level collate and binary can also be implemented

Mysql> select * from Case1;
+------+------+
| A | B |
+------+------+
| 1 | AAA |
| 2 | BBB |
| 4 | AAA |
| 3 | BBB |
+------+------+
4 rows in Set (0.00 sec)

Mysql> SELECT * from case1 where B like '%b% ';
+------+------+
| A | B |
+------+------+
| 2 | BBB |
| 3 | BBB |
+------+------+
2 rows in Set (0.00 sec)

Specify collation
Mysql> SELECT * from case1 where B like '%b% ' collate utf8_bin;
+------+------+
| A | B |
+------+------+
| 2 | BBB |
+------+------+
1 row in Set (0.01 sec)

Converting a string to a binary comparison by binary, because the binary of uppercase and lowercase characters is definitely different, is also a way to differentiate size
Mysql> SELECT * from case1 where binary B like '%b% ';
+------+------+
| A | B |
+------+------+
| 2 | BBB |
+------+------+
1 row in Set (0.00 sec)

Mysql>

The last thing to note is the relationship between collation and the index store. Because collation is used for comparison between strings, and the index is based on a relatively orderly arrangement,
So collation will affect the index order of the records

Reference:
Http://dev.mysql.com/doc/refman/5.0/en/charset-collation-names.html
Http://dev.mysql.com/doc/refman/5.0/en/identifier-case-sensitivity.html

MySQL Case sensitive