在表之间移动一行

Question

我想将一行从posts移动到archive。两个表具有相同的列。

$st = $db->query("select * from posts where id = " . $id);
while ($row = $st->fetch()){
    $date = $row['date']; ...
$sql = "insert into archive (date,...) values (:adate,...)";
$st = $db->prepare($sql);
$st->execute(array(
    ":adate" => $date, ...
$st = $db->query("delete from posts where id = " . $id);

id列在两个表上都是自动递增的。

有没有更短的方法可以做到这一点，因为每个表上有 14 列？

Answer 1

只要列的类型相同且顺序相同，就可以执行insert into archive select * from posts where id = :id .但是，这将插入到具有相同 id 的archive中。

    MariaDB [temp]> select * from posts;
    +------+-------+-------+
    | id   | a     | b     |
    +------+-------+-------+
    |    2 | test3 | test4 |
    |    1 | test  | test2 |
    +------+-------+-------+
    2 rows in set (0.00 sec)
    MariaDB [temp]> select * from archive;
    Empty set (0.00 sec)
    MariaDB [temp]> insert into archive select * from posts where id = 2;
    Query OK, 1 row affected (0.05 sec)
    Records: 1  Duplicates: 0  Warnings: 0
    MariaDB [temp]> select * from archive;
    +------+-------+-------+
    | id   | a     | b     |
    +------+-------+-------+
    |    2 | test3 | test4 |
    +------+-------+-------+
    1 row in set (0.01 sec)
    MariaDB [temp]> 

如果要让id列正常自动递增，则必须选择每列，例如insert into archive (date,...) select (date,...) from posts where id = :id

    MariaDB [temp]> select * from posts;
    +------+------+-------+
    | id   | a    | b     |
    +------+------+-------+
    |    1 | test | test2 |
    +------+------+-------+
    1 row in set (0.00 sec)
    MariaDB [temp]> select * from archive;
    +----+-------+-------+
    | id | a     | b     |
    +----+-------+-------+
    |  2 | test3 | test4 |
    +----+-------+-------+
    1 row in set (0.00 sec)
    MariaDB [temp]> insert into archive (a, b) select a, b from posts where id = 1;
    Query OK, 1 row affected (0.02 sec)
    Records: 1  Duplicates: 0  Warnings: 0
    MariaDB [temp]> select * from archive;
    +----+-------+-------+
    | id | a     | b     |
    +----+-------+-------+
    |  2 | test3 | test4 |
    |  3 | test  | test2 |
    +----+-------+-------+
    2 rows in set (0.00 sec)
    MariaDB [temp]> 

Answer 2

实际上，我建议您使用以下方法：

$ids = array(3, 7, 15, 31, 45);
$clause = implode(',', array_fill(0, count($ids), '?'));
$stmt = $mysqli->prepare('INSERT INTO Archive SELECT Field1, Field2, Field3 FROM Posts WHERE `id` IN ('.$clause.');');
call_user_func_array(array($stmt, 'bind_param'), $ids);
$stmt->execute();
$stmt = $mysqli->prepare('DELETE FROM Posts WHERE `id` IN ('.$clause.');');
call_user_func_array(array($stmt, 'bind_param'), $ids);
$stmt->execute();

IN 语句允许您避免对每个 ID 使用单个查询，而 INSERT INTO 语句允许您避免对每次插入执行选择。总体而言，应提高过程的性能。

最重要的是，我建议您在TRANSACTION内执行所有这些操作，以便在两个查询中的一个失败或服务器遇到问题时，您的表就不会搞砸。