MySQL Case sensitivity

MySQL Case sensitivity

Address: http://zhaolinjnu.blog.sohu.com/45332540.html

The case sensitivity of MySQL is mainly divided into two parts: one is the case sensitivity of table names; the other is the case sensitivity of field values. this is the opposite of what we want, but it is the default setting of MySQL, which has to be puzzling, and these are the basic things of Oracle Database.

First, let's talk about the features of MySQL table creation? If you create a test table and go to the data directory, you will find the following three files:

Test. FRM
Test. MYD
Test. myi

Create a table, which automatically generates three files. This is also very different from Oracle. If so, how can we use bare devices? It seems that you have to use a file system to use a MySQL database. Generally, the operating system has a limit on the number of files that users can open at the same time. Generally, there are 1024 files. Pay attention to this when using MySQL.

So how can we make MySQL table names case insensitive? Modify the/etc/My. CNF file and add the parameter lower_case_table_names = 1 under [mysqld ].

The meaning of this parameter is to convert all the table names to lowercase for processing. If you already have an upper-case table name in the original system, you must first rename them to lower-case ones, this parameter cannot be recognized by tables in upper case.

Another problem is that the default field values are case-insensitive? This is a headache. If you insert two rows of values 'A' and 'A' in a column with a unique constraint, MySQL considers it to be the same, but not in Oracle. See the following test:

Mysql> Create Table test4 (Nick varchar (20) primary key );
Query OK, 0 rows affected (0.01 Sec)

Mysql> insert into test4 values ('A ');
Query OK, 1 row affected (0.00 Sec)

Mysql> insert into test4 values ('A ');
Error 1062: duplicate entry 'A' for key 1

But how can we make the column values case sensitive?

Mysql> Create Table test4 (Nick varchar (20) binary primary key );
Query OK, 0 rows affected (0.01 Sec)

Mysql> insert into test4 values ('A ');
Query OK, 1 row affected (0.00 Sec)

Mysql> insert into test4 values ('A ');
Query OK, 1 row affected (0.00 Sec)

After declaring the character type, add a binary value to the end, and MySQL can be case sensitive.