如何在MySQL中删除同表内idRef不存在的指定用户数据行

我有一张MySQL表，结构和数据如下：

需要删除所有满足以下条件的行：

• userID = 30
• userIDRef = 100
• idRef的值不存在于该表的id列中

示例里要删除的是idRef为6和8的行，因为这两个值不在id列里。我尝试了下面的SQL语句，但有问题：

DELETE FROM table
WHERE userID = 30
  AND idRef != 0
  AND idRef NOT IN (
    SELECT id
    FROM table
    WHERE userID = 100
    AND)

你的SQL存在两个问题： #

1. 子查询末尾多了一个无效的AND，属于语法错误
2. 需求是idRef不存在于整个表的id列，而你限制了子查询只取userID=100的id，逻辑不符合要求

正确的SQL语句 #

方式一：使用NOT IN

DELETE FROM `table`
WHERE userID = 30
  AND userIDRef = 100
  AND idRef NOT IN (SELECT id FROM `table`)

方式二：使用LEFT JOIN（大表场景下性能更优）

DELETE t1
FROM `table` t1
LEFT JOIN `table` t2 ON t1.idRef = t2.id
WHERE t1.userID = 30
  AND t1.userIDRef = 100
  AND t2.id IS NULL

PHP中执行的示例代码 #

PDO版本

<?php
$dsn = 'mysql:host=localhost;dbname=你的数据库名;charset=utf8mb4';
$username = '你的用户名';
$password = '你的密码';

try {
    $pdo = new PDO($dsn, $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // 选择其中一种SQL方式执行即可
    $sql = "DELETE FROM `table` WHERE userID = 30 AND userIDRef = 100 AND idRef NOT IN (SELECT id FROM `table`)";
    // $sql = "DELETE t1 FROM `table` t1 LEFT JOIN `table` t2 ON t1.idRef = t2.id WHERE t1.userID = 30 AND t1.userIDRef = 100 AND t2.id IS NULL";
    
    $stmt = $pdo->prepare($sql);
    $stmt->execute();

    echo "成功删除 " . $stmt->rowCount() . " 行";
} catch(PDOException $e) {
    echo "错误: " . $e->getMessage();
}
$pdo = null;
?>

MySQLi版本

<?php
$servername = "localhost";
$username = "你的用户名";
$password = "你的密码";
$dbname = "你的数据库名";

$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
    die("连接失败: " . $conn->connect_error);
}

// 选择其中一种SQL方式执行即可
$sql = "DELETE FROM `table` WHERE userID = 30 AND userIDRef = 100 AND idRef NOT IN (SELECT id FROM `table`)";
// $sql = "DELETE t1 FROM `table` t1 LEFT JOIN `table` t2 ON t1.idRef = t2.id WHERE t1.userID = 30 AND t1.userIDRef = 100 AND t2.id IS NULL";

if ($conn->query($sql) === TRUE) {
    echo "成功删除 " . $conn->affected_rows . " 行";
} else {
    echo "错误: " . $sql . "<br>" . $conn->error;
}

$conn->close();
?>

内容的提问来源于stack exchange，提问作者ComAssistant