我有一个包含100多个表的数据库,其中约有20个表拥有一个特定的列,例如 column1
。
我能否在不指定每个表的情况下,删除所有表中column1="abc"
的行?
因此,我需要类似于以下内容的东西:
DELETE FROM [all tables] WHERE column1 = 'abc';
SELECT
CONCAT('DELETE FROM ',TABLE_NAME," WHERE column1 = 'abc';") comd
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'YOUR_DATABASE_NAME'
AND COLUMN_NAME ='column1';
这个查询将会给你以下的输出:
DELETE FROM TABLE_1 WHERE column1 = 'abc';
DELETE FROM TABLE_2 WHERE column1 = 'abc';
DELETE FROM TABLE_3 WHERE column1 = 'abc';
DELETE FROM TABLE_4 WHERE column1 = 'abc';
.
.
.
DELETE
命令并全部执行。
prepare语句
将这些生成的命令字符串转换为可执行的命令/查询。
information_schema
来获取具有column1
的表。 - Juan Carlos Oropeza