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

This article will explain in detail how to set the case insensitive to mysql, and the content of the article is of high quality, so the editor will share it with you for reference. I hope you will have some understanding of the relevant knowledge after reading this article.

Mysql sets case-insensitive methods:

1. Open and edit the "my.cnf" file

2. Look for the "lower_case_table_names" item and set the value of the item to "1"

3. Restart mysql.

The operating environment of this tutorial: linux5.9.8 system, mysql8 version, Dell G3 computer.

I. principle and parameters

Mysql case-sensitive configuration is related to two parameters-- lower_case_file_system and lower_case_table_names

View the case-sensitive configuration of the current mysql

Show global variables like'% case%' +-+-+ | Variable_name | Value | +-+-+ | lower_case_file_system | ON | | lower_case_table_names | 0 | +- -+-+

Lower_case_file_system: indicates whether the current system file is case-sensitive (ON is insensitive, OFF is sensitive), read-only parameters, and cannot be modified.

Lower_case_table_names: indicates whether the table name is case-sensitive and can be modified.

When lower_case_table_names = 0, mysql operates directly according to the table name and is case-sensitive.

When lower_case_table_names = 1, mysql changes the table name to lowercase before performing the operation.

II. Preparation before revision

Since lower_case_table_names = 1, mysql will first convert the table name to lowercase. If the table name is uppercase when the table is created, it will not be found after setting case insensitivity. It feels like Oracle creates a table to write create table "test" and then uses select * from test to find it, because Oracle automatically converts test to uppercase.

So we need to change all the table names in the library to lowercase first. There is no built-in method in mysql, so you can write a stored procedure to modify it.

DELIMITER / / DROP PROCEDURE IF EXISTS lowercase / / CREATE PROCEDURE lowercase (IN dbname VARCHAR) BEGIN DECLARE done INT DEFAULT 0; DECLARE oldname VARCHAR; DECLARE cur CURSOR FOR SELECT table_name FROM information_schema.TABLES WHERE table_schema = dbname; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; OPEN cur; REPEAT FETCH cur INTO oldname; SET @ newname = LOWER (oldname); # IF newname equals to oldname, do nothing; # select 'a'A' -> 0 # select'a 'BINARY' Aids;-> 1 SET @ isNotSame = @ newname BINARY oldname; IF NOT done & & @ isNotSame THEN SET @ SQL = CONCAT ('rename table', oldname,' to', @ newname); PREPARE tmpstmt FROM @ SQL; EXECUTE tmpstmt; DEALLOCATE PREPARE tmpstmt; END IF; UNTIL done END REPEAT; CLOSE cur; END / / DELIMITER; # call stored procedure # call lowercase ('TEST') # TEST is the name of the database you want to modify

III. Formal setting

Vi my.cnf file, which sets lower_case_table_names = 1. It takes effect when mysql is restarted.

Vi my.cnf# add lower_case_table_names = 1 on how to set case insensitive mysql to share here, I hope the above content can be of some help to you, you can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.