php - MySQL跨表删除

Question

我正在编写一个 cron 脚本(在 MySQL 数据库的 PHP PDS 中)，它将从数据库中删除超过 30 天的数据。我尝试在我的表和语句上进行连接和删除级联，但它们没有成功，所以我在这里采用了一种更简单的方法来让它工作，但我知道这不是处理器效率最高的方法。

请注意，其中一些表(尤其是链接表)有数十万行，因此我想比这里的表更聪明一些。

表结构很简单，有一个表键表，有时间戳和id。该 id 在所有表中重复为 tablekey_id。

我当前的 cron 作业如下。

/* Cron script to delete old data from the database. */
if (@include dirname(dirname(dirname(__FILE__))) . '/dbconn/website_connections.php') {
    $conn = $connectionFromOtherFile;
    $keyTable = 'table_key';
    $linksTable = 'links';
    $domainsTable = 'link_domains';
    $terms = 'searched_domains';

    $query = $conn->query("SELECT `id` FROM `$keyTable` WHERE `timestamp` < (NOW() - INTERVAL 30 DAY)");
    $query->setFetchMode(PDO::FETCH_ASSOC);

    while($row = $query->fetch()) {
        $conn->exec("DELETE FROM `$linksTable` WHERE `tablekey_id` = $row[id]");
        $conn->exec("DELETE FROM `$domainsTable` WHERE `tablekey_id` = $row[id]");
        $conn->exec("DELETE FROM `$terms` WHERE `tablekey_id` = $row[id]");
        $conn->exec("DELETE FROM `$keyTable` WHERE `id` = $row[id]");
    }
}

有什么办法可以把它写进一个语句中吗？请并感谢您的协助。

编辑:这是我最终得到的结果。

/* Cron script to delete old data from the database. */
if (@include dirname(dirname(dirname(__FILE__))) . '/dbconn/website_connections.php') {
    $conn = $connectionFromOtherFile;
    $keyTable = 'table_key';
    $linksTable = 'links';
    $domainsTable = 'link_domains';
    $terms = 'searched_domains';
         $delete = $conn->prepare("DELETE tablekey, linktable, domaintable, searched
                            FROM `$keyTable` AS tablekey
                            LEFT OUTER JOIN `$linksTable` AS linktable on tablekey.id = linktable.tablekey_id
                            LEFT OUTER JOIN `$domainsTable` AS domaintable on tablekey.id = domaintable.tablekey_id
                            LEFT OUTER JOIN `$terms` AS searched on  tablekey.id = searched.tablekey_id
                            WHERE tablekey.id = :tablekey_id");

    $query = $conn->query("SELECT `id` FROM `$keyTable` WHERE `timestamp` < (NOW() - INTERVAL 30 DAY)");
    $query->setFetchMode(PDO::FETCH_ASSOC);

    while($row = $query->fetch()) {
        $delete->execute(array('tablekey_id' => $row['id']));
    }
}   

Answer 1

您应该能够使用这样的一个查询进行删除:

DELETE kt, lt, dt, t
FROM `$keyTable` AS kt
LEFT OUTER JOIN `$linksTable` AS lt on kt.id = lt.tablekey_id
LEFT OUTER JOIN `$domainsTable` AS dt on kt.id = dt.tablekey_id
LEFT OUTER JOIN `$terms` AS t on  kt.id = t.tablekey_id
WHERE kt.id = $row[id]

请注意，我在这里使用了外连接，因为我不确定 keyTable 以外的表是否保证包含具有该 tablekey_id 的记录。如果是，您可以使用内部联接。