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

Editor to share with you how to solve the case-sensitive problem of MySQL database, I believe that most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

In MySQL, the database corresponds to the directory in the data directory. Each table in the database corresponds to at least one file in the database directory (or multiple, depending on the storage engine). Therefore, the case sensitivity of the operating system used determines the case sensitivity of database and table names. This means that database and table names are case-sensitive in most Unix and insensitive in Windows. One notable exception is Mac OS X, which is based on Unix but uses the default file system type (HFS+) and is case-insensitive.

Table names under windows are not case-sensitive, so after importing data, it is possible that all table names are lowercase, but after importing linux from win, it is easy to have case problems when calling. For example:

The first point: the program call table name is: codetc_Web

Second point: change to codetc_web after importing win

The third point: after importing linux, it is also codetc_web. In this case, linux will be case-sensitive, which will cause the table to be unreadable.

Solution to the case problem of mysql table names under linux:

Modify my.cnf, which is usually located at: / etc/my.cnf. Under the [mysqld] section of the file, add the following statement (note that if the statement already exists, please change the value to 1):

Lower_case_table_names=1

1. After the mysql is installed in Linux, it defaults to case-sensitive table names and insensitive column names.

2. After logging in with a root account, add lower_case_table_names=1 after "mysqld" in / etc/my.cnf, and restart the MYSQL service. At this time, the setting is successful: table names are not case-sensitive.

Detailed description of lower_case_table_names parameters:

Lower_case_table_names = 0 or 1

Where 0: case sensitive, 1: case insensitive

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. If you need to distinguish the case of field values when querying, you need to set the BINARY attribute for the query field values. You can set them in the following ways:

(1) set at creation time:

CREATE TABLE T (A VARCHAR (10) BINARY)

(2) use alter to modify:

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

(3) check the BINARY item directly in mysql table editor.

To make mysql queries case-sensitive, you can:

Select * from table_name where binary a like'a% 'select * from table_name where binary a like' A%'

It can also be identified when creating a table.

Create table table_name (a varchar (20) binary) above is all the contents of this article entitled "how to solve the case-sensitive problem of MySQL database". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.