From 96507eac408ab42806e07a3947f64972471f2bc1 Mon Sep 17 00:00:00 2001 From: Alan Knowles Date: Thu, 18 Jun 2015 14:00:24 +0800 Subject: [PATCH] mysql/mysql_change_engine.sql --- mysql/mysql_change_engine.sql | 51 +++++++++++++++++++++++++++++++++++ 1 file changed, 51 insertions(+) diff --git a/mysql/mysql_change_engine.sql b/mysql/mysql_change_engine.sql index e69de29b..76119685 100644 --- a/mysql/mysql_change_engine.sql +++ b/mysql/mysql_change_engine.sql @@ -0,0 +1,51 @@ + + +DROP PROCEDURE IF EXISTS mysql_change_engine; + +CREATE PROCEDURE mysql_change_engine(mytb TEXT) + BEGIN + DECLARE mydb TEXT; + DECLARE ConvertEngineSQL TEXT; + + SELECT database() INTO mydb; + + + SELECT IF( + dbtb2='.', + CONCAT('ALTER TABLE ',dbtb1,' ENGINE=InnoDB'), + CONCAT('SELECT ''',dbtb1,' is Already InnoDB'' as \"No Need to Convert\"') + ) + INTO + ConvertEngineSQL + + FROM ( + SELECT + CONCAT(A.db,'.',A.tb) dbtb1, + CONCAT(IFNULL(B.db,''),'.',IFNULL(B.tb,'')) dbtb2,engine + FROM + ( + SELECT + table_schema db,table_name tb,engine + FROM + information_schema.tables + WHERE + table_schema=mydb and table_name=mytb + ) A + LEFT JOIN + ( + SELECT + table_schema db,table_name tb + FROM + information_schema.tables + WHERE + table_schema=mydb and table_name=mytb AND engine='InnoDB' + ) B + USING + (db,tb) + ) AA; + + //SELECT ConvertEngineSQL; -- ??? + PREPARE st FROM ConvertEngineSQL; + EXECUTE st; + DEALLOCATE PREPARE st; + \ No newline at end of file -- 2.39.2