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

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