Home  >  Article  >  Database  >  How to set case insensitivity in mysql

How to set case insensitivity in mysql

青灯夜游
青灯夜游Original
2022-01-06 12:00:0612316browse

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.

How to set case insensitivity in mysql

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!

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn