如何遍历数据库中的所有表以更新列 [英] How to loop through all the tables on a database to update columns

问题描述

我正在尝试更新数据库中大多数表上存在的列(在这种情况下为日期).不幸的是，我的数据库已经创建了100多个表，并且充满了信息.有什么办法可以遍历它们，而只需使用:

I'm trying to update a column (in this case, a date) that is present on most of the tables on my database. Sadly, my database has more than 100 tables already created and full of information. Is there any way to loop through them and just use:

UPDATE SET date = '2016-04-20' WHERE name = 'Example'

在循环上?

推荐答案

一个简单的选择是创建一个查询，该查询生成要在所有表上运行的UPDATE语句:

One painless option would be to create a query which generates the UPDATE statements you want to run on all the tables:

SELECT CONCAT('UPDATE ', a.table_name, ' SET date = "2016-04-20" WHERE name = "Example";')
FROM information_schema.tables a
WHERE a.table_schema = 'YourDBNameHere'

您可以复制此查询的输出，将其粘贴到查询编辑器中，然后运行它.

You can copy the output from this query, paste it in the query editor, and run it.

更新:

正如@PaulSpiegel指出的那样，如果使用诸如HeidiSQL之类的编辑器，上述解决方案可能会很不方便，因为它将需要手动复制结果集中的每条记录.使用GROUP_CONCAT()技巧将给出一个字符串，其中包含每个所需的UPDATE查询:

As @PaulSpiegel pointed out, the above solution might be inconvenient if one be using an editor such as HeidiSQL, because it would require manually copying each record in the result set. Employing a trick using GROUP_CONCAT() would give a single string containing every desired UPDATE query in it:

SELECT GROUP_CONCAT(t.query SEPARATOR '; ')
FROM
(
    SELECT CONCAT('UPDATE ', a.table_name,
                  ' SET date = "2016-04-20" WHERE name = "Example";') AS query,
        '1' AS id
    FROM information_schema.tables a
    WHERE a.table_schema = 'YourDBNameHere'
) t
GROUP BY t.id

这篇关于如何遍历数据库中的所有表以更新列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！