在MySQL中模拟DELETE CASCADE？

Question

是否有可能自动预测跟随DELETE CASCADE的操作？在我的软件中，我希望向用户发出警告，并提供有关将要被删除数据的详细信息。

- user694971

---

好问题，我也很感兴趣得到答案。 - Alp

我目前能想到的唯一合理的方法是 SHOW FULL COLUMNS FROM <table_name> 然后遍历相应的表...（硬编码 fkey_names => table_names...） - user694971

Answer 1

您可以复制数据库并在删除后添加触发器。

DELIMITER $$

CREATE TRIGGER ad_table1_each AFTER DELETE ON table1 FOR EACH ROW
BEGIN
  INSERT INTO log VALUES (null                 /*autoinc id*/
        , 'table1'                             /*tablename*/
        , old.id                               /*tableid*/
        , concat_ws(',',old.field1,old.field2  /*CSV's of fields*/
        , NOW()                                /*timestamp*/
        , 'delete');                           /*what action*/


  REPLACE INTO restore_table1 VALUES (old.id,
        , old.field1
        , old.field2
        , ... );

END $$

DELIMITER ;

日志表只是包含以下字段的表格：

id            integer autoincrement primary key
tablename     varchar(45)
table_id      integer
fields        varchar(6000)
delete_time   timestamp
action        enum('insert','update','delete')

如果在复制数据库上的级联删除之前执行SELECT @last_id:= max(id) FROM log，
那么您可以执行SELECT * FROM log WHERE id > @last_id
并获取将在级联中删除的所有行。

然后，您可以使用restore_table1在复制数据库中重新创建在级联中被删除的行。

- Johan

Answer 2

我认为你可以使用Johan的触发器解决方案，结合回滚事务。这样既避免了需要第二个数据库，也避免了手动恢复已删除的条目。

• 添加触发器和日志表
• 对于每个尝试删除的条目，启动一个事务并删除条目
• 向用户呈现来自日志的信息以供批准
• 如果用户同意，则提交事务，否则回滚

- Stefan

---

唯一的问题是，如果您登录的引擎也支持事务，则日志记录也将被回滚，因此您需要使用非事务性引擎进行日志记录，例如MyISAM。 - Johan

事务的想法很好。实际上，我既不会使用触发器也不会使用日志来实现它。我只会模拟删除，然后回滚。 - user694971

@user694971: 我觉得如果你想向用户展示已删除的条目，你需要日志。如果没有日志，你只能显示剩余的条目，除非你的应用逻辑可以解决这个问题。 - Stefan

Answer 3

我用PHP编写了一个非常快速的hack，完全可以满足您的需求，因为我想做同样的事情但在网上没有找到任何资源。

也许对于您来说已经太晚了，但这可能会帮助其他人。

function get_referencing_foreign_keys ($database, $table) {
    $query = 'SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, REFERENCED_COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_SCHEMA = "'.$database.'" AND REFERENCED_TABLE_NAME = '.esc($table);
    $result = rquery($query);
    $foreign_keys = array();
    while ($row = mysql_fetch_row($result)) {
        $foreign_keys[] = array('database' => $row[0], 'table' => $row[1], 'column' => $row[2], 'reference_column' => $row[3]);
    }

    return $foreign_keys;
}

function get_foreign_key_deleted_data_html ($database, $table, $where) {
    $data = get_foreign_key_deleted_data ($database, $table, $where);

    $html = '';
    foreach ($data as $key => $this_data) {
        $html .= "<h2>$key</h2>\n";

        $html .= "<table>\n";
        $i = 0;
        foreach ($this_data as $value) {
            if($i == 0) {
                $html .= "\t<tr>\n";
                foreach ($value as $column => $column_value) {
                    $html .= "\t\t<th>".htmlentities($column)."</th>\n";
                }
                $html .= "\t</tr>\n";
            }
            $html .= "\t<tr>\n";
            foreach ($value as $column => $column_value) {
                $html .= "\t\t<td>".htmlentities($column_value)."</td>\n";
            }
            $html .= "\t</tr>\n";
            $i++;
        }
        $html .= "</table>\n";
    }

    return $html;
}

function get_foreign_key_deleted_data ($database, $table, $where) {
    $GLOBALS['get_data_that_would_be_deleted'] = array();
    $data = get_data_that_would_be_deleted($database, $table, $where);
    $GLOBALS['get_data_that_would_be_deleted'] = array();
    return $data;
}

function get_data_that_would_be_deleted ($database, $table, $where, $recursion = 100) {
    if($recursion <= 0) {
        die("Deep recursion!");
    }

    if($recursion == 100) {
        $GLOBALS['get_data_that_would_be_deleted'] = array();
    }

    if($table) {
        if(is_array($where)) {
            $foreign_keys = get_referencing_foreign_keys($database, $table);
            $data = array();

            $query = 'SELECT * FROM `'.$table.'`';
            if(count($where)) {
                $query .= ' WHERE 1';
                foreach ($where as $name => $value) {
                    $query .= " AND `$name` = ".esc($value);
                }
            }
            $result = rquery($query);

            $to_check = array();

            while ($row = mysql_fetch_row($result)) {
                $new_row = array();
                $i = 0;
                foreach ($row as $this_row) {
                    $field_info = mysql_fetch_field($result, $i);
                    $new_row[$field_info->name] = $this_row;
                    foreach ($foreign_keys as $this_foreign_key) {
                        if($this_foreign_key['reference_column'] == $field_info->name) {
                            $to_check[] = array('value' => $this_row, 'foreign_key' => array('table' => $this_foreign_key['table'], 'column' => $this_foreign_key['column'], 'database' => $this_foreign_key['database']));
                        }
                    }
                    $i++;
                }
                $GLOBALS['get_data_that_would_be_deleted'][$table][] = $new_row;
            }
            foreach ($to_check as $this_to_check) {
                if(isset($this_to_check['value']) && !is_null($this_to_check['value'])) {
                    get_data_that_would_be_deleted($database, $this_to_check['foreign_key']['table'], array($this_to_check['foreign_key']['column'] => $this_to_check['value']), $recursion - 1);;
                }
            }

            $data = $GLOBALS['get_data_that_would_be_deleted'];

            return $data;
        } else {
            die("\$where needs to be an array with column_name => value pairs");
        }
    } else {
        die("\$table was not defined!");
    }
}

假设我在名为“db”的数据库中有一个名为“table”的表格，并且我想删除ID为180的数据，那么我会调用以下命令：

print(get_foreign_key_deleted_data_html('db', 'table', array('id' => 180)));

它打印了一个完整的表格，其中包含所有将被删除的行和所有值。

但正如我所说，这是一个非常快速且不太规范的hack。如果有任何错误报告，我会很高兴（肯定有很多！）。

- Norman