mysql 操作数据所有表

93 阅读1分钟

所有的表都增加字段:

-- table_schema 指定数据库
SELECT table_name,substr(table_name,1,3),
CONCAT('alter table ' ,table_name ,' add update_user varchar(50);') as f
FROM information_schema.tables
WHERE table_schema = 'gzgs_202230920' ;

所有的表修改编码:

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="bladex_laster" 
and TABLE_NAME not like 'act%'
and TABLE_NAME not like 'view%'
and TABLE_NAME not like 'lab%'