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