mysql 统一修改字符集和字段属性

-- 修改表字符集

SELECT
CONCAT("ALTER TABLE `", TABLE_NAME,"` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;")
AS target_tables
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA="uat-zpg"
AND TABLE_TYPE="BASE TABLE";

-- 修改表字段属性
SELECT
t.table_schema AS 'Database',
t.table_name AS 'Table',
c.column_name AS 'Column',
c.character_maximum_length AS 'Length',
CONCAT("ALTER TABLE `", t.table_name,"` modify `", c.column_name,"` varchar(255);")
AS target_tables
FROM
information_schema.tables t
INNER JOIN
information_schema.columns c
ON
t.table_schema = c.table_schema
AND t.table_name = c.table_name
WHERE
t.table_schema = 'uat-zpg'
AND c.data_type = 'varchar'
and c.column_name != 'create_time' and c.column_name != 'update_time'
AND c.character_maximum_length >= 10
AND c.character_maximum_length <= 20;