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 COLLATE utf8_unicode_ci
  45                     AND
  46                     table_name=mytb COLLATE utf8_unicode_ci
  47                     AND engine='InnoDB'
  48             ) B
  49         USING
  50             (db,tb)
  51     ) AA;
  52
  53     -- SELECT ConvertEngineSQL; -- ???
  54     PREPARE st FROM @ConvertEngineSQL;
  55     EXECUTE st;
  56     DEALLOCATE PREPARE st;
  57
  58 END;
  59
  60 $$
  61
  62 DELIMITER ;