Shulou(Shulou.com)06/01 Report--

This article mainly gives you a brief talk about MySQL case-sensitive setting method, related technical terms you can check online or find some related books to add, here is not involved, we will go straight to the topic, I hope MySQL case-sensitive setting method This article can bring you some practical help.

If you search by keyword case, you will find two parameters.

mysql> show variables like '%case%';

+------------------------+-------+

| Variable_name | Value |

+------------------------+-------+

| lower_case_file_system | OFF |

| lower_case_table_names | 0 |

+------------------------+-------+

2 rows in set (0.01 sec)

The first parameter is whether the system path is case-sensitive. We skip it for the time being and look at the second parameter lower_case_table_names. Its meaning is 0: case-sensitive, 1: case-insensitive.

Case-sensitive issues fall into two categories in MySQL, on Linux and Windows platforms.

MySQL database name, table name, column name, alias case rules under Linux are as follows:

1. Database names and table names are strictly case-sensitive;

2. The alias of the table is strictly case-sensitive;

3. Column names and column aliases are case insensitive in all cases;

4. Variable names are also strictly case-sensitive;

MySQL on Windows is case-insensitive and all table names and database names are lowercase.

For example, we create the table test_tab, and then insert two rows of records to continue the test.

create table test_tab(id int,name varchar(64));

insert into test_tab values(1,'aa'),(2,'bb');

By default, case sensitivity is turned on, so test_tab,TEST_TAB are two completely different objects.

mysql> select id,name from TEST_TAB;

ERROR 1146 (42S02): Table 'test.TEST_TAB' doesn't exist

mysql>

At the column level, MySQL is case-insensitive.

mysql> select id,NAMe from test_tab;

+------+---------+

| id | NAMe |

+------+---------+

| 1 | aa |

| 2 | newtest |

+------+---------+

2 rows in set (0.00 sec)

If you want to modify a case-sensitive setting, such as setting it to case-insensitive, you need to set the parameter value and restart.

mysql> set global lower_case_table_names=on;

ERROR 1238 (HY000): Variable 'lower_case_table_names' is a read only variable

After restart, test_TAB and the original test_tab mean the same thing.

mysql> desc test_TAB;

+-------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+-------+

| id | int(11) | YES | | NULL | |

| name | varchar(64) | YES | | NULL | |

+-------+-------------+------+-----+---------+-------+

2 rows in set (0.01 sec)

However, switching in case-sensitive cases can easily cause problems.

In particular, you need to pay attention to this compatibility problem, if there are still doubts about the storage process, etc., you can also turn on test mode.

delimiter //

drop procedure if exists `test_proc` //

create procedure test_proc(in push_id char(64))

begin

update Test.test_tab set name='test' where id=2;

end

//

delimiter ;

mysql> call test_proc('newtest');

Query OK, 1 row affected (0.00 sec)

mysql> select *from test_tab;

+------+---------+

| id | name |

+------+---------+

| 1 | aa |

| 2 | newtest |

+------+---------+

2 rows in set (0.00 sec)

In this place or to do detailed acceptance testing, be sure to have the test results prevail, because the case of the table data is not accessible should not be.

In addition, I would like to mention the case problem in Oracle. Oracle has a default parameter sec_case_sensitive_logon in 11g. The actual application of this parameter is really tragic. First, many customers do not like this feature. It seems to be regulated from a security point of view, but it seems to bring more problems. Quite a few of them are caused by habit. On the other hand, because this parameter was introduced in 11g, this parameter expired in 12c, and its meaning has also changed. There will be some ambiguity in the use of understanding.

This parameter can only ensure that the login user password is case-sensitive, and the rest is the same as the original. In 12c, this parameter has changed its flavor and expired in the new version. When setting, it is necessary to ensure that the settings in sqlnet.ora are compatible. If the parameter in the file is 12, 12a, it is necessary to ensure that SEC_CASE_SENSITIVE_LOGON is not false.

MySQL case-sensitive setting method will first give you to talk about here, for other related issues you want to know can continue to pay attention to our industry information. Our section content captures some industry news and expertise to share with you every day.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.