MySQL 带有内部连接的 SQL 删除
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8598791/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL DELETE with INNER JOIN
提问by JoinOG
There are 2 tables, spawnlistand npc, and I need to delete data from spawnlsit.
npc_templateid = n.idTemplateis the only thing that "connect" the tables.
I have tried this script but it doesn't work.
有 2 个表spawnlist和npc，我需要从spawnlsit.
npc_templateid = n.idTemplate是唯一“连接”表格的东西。我试过这个脚本，但它不起作用。
I have tried this:
我试过这个：
DELETE s FROM spawnlist s
INNER JOIN npc n ON s.npc_templateid = n.idTemplate
WHERE (n.type = "monster");
回答by ThinkingStiff
Add .*to sin your first line.
添加.*到s您的第一行。
Try:
尝试：
DELETE s.* FROM spawnlist s
INNER JOIN npc n ON s.npc_templateid = n.idTemplate
WHERE (n.type = "monster");
回答by Dan
If the database is InnoDB then it might be a better idea to use foreign keys and cascade on delete, this would do what you want and also result in no redundant data being stored.
如果数据库是 InnoDB，那么使用外键和级联删除可能是一个更好的主意，这将满足您的需求，并且不会存储冗余数据。
For this example however I don't think you need the first s:
但是对于这个例子，我认为你不需要第一个：
DELETE s
FROM spawnlist AS s
INNER JOIN npc AS n ON s.npc_templateid = n.idTemplate
WHERE n.type = "monster";
It might be a better idea to select the rows before deleting so you are sure your deleting what you wish to:
在删除之前选择行可能是一个更好的主意，以便您确定删除您想要的内容：
SELECT * FROM spawnlist
INNER JOIN npc ON spawnlist.npc_templateid = npc.idTemplate
WHERE npc.type = "monster";
You can also check the MySQL delete syntax here: http://dev.mysql.com/doc/refman/5.0/en/delete.html
您还可以在此处检查 MySQL 删除语法：http: //dev.mysql.com/doc/refman/5.0/en/delete.html
回答by Aylian Craspa
if the database is InnoDB you dont need to do joins in deletion. only
如果数据库是 InnoDB，则不需要在删除时进行连接。只要
DELETE FROM spawnlist WHERE spawnlist.type = "monster";
can be used to delete the all the records that linked with foreign keys in other tables, to do that you have to first linked your tables in design time.
可用于删除与其他表中的外键链接的所有记录，为此您必须首先在设计时链接您的表。
CREATE TABLE IF NOT EXIST spawnlist (
npc_templateid VARCHAR(20) NOT NULL PRIMARY KEY
)ENGINE=InnoDB;
CREATE TABLE IF NOT EXIST npc (
idTemplate VARCHAR(20) NOT NULL,
FOREIGN KEY (idTemplate) REFERENCES spawnlist(npc_templateid) ON DELETE CASCADE
)ENGINE=InnoDB;
if you uses MyISAM you can delete records joining like this
如果您使用 MyISAM，您可以删除像这样加入的记录
DELETE a,b
FROM `spawnlist` a
JOIN `npc` b
ON a.`npc_templateid` = b.`idTemplate`
WHERE a.`type` = 'monster';
in first line i have initialized the two temp tables for delet the record, in second line i have assigned the existance table to both a and b but here i have linked both tables together with join keyword, and i have matched the primary and foreign key for both tables that make link, in last line i have filtered the record by field to delete.
在第一行中，我初始化了两个临时表以删除记录，在第二行中，我将存在表分配给了 a 和 b，但在这里我用 join 关键字将两个表链接在一起，并且匹配了主键和外键对于创建链接的两个表，在最后一行我已按字段过滤要删除的记录。
