git.roojs.org Git - Pman.Core/blob - mysql/mysql_change_engine.sql

   1
   2
   3 DROP PROCEDURE IF EXISTS mysql_change_engine;
   4
   5 delimiter $$
   6
   7
   8 CREATE PROCEDURE mysql_change_engine(mytb TEXT)
   9     BEGIN
  10     DECLARE mydb TEXT;
  11
  12     SELECT database() INTO mydb;
  13
  14
  15     SELECT
  16         IF(
  17             dbtb2='.',
  18             CONCAT('ALTER TABLE ',dbtb1,' ENGINE=InnoDB'),
  19             CONCAT('SELECT ''',dbtb1,' is Already InnoDB'' as \"No Need to Convert\"')
  20         )
  21     INTO
  22         @ConvertEngineSQL
  23
  24     FROM (
  25         SELECT
  26             CONCAT(A.db,'.',A.tb) dbtb1,
  27             CONCAT(IFNULL(B.db,''),'.',IFNULL(B.tb,'')) dbtb2,engine
  28         FROM
  29             (
  30                 SELECT
  31                     table_schema db,table_name tb,engine
  32                 FROM
  33                     information_schema.tables
  34                 WHERE
  35                     table_schema=mydb and table_name=mytb
  36             ) A
  37         LEFT JOIN
  38             (
  39                 SELECT
  40                     table_schema db,table_name tb
  41                 FROM
  42                     information_schema.tables
  43                 WHERE
  44                     table_schema=mydb and table_name=mytb AND engine='InnoDB'
  45             ) B
  46         USING
  47             (db,tb)
  48     ) AA;
  49
  50     -- SELECT ConvertEngineSQL; -- ???
  51     PREPARE st FROM @ConvertEngineSQL;
  52     EXECUTE st;
  53     DEALLOCATE PREPARE st;
  54
  55 END;
  56
  57 $$
  58
  59 DELIMITER ;