使用 InnoDB 创建表并使用外键填充表

发布于 2024-10-15 19:34:04 字数 462 浏览 1 评论 0原文

我有一个列出客户订单的 InnoDB 表 - 该表记录了客户的电子邮件地址、订单的联系信息和订单 id。该表使用 innodb，包含大约 2.2 亿行。我没有的是列出用户的表。

我是否可以使用外键创建并填充一个表，其中包含订单表中数据的 customer_id 和 e-mail 地址？

这是我到目前为止的查询：

INSERT INTO customers (email, last_order) 
  SELECT (email_address, order_date) FROM orders 
ON DUPLICATE KEY 
  UPDATE last_order = MAX(order_date, last_order);

我相信这个查询将构建数据库，但它不会将客户 ID 放入订单表中的行中，并且我不确定这是否是最好的开始方法。有什么建议吗？

原文

I have an InnoDB table that lists customer orders - this table records the customer's email address, contact information for the order, and the order id. This table uses innodb and contains roughly 220 million rows. What I do not have, is a table that lists the users.

Is it possible, perhaps using foreign keys, for me to create and populate a table that will contain a customer_id and e-mail address from the data that is in my orders table?

This is the query that I have so far:

INSERT INTO customers (email, last_order) 
  SELECT (email_address, order_date) FROM orders 
ON DUPLICATE KEY 
  UPDATE last_order = MAX(order_date, last_order);

I believe this query will build the database, but it won't put the customer id into the row in the orders table, and I am not sure if it is the best approach to start with. Any suggestions?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

街角迷惘 2024-10-22 19:34:04

您可以通过 2 个步骤实现此目的：

第一，填写 customers 表：

INSERT INTO customers (email, last_order ) SELECT email_address, 
max(order_date) as last_order FROM orders GROUP by email_address;

第二，使用客户 ID 更新 orders 表（假设您有一个名为 的字段） >customer_id 在 orders 表中）

UPDATE orders o SET customer_id = 
    (SELECT id FROM customers WHERE email = o.email_address);

最后，如果您想保持 customers 表一致，您可以添加一个 AFTER INSERT TRIGGER 到 orders 表以使您的 customers 表保持最新状态。

You can achieve this it in 2 steps:

1st, fill in the customers table:

INSERT INTO customers (email, last_order ) SELECT email_address, 
max(order_date) as last_order FROM orders GROUP by email_address;

2nd, update the orders table with customers' ids (assuming you have a field called customer_id in the orders table)

UPDATE orders o SET customer_id = 
    (SELECT id FROM customers WHERE email = o.email_address);

Finally, if you want to keep your customers table consistent, you can add an AFTER INSERT TRIGGER to the orders table to keep your customers table up-to-date.

回复收藏 0 原文

~没有更多了~