將現有MySQL資料庫改為大小寫不敏感

深藍發表於2016-12-01
MySql資料庫

用過MySQL的應該都會注意到，預設情況下，Linux下安裝的MySQL是大小寫敏感的，也就是說Table1和table1可以同時存在。而Windows下的MySQL卻是大小寫不敏感的，所有表名和資料庫名都會變成小寫。

對於怎麼啟用或者停用MySQL資料庫的大小寫敏感，這個網上隨便都能找到，就是改改引數lower_case_table_names，然後重啟即可。

但是，如果我們的資料庫中已經有了多個區分大小寫的資料庫，現在要改為不區分大小寫的，那麼就會報錯：Table 'databasenamexxx.tablenamexxx' doesn't exist.

為此，我們需要將MySQL改為大小寫敏感的模式，然後去重新命名每個表名和資料庫名。

MySQL確實很神奇的一點是不允許重新命名資料庫，所以如果我們要重新命名Test1為test1，那麼只有新建一個test1的資料庫，然後把Test1中的表全面rename到test1資料庫中。

而且在rename的過程中，我們也需要將表面從大小寫的形式改為全部小寫的形式。

為了批量的做這麼一件事，與，我寫了一個儲存過程，通過讀取系統表，獲得資料庫表名，然後用遊標的方式依次執行rename操作。

DELIMITER //

CREATE PROCEDURE renametables(olddb VARCHAR(50),newdb VARCHAR(50)) 
BEGIN 
DECLARE done BOOLEAN DEFAULT 0; 
DECLARE tmp VARCHAR(100); -- 定義區域性變數

DECLARE tbcur CURSOR 
    FOR    SELECT TABLE_NAME FROM `information_schema`.`TABLES` WHERE table_schema=olddb AND Table_Type='BASE TABLE'; 
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; 
    OPEN tbcur;  -- 開啟遊標

    REPEAT 
     FETCH tbcur INTO tmp; 
     IF done=0 
     THEN 
    　　SET @sqlstring=CONCAT( 'RENAME TABLE ',olddb,'.`',tmp,'` TO ',newdb,'.`',LOWER(tmp),'`;'); 
   　　 SELECT @sqlstring; -- 這一句可以不要，只是列印我們拼接後要執行的SQL是什麼
  　　  PREPARE s1 FROM  @sqlstring; -- 執行拼接出來的SQL 
  　　  EXECUTE s1;  
 　　   DEALLOCATE PREPARE s1;    
    END IF; 
     UNTIL done END REPEAT; 
    CLOSE tbcur; -- 關閉遊標,釋放遊標使用的所有內部記憶體和資源 
    
    END//

 

   
我們在新資料庫中建立了該儲存過程，然後呼叫即可：

CALL renametables('Test1','test1')

這樣所有Test1中的大小寫混合的表，就全部轉換到了test1資料庫中，而且表名都變成了小寫了。

一個一個的資料庫去這麼做，然後再把MySQL的引數改為大小寫不敏感，這樣才能正常使用。

這裡我只是做了表的遷移，接下來儲存過程和檢視的遷移，由於不涉及到資料，所以比較簡單，找到當年的DDL或者我們在大小寫敏感的時候就匯出View和儲存過程的定義，然後用文字編輯器把整個SQL變成小寫的，然後到新資料庫中去執行，重新建立即可。

相關文章