mysql/mysql_change_engine.sql

diff --git a/mysql/mysql_change_engine.sql b/mysql/mysql_change_engine.sql
index e69de29..7611968 100644 (file)
--- 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