在我的数据库中有很多以_elgg开头的表,现在我想删除所有带有这个前缀的表.有谁能给我一个解决方案?
提前致谢!
解决方法:
先做一个
SELECT CONCAT('DROP TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ';')
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE '\_elgg%'
AND TABLE_SCHEMA = 'your_database_name';
并检查这是否为每个表返回正确的语句.如果要执行这些语句,请执行
SELECT CONCAT('DROP TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ';')
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE '\_elgg%'
AND TABLE_SCHEMA = 'your_database_name'
INTO OUTFILE '/tmp/whatever_filename';
SOURCE /tmp/whatever_filename;
另一种方法:在命令行上,一次急于做,你可以做到
mysql -B databasename -uroot -prootpassword --disable-column-names -e `mysql -B databasename -uroot -prootpassword --disable-column-names -e "SELECT CONCAT('DROP TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ';') FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '\_elgg%' AND TABLE_SCHEMA = 'your_database_name';"`
标签:mysql
来源: https://codeday.me/bug/20190807/1605180.html