当前位置：首页 > article >正文

MySQL批量删除字段跑路？

article 2024/12/21 12:43:34

表多，废物字段多，字段名字很像

当表里有些字段想要删掉，而且字段名字具有一定相似性，可以考虑批量删除

-- 查询所有表，生成 DROP COLUMN 的动态 SQL，批量，小心
SELECT CONCAT('ALTER TABLE `', TABLE_NAME, '` ', 
              GROUP_CONCAT('DROP COLUMN `', COLUMN_NAME, '`' SEPARATOR ', '), ';') AS drop_sql
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'impcsv'
AND COLUMN_NAME like '%XXXX%' 
GROUP BY TABLE_NAME;

--替换字段内容
SELECT CONCAT('ALTER TABLE `', TABLE_NAME, '` ', 
              GROUP_CONCAT('rename to `', replace(TABLE_NAME,'/','_'), '`' SEPARATOR ', '), ';') AS drop_sql
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME like '%XXXX%'
GROUP BY TABLE_NAME;

--重命名表名

SELECT CONCAT('ALTER TABLE `', TABLE_NAME, '` ', 
              GROUP_CONCAT('rename to `', replace(TABLE_NAME,'-','_'), '`' SEPARATOR ', '), ';') AS drop_sql
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'impcsv'
GROUP BY TABLE_NAME;

--修改表

SELECT CONCAT('ALTER TABLE `', TABLE_NAME, '` ', 
              GROUP_CONCAT('modify column`',COLUMN_NAME, '` decimal(10,2)' SEPARATOR ', '), ';') AS drop_sql
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'impcsv'
AND COLUMN_NAME like '%XXXX%' 
GROUP BY TABLE_NAME;

怎么做？代码贴进去找到自己的数据库名字，限定好表名，或者字段名，抛出来一大堆语句，把语句贴出来，放到一个查询里面，一键执行