Shulou(Shulou.com)05/31 Report--

This article introduces the knowledge of "is there a difference in case in MySQL?". In the operation of actual cases, many people will encounter such a dilemma. Then let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

If you search by the keyword case, you will find that there are 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.01sec)

The first parameter is whether the system path is case-sensitive. Let's skip for a moment and look at the second parameter, lower_case_table_names, which means 0: case-sensitive and 1: case-insensitive.

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

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

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

2. Aliases for tables are strictly case-sensitive

3. Column names and column aliases ignore case in all cases

4. Variable names are also strictly case-sensitive

However, MySQL under Windows is case-insensitive, and all table and database names are changed to lowercase.

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

Create table test_tab (id int,name varchar 64)

Insert into test_tab values (1), (2) (2)

By default, case sensitivity is turned on, so test _ tab,TEST_TAB is 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 change case-sensitive settings, such as setting to case-insensitive, you need to set the parameter value and restart it.

Mysql > set global lower_case_table_names=on

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

After rebooting, test_TAB and the original test_tab have the same meaning.

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.01sec)

However, it is easy to have problems if you switch in a case-sensitive situation.

In particular, you need to pay attention to this compatibility, and if you still have doubts about stored procedures, 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, we still have to do a detailed acceptance test, and make sure that the test results prevail, because it is inappropriate for the table data to be inaccessible if the case problem occurs.

In addition, I would like to mention the case problem in Oracle. There is a default parameter sec_case_sensitive_logon in 11g in Oracle. The practical application of this parameter is really tragic. On the one hand, many customers do not like this feature and seem to regulate it from a security point of view, but it seems to bring more problems, quite a few of which are caused by habit, on the other hand, because this parameter is introduced in 11g. In 12c, this parameter expires, the meaning has changed, and there will be some ambiguity in use.

This parameter can only ensure that the login user's password is case-sensitive, but everything else is the same as before. This parameter in 12c has changed and has expired in the new version. When setting it, you must ensure that the settings in sqlnet.ora need to be compatible. If the parameter SQLNET.ALLOWED_LOGON_VERSION_SERVER in the file is 12jue 12a, you need to ensure that SEC_CASE_SENSITIVE_LOGON is not false.

This is the end of the content of "is there a difference between case and case in MySQL"? thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.