1.修改表字段字符集
語句：
ALTER TABLE {table_name} CHANGE {field} {field} {field_type} CHARACTER SET {character_name} [other_attribute];
導(dǎo)出 sql：
SELECT CONCAT('ALTER TABLE `',TABLE_SCHEMA,'`.`',TABLE_NAME,'` CHANGE `',column_name,'` `',
column_name,'` ',
COLUMN_TYPE,
' CHARSET utf8mb4 COLLATE utf8mb4_general_ci ',IF(IS_NULLABLE = 'NO', 'NOT NULL', 'NULL'),
' ',
IF(ISNULL(COLUMN_DEFAULT), '', CONCAT('default \'', COLUMN_DEFAULT, "'")),
';') AS sqltext
FROM `information_schema`.`COLUMNS` WHERE table_schema = '數(shù)據(jù)庫'
AND (CHARACTER_SET_NAME = 'utf8'
OR COLLATION_NAME = 'utf8mb4_0900_ai_ci'
or COLLATION_NAME = 'utf8mb4_unicode_ci');
2.修改表字符集
語句：
ALTER TABLE {table_name} DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
搜索數(shù)據(jù)庫表 sql：
SELECT TABLE_NAME from information_schema.`TABLES` WHERE TABLE_SCHEMA = '數(shù)據(jù)庫';
導(dǎo)出 sql:
SELECT CONCAT('ALTER TABLE ',TABLE_NAME,' DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;')
from information_schema.`TABLES` WHERE TABLE_SCHEMA = '數(shù)據(jù)庫';
3.修改表引擎
語句：
ALTER TABLE {table_name} ENGINE=InnoDB;
搜索數(shù)據(jù)庫表 sql：
SELECT TABLE_NAME,
ENGINE
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = '數(shù)據(jù)庫' AND ENGINE = 'myISAM';
導(dǎo)出 sql:
SELECT CONCAT('ALTER TABLE ',TABLE_NAME,' ENGINE=InnoDB;')
from information_schema.`TABLES` WHERE TABLE_SCHEMA = '數(shù)據(jù)庫' AND ENGINE = 'myISAM';
