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

   1
   2
   3 DROP PROCEDURE IF EXISTS mysql_change_charset;
   4
   5 delimiter $$
   6
   7
   8 CREATE PROCEDURE mysql_change_charset(mytb TEXT)
   9     BEGIN
  10     DECLARE mydb TEXT;
  11
  12     SELECT database() INTO mydb;
  13
  14
  15     SELECT
  16         IF(
  17             csname='utf8' AND collatename='utf8_unicode_ci',
  18             CONCAT('SELECT ''',mytb,' is Already utf8'' as \"No Need to Convert\"'),
  19             CONCAT('ALTER TABLE ',mytb,' CONVERT TO CHARACTER SET  utf8 COLLATE utf8_unicode_ci')
  20
  21         )
  22     INTO
  23         @ConvertEngineSQL
  24
  25     FROM (
  26         SELECT
  27             CCSA.character_set_name csname,
  28             CCSA.collation_name collatename
  29             FROM
  30                 information_schema.`TABLES` T,
  31                 information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA
  32             WHERE
  33                 CCSA.collation_name = T.table_collation
  34                 AND
  35                 T.table_schema = mydb COLLATE utf8_unicode_ci
  36                 AND
  37                 T.table_name = mytb COLLATE utf8_unicode_ci
  38
  39
  40     ) AA;
  41
  42     -- SELECT ConvertEngineSQL; -- ???
  43     PREPARE st FROM @ConvertEngineSQL;
  44     EXECUTE st;
  45     DEALLOCATE PREPARE st;
  46
  47 END;
  48
  49 $$
  50
  51 DELIMITER ;