由于lower_case_table_names = 1時，mysql會先把表名轉為小寫，如果建表時表名是大寫的，設置完大小寫不敏感后就會查不到。感覺類似Oracle建表寫create table "test" 之后用select * from test就查不到，因為Oracle自動把test轉成了大寫。
所以我們需要先把庫里所有表名改為小寫。mysql中沒有自帶方法，可以寫個存儲過程修改。
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 ;
