mysql - 为多行创建触发器

Question

我有表用户和订单。在 orders 中的每个 UPDATE 行之后。我想更新 users 表中的 DATA，即 concat(OLD.DATA + ID 已更新)。

Table 'users'.

ID  NAME    DATA
1   John    1|2
2   Michael 3|4
3   Someone 5

Table 'orders'.

ID  USER    CONTENT
1   1       ---
2   1       ---
3   2       ---
4   2       ---
5   3       ---

例如:

SELECT `data` from `users` where `id` = 2; // Result: 3|4
UPDATE `orders` SET '...' WHERE `id` > 0;
**NEXT LOOP**
UPDATE `users` SET `data` = concat(OLD.data, ID.rowUpdated) WHERE `user` = 1;
UPDATE `users` SET `data` = concat(OLD.data, ID.rowUpdated) WHERE `user` = 1;
UPDATE `users` SET `data` = concat(OLD.data, ID.rowUpdated) WHERE `user` = 2;
UPDATE `users` SET `data` = concat(OLD.data, ID.rowUpdated) WHERE `user` = 2;
UPDATE `users` SET `data` = concat(OLD.data, ID.rowUpdated) WHERE `user` = 3;

结果:

SELECT data from users where id = 1; // Result: 1|2|1|2
SELECT data from users where id = 2; // Result: 3|4|3|4
SELECT data from users where id = 3; // Result: 5|5

我该怎么做？

Answer 1

我认为你犯了我不久前犯的同样的错误，即将数组/对象存储在列中。

我建议在您的场景中使用下表:

用户

+-----------+-----------+
|    id     | user_name |
+-----------+-----------+
|     1     |   John    |
+-----------+-----------+
|     2     |  Michael  | 
+-----------+-----------+

订单

+-----------+-----------+------------+
|    id     | user_id   |date_ordered|   
+-----------+-----------+------------+
|     1     |     1     | 2019-03-05 |
+-----------+-----------+------------+
|     2     |     2     | 2019-03-05 |
+-----------+-----------+------------+

其中 user_id 是 users 的外键

销售

+-----------+-----------+------------+------------+------------+
|    id     | order_id  |  item_sku  |    qty     |   price    |
+-----------+-----------+------------+------------+------------+
|     1     |     1     |    1001    |     1      |   2.50     |
+-----------+-----------+------------+------------+------------+
|     2     |     1     |    1002    |     2      |   3.00     |
+-----------+-----------+------------+------------+------------+
|     3     |     2     |    1001    |     2      |   2.00     |
+-----------+-----------+------------+------------+------------+

其中 order_id 是 orders 的外键

现在是令人困惑的部分。您将需要使用一系列JOIN来访问每个用户的相关数据。

SELECT 
t3.id AS user_id,
t3.user_name,
t1.id AS order_id,
t1.date_ordered,
SUM((t2.price * t2.qty)) AS order_total   
FROM orders t1
JOIN sales t2 ON (t2.order_id = t1.id)
LEFT JOIN users t3 ON (t1.user_id = t3.id)
WHERE user_id=1
GROUP BY order_id;

这将返回:

+-----------+--------------+------------+------------+--------------+
|  user_id  |  user_name   |  order_id  |date_ordered|  order_total |   
+-----------+--------------+------------+------------+--------------+
|     1     |    John      |      1     | 2019-03-05 |     8.50     |
+-----------+--------------+------------+------------+--------------+

这些类型的 JOIN 语句基本上应该出现在使用关系数据库的任何项目中(也就是说，如果您正确设计了数据库)。通常，我会为每个复杂查询创建一个 View ，然后可以通过简单的SELECT * FROMorders_view

来访问该 View

例如:

CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `root`@`localhost` 
    SQL SECURITY DEFINER
VIEW orders_view AS (

  SELECT 
  t3.id AS user_id,
  t3.user_name,
  t1.id AS order_id,
  t1.date_ordered,
  SUM((t2.price * t2.qty)) AS order_total   
  FROM orders t1
  JOIN sales t2 ON (t2.order_id = t1.id)
  LEFT JOIN users t3 ON (t1.user_id = t3.id)
  GROUP BY order_id
)

然后可以通过以下方式访问:

SELECT * FROM orders_view WHERE user_id=1;

这将返回与上面查询相同的结果。

根据您的需要，您可能需要向每个表中添加更多表格(地址、产品等)以及更多行。您经常会发现需要将 5 个以上的表JOIN 合并到一个 View 中，有时您可能需要JOIN 同一个表两次。

我希望这会有所帮助，尽管它不能完全回答您的问题!