创建一个存储过程以遍历表列表并在MySQL中截断它们 [英] Create a stored procedure to iterate through a list of tables and truncate them in MySQL

问题描述

我正在讨论是否尝试遍历表列表并使用存储过程将其截断. MySql会那么容易，我该怎么办?

I'm debating whether or not to try running through a list of tables and truncating them with a stored procedure. Would it be that easy with MySql and how would I do it?

推荐答案

您需要的主要信息是表列表.大多数平台都支持此功能:

The main piece of info you need is the list of tables. Most platforms support this:

select table_name from information_schema.tables

但是，在对存储过程进行编码之前，请从information_schema.tables中执行select *并检查条目，可能有些不期望的内容-系统表等，因此您可能需要制作一个过滤器以获取设置你想要的.

However, before you code the sproc, do a select * from information_schema.tables and examine the entries, there may be some you do not expect -- system tables and such, so you may need to craft a filter to get the set you want.

由于我没有做太多的MySQL，因此无法向您显示代码，但是如果您可以从MS SQL进行翻译，并填写一些空白，则可以使其工作:

Since I don't do mySQL that much, I can't show you the code, but if you can translate this from MS SQL, and fill in some blanks you can make it work:

declare @table_name varchar(200)
while 1=1 begin
    select top 1 @table_name = table_name
      from information_schema.tables
     where ....possible filter...
    if @table_name is null break

    -- for this line you may need dynamic sql
    truncate table @table_name
end    

这篇关于创建一个存储过程以遍历表列表并在MySQL中截断它们的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！