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_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.
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 'a' <> 'A'; -> 0 #select 'a' <> BINARY 'A'; -> 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 lowercase('TEST'); #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!