1、批量修改表前缀
# def是现在的表前缀,abc是要修改成的表前缀 substring(table_name, 5) 5需要根据实际调整 SELECT CONCAT( 'ALTER TABLE ', table_name, ' RENAME TO abc_', substring(table_name, 5), ';' ) sqls FROM information_schema.tables Where table_name LIKE 'def_%';
2、批量修改表编码
SELECT CONCAT("ALTER TABLE `", TABLE_NAME,"` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;") AS target_tables FROM INFORMATION_SCHEMA.TABLES
3、数据查重
select 字段名,count(id) as count from 数据库 group by 字段名 having count>1