MySql - 从带有数据的现有表创建表和中间表

Question

我有一个购买表，其中存储与购买相关的所有商品。我正在重新设计表格等，以更好地构建我的表格设计，我相信我需要一个购买表并将其转换为购买、交易和purchase_transactions(中间表)。

我的购买表的简要说明是

columns related to the purchase item | columns related to the transaction

每笔交易都可以附加多个购买项目，因此如果一次购买有多个项目，则会出现大量冗余交易信息。此外，当我处理退款或类似情况时，我必须覆盖当前交易信息，这样历史记录就会丢失。

我的问题是，有没有办法编写一个脚本来分解我的购买表，不仅可以创建交易表，还可以创建purchase_transactions中间表并保留所有信息？

我相信我可以像这样创建交易表

CREATE TABLE transactions AS
  SELECT [ transaction_id, transaction_status_id...etc ]
  FROM purchases

但是我不知道如何将中间表包含到这个过程中(如果可能的话)。

我的替代方案是创建一个 php block 函数来遍历所有记录并创建条目，但数据量很大，因此看起来效率很低。

Answer 1

我实际上能够自己解决这个问题，因为购买表保存了交易表和purchase_transactions中间表所需的所有信息。

我只是使用主键 id 字段创建了交易表，并将所需的数据插入其中:

  INSERT INTO transactions (`client_id`, `last_four`, `transaction_id`,`transaction_status_id`, 
       `total`, `payment_type_id`,`cart_id`, `created_at`)
        SELECT `client_id`, `last_four`, `transaction_id`,`transaction_status_id`, 
        sum(`total`) as `total`, `payment_type_id`,`cart_id`, `created_at`
        FROM purchases
        GROUP BY `cart_id`
        ORDER BY `id` ASC")

然后我使用下面的创建表语法来创建purchase_transactions表。

 CREATE TABLE purchase_transactions
        SELECT `id`, `cart_id`
        FROM purchases
        ORDER BY id ASC

最后，我将上面的 id 列重命名为purchase_id。我不确定，但我想我可以在创建表中使用 as 语句来:

  SELECT `id` as `purchase_id`