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

Case problems in MySQL

1.MySQL case-sensitive rules

In MySQL, a library will correspond to a folder, and the tables in the library will be stored in the folder as files, so the sensitivity of the operating system to case determines the case sensitivity of the database and tables (MySQL has a read-only system variable lower_case_file_system, whose value reflects whether the current file system is case-sensitive) therefore: under Windows, the database and table names of Mysql are case-insensitive. In most types of Unix systems, it is case-sensitive.

Here are the detailed case-sensitive rules for MySQL:

Under Linux:

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

Under Windows:

All are case-insensitive

Add:

There is a system variable in 1.MySQL: lower_case_table_names, which is specifically used to configure whether the database name is case-sensitive from the table name. If your system is Windows and you want mysql to be case-sensitive, you can add the following section to the my.ini file at the end of the [mysqld] section:

[plain] view plain copy#If set to 0, table names are stored as specified and comparisons are case sensitive.#If set to 1, table names are stored in lowercase on disk and comparisons are not case sensitive.#If set to 2, table names are stored as given but compared in lowercase. This option also applies to database names and table aliases.lower_case_table_names=0

2.MySQL is case-insensitive when querying strings. If a field value needs to be case-sensitive, you can specify the BINARY property when defining the field.

two。 A naming convention for MySQL

To avoid uppercase and lowercase problems, a recommended naming rule is to define databases, tables, and columns with lowercase letters and underscores, without any uppercase letters.

3. On the automatic Mapping of schema in Hibernate/JPA Database

Projects that use Hibernate/JPA can automatically generate the schema of the database. Because java has its own naming rules for classes and fields, which is not consistent with the naming rules recommended by mysql, the question of which naming convention for follow arises. Generally speaking, this will be determined by the team and DBA. Naming conventions that use mysql will not cause case problems and may be more friendly to DBA, while naming conventions using java will undoubtedly be more friendly to developers, which will save the mapping configuration of column names and make it easier for developers to view the database. If your project uses the latter, you'd better set up lower_case_table_names=0 as mentioned above.

The letter case rules in MySQL statements vary from statement element to statement element, depending on the operating system on the MySQL server host.

SQL keyword and function name

Keywords and function names are not case-sensitive. Such as, abs, bin, now, version, floor functions, SELECT, WHERE, ORDER, GROUP BY and other keywords.

Names of databases, datasheets, and views

On the server host, MySQL databases and data tables are represented by directories and files in the underlying file system. So the default case of database and data table names depends on the file name rules of the operating system on the server host. Windows filenames are not case-sensitive, so MySQL servers running on windows hosts do not distinguish between database and datasheet names. The MySQL server running on the Unix\ Linux host distinguishes the case of database and data table names.

A file is used in MySQL to represent an attempt, so the view also conforms to the above rules.

The name of the stored program

The names of stored functions, stored procedures, and events are not case-sensitive. The name of the trigger should be case sensitive.

Names of data columns and indexes

The names of data columns and indexes are not case-sensitive in MySQL environments. Examples are as follows:

The case of MySQL is not a big problem, but if you don't understand it, it will confuse users, such as the following

Sql code

Insert into t values ('A')

Insert into t values ('a')

Occurs when the second item is executed, if it is a primary key or has a uniqueness constraint

Sql code

Duplicate entry for XXX

What is even more confusing is the following two statements

Java code

Insert into t values ('~')

Insert into t values ('y')

Insertion errors can also occur.

The same problem will occur when querying.

Java code

/ * A glance at the result of the query * /

Select * from t where a like'a%'

Select * from t where a like'A%'

/ * the result of the following query is the same * /

Select * from t where a ='y'

Select * from t where a =''~

1. The cause of the problem

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

MySQL is not case-sensitive under Windows.

MySQL is case-insensitive when querying strings, and ISO-8859 character set is generally used as the default character set when compiling MySQL. This character set is not case-sensitive, so this phenomenon is caused by the case conversion of Chinese coded characters in the process of comparison.

two。 Solution.

a. Table names are case sensitive

Join in my.conf:

Ower_case_table_names = 0

Where 0: case sensitive, 1: case insensitive

b. Case sensitive when inserting a query

The BINARY property needs to be set for the field value to be case sensitive.

There are several ways to set it:

Settings at creation time:

CREATE TABLE T (

A VARCHAR (10) BINARY

);

Use alter to modify:

ALTER TABLE `tablename` MODIFY COLUMN `cloname` VARCHAR (45) BINARY

Check the BINARY item directly in mysql table editor.

Five Solutions to the case problem of MySQL string

The case of MySQL strings often makes us trapped in the case of registration names when registering websites. The reason for this problem is that MySQL is case-insensitive when querying strings, and generally uses the ISO-8859 character set as the default character set when compiling MySQL. This character set is not sensitive to the case of MySQL strings, so the case conversion of Chinese encoded characters in the comparison process causes the case problem of MySQL strings.

Method 1:

The solution is to add the "binary" attribute to the fields that contain Chinese and make them as binary comparisons, such as changing "name char (10)" to "name char (10) binary".

Method 2:

If you compile MySQL with source code, you can compile MySQL with the-- with--charset=gbk parameter, so that MySQL will directly support Chinese search and sorting.

Method 3:

You can use the locate function of Mysql to determine. Taking the above questions as an example, the usage is as follows:

SELECT * FROM table WHERE locate (field,' Li') > 0

Method 4:

Change your Select statement to this: SELECT * FROM TABLE WHERE FIELDS LIKE BINARY'% FIND%'!

Method 5:

If you use the compiled MySQL under Windows, you can modify the character set option in My.ini.

Default-character-set = gb2312

If your own server uses the win system, method five is the best way! If it is a linux system, you can use method 2! If it is not your own machine, you can use method 1, so that your user name is strictly case-sensitive!

About Me

. .

● this article is sorted out from the network

● article is updated synchronously on itpub (http://blog.itpub.net/26736162/abstract/1/), blog Park (http://www.cnblogs.com/lhrbest) and personal Wechat official account (xiaomaimiaolhr).

● article itpub address: http://blog.itpub.net/26736162/abstract/1/

● article blog park address: http://www.cnblogs.com/lhrbest

● pdf version of this article, personal introduction and wheat seedling cloud disk address: http://blog.itpub.net/26736162/viewspace-1624453/

● database written examination interview questions database and answers: http://blog.itpub.net/26736162/viewspace-2134706/

● DBA Baodian Jinri Toutiao address: http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826

. .

● QQ group: 230161599 WeChat group: private chat

● contact me, please add QQ friend (646634621), indicate the reason for adding

● completed in Mordor from 09:00 on 2017-07-01 to 22:00 on 2017-07-31.

The content of the ● article comes from the study notes of wheat seedlings, and some of it is sorted out from the Internet. Please forgive me if there is any infringement or improper place.

Copyright ● all rights reserved, welcome to share this article, please reserve the source for reprint

. .

The micro store of ● wheat seedlings: https://weidian.com/s/793741433?wfr=c&ifr=shopdetail

A series of database books published by ● wheat seedlings: http://blog.itpub.net/26736162/viewspace-2142121/

. .

Use Wechat client to scan the following QR code to follow the Wechat official account (xiaomaimiaolhr) and QQ group (DBA treasure book) of wheat seedlings and learn the most practical database technology.

The Wechat official account of wheat seedlings, the QQ group of wheat seedlings, the micro shop of wheat seedlings.

. .

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.