有没有办法在 MySQL 模式中截断大多数表? [英] Is there a way to TRUNCATE most tables in a MySQL schema?

问题描述

我正在寻找一个查询(或一系列)来截断我的架构(有几百个表)中的所有表，除了 4 个特定的表.我该怎么做呢?谢谢！

I'm looking for a query (or series of) to TRUNCATE all tables in my schema (which has a few hundred tables) EXCEPT for a 4 specific ones. How might I go about doing that? Thanks!

推荐答案

我相信您必须使用您最喜欢的任何语言编写脚本.您可以从 information_schema 数据库中获取架构中的表列表，然后遍历它们，截断您想要的任何表.

I believe you'll have to write a script in whatever language you like the most. You can get a list of the tables in the schema from the information_schema db, then iterate over them, truncating any that you feel like.

查询类似于:

SELECT table_name FROM information_schema.tables WHERE table_schema = 'test' AND table_name NOT IN ('table1', 'table2');

编辑:这是一个使用 Perl 的示例:

Edit: Here's an example using Perl:

use strict;
use warnings;
use DBI;

my $dbh = DBI->connect("some_dsn");

my $sth = $dbh->prepare(q{SELECT table_name FROM information_schema.tables WHERE table_schema = 'test' AND table_name NOT IN ('table1', 'table2')});
$sth->execute();
$sth->bind_columns(\my $table_name);

while($sth->fetch) { $dbh->do(q{TRUNCATE TABLE } . $table_name) }

这篇关于有没有办法在 MySQL 模式中截断大多数表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！