考虑以下 2 个表:
Users
------
id | name
1 | David
2 | John
3 | Sam
User_Payments
-----
id | user_id | amount
1 | 1 | 100
2 | 2 | 200
User_Payments.user_id REFERENCES Users(id) ON DELETE NO ACTION
^ 这意味着 user_payments 中引用的用户可能不会被删除。
如果我们运行查询:
DELETE IGNORE FROM Users;
根据版本,MySQL将:
删除- 山姆,因为他是唯一可以安全删除的用户
- 遇到错误,不会删除任何内容
由于这与版本相关,因此我想找到一个将满足#1的查询。
我想删除任何其他表未引用的所有用户。
我已经阅读了这个问题,但我不想检查其他所有表以查看是否引用了user_id。
如果我有很多很多表,这种方法将不起作用。
users
user_payments
user_info
user_keywords
user_logs
user_etc
...
所有user_*表都将能够引用某些用户(id)。
那么,如何编写一个查询来仅删除未被任何其他表引用的用户呢?
编辑 3:这可能会有所帮助:
DELETE FROM Users WHERE
SELECT CONCAT('Users.id NOT EXISTS (SELECT ', t.table_name, '.user_id FROM t WHERE ', t.table_name, '.user_id=Users.id) AND')
FROM information_schema.tables t
WHERE t.table_name LIKE 'user_%'
AND t.table_schema = 'ENTER_DB_NAME_HERE'
INTO OUTFILE '/tmp/sqlstatement.sql';
然后(翻白眼,很抱歉),连接后,删除训练AND
并附加分号。
编辑2:下面是一个经过修订的,可能非常昂贵的提案:
DELETE IGNORE FROM Users
SELECT CONCAT('LEFT JOIN ', t.table_name, ' ON ', t.table_name, '.user_id=Users.id')
FROM information_schema.tables t
WHERE t.table_name LIKE 'user_%'
AND t.table_schema = 'ENTER_DB_NAME_HERE'
INTO OUTFILE '/tmp/sqlstatement.sql';
WHERE
SELECT CONCAT(t.table_name, '.user_id IS NULL AND')
FROM information_schema.tables t
WHERE t.table_name LIKE 'user_%'
AND t.table_schema = 'ENTER_DB_NAME_HERE'
INTO OUTFILE '/tmp/sqlstatement2.sql';
您必须手动将它们连接在一起。一定有更好的方法,对不起垃圾邮件。
编辑:抱歉,我看到这不起作用,因为您需要检查每个user_id
的所有表。
好吧,您可以使用创建存储过程,使用动态SQL和一堆CONCAT
和循环,但是,看看这篇文章,我认为以下内容可能会使您走上正确(也是最安全)的轨道:
SELECT CONCAT('DELETE IGNORE FROM Users LEFT JOIN ', t.table_name, ' ON ', t.table_name, '.user_id=Users.id WHERE ', t.table_name, '.user_id IS NULL;')
FROM information_schema.tables t
WHERE t.table_name LIKE 'user_%'
AND t.table_schema = 'ENTER_DB_NAME_HERE'
INTO OUTFILE '/tmp/sqlstatements.sql';
然后:
SOURCE /tmp/sqlstatements.sql;
执行(但请先检查文件内容)。我还没有测试过上述内容,抱歉,但希望它至少会有所帮助。比存储过程容易得多。另一种方法是在另一种语言(例如 Python)中进行循环,但我认为上述方法应该可以解决问题。