How to set case insensitivity in mysql

How to set case insensitivity in mysql: 1. Open and edit the "my.cnf" file; 2. Find the "lower_case_table_names" item and set the value of the item to "1"; 3. Restart mysql That’s it.

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

1. Principles and parameters

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

##View the current mysql Case-sensitive configuration

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 means insensitive, OFF means sensitive), a read-only parameter that cannot be modified.

• lower_case_table_names: Indicates whether the table name is case-sensitive and can be modified.
  • lower_case_table_names = 0, mysql will operate directly based on the table name and is case sensitive.

  • lower_case_table_names = 1, mysql will first convert the table name to lowercase before performing the operation.

2. Preparation before modification

Because

lower_case_table_names = 1, mysql will first convert the table name to lowercase , if the table name is in uppercase when creating the table, it will not be found after setting case insensitivity. It feels like Oracle creates a table and writes 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 table names in the library to lowercase first. There is no built-in method in mysql, you can write a stored procedure to modify it.

DELIMITER //  
    
DROP PROCEDURE IF EXISTS lowercase //  
CREATE PROCEDURE lowercase(IN dbname VARCHAR(200))  
BEGIN     
DECLARE done INT DEFAULT 0;  
DECLARE oldname VARCHAR(200);  
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 &#39;a&#39; <> &#39;A&#39;; -> 0  
#select &#39;a&#39; <> BINARY &#39;A&#39;; -> 1  
SET @isNotSame = @newname <> BINARY oldname;  
IF NOT done && @isNotSame THEN 
SET @SQL = CONCAT(&#39;rename table &#39;,oldname,&#39; to &#39;,@newname);
PREPARE tmpstmt FROM @SQL;      
EXECUTE tmpstmt;      
DEALLOCATE PREPARE tmpstmt;      
END IF;      
UNTIL done END REPEAT;      
CLOSE cur;     
END //      
DELIMITER ;
 
#调用存储过程 
#call lowercase(&#39;TEST&#39;);
#TEST为你想要修改的数据库的名称

3. Formal settings

vi my.cnf file, set lower_case_table_names = 1,

It will take effect after restarting mysql

vi my.cnf
#添加
lower_case_table_names = 1

[Related recommendations:

mysql video tutorial】

The above is the detailed content of How to set case insensitivity in mysql. For more information, please follow other related articles on the PHP Chinese website!