MySQL存储过程在2个表中循环，选择值，并存储在第三个表中

Question

我有三个表users、memos和memos_read。

users:

+---+----------+
|ID | NAME     |
+---+----------+
| 1 | Mike     |
| 4 | Susan    |
| 5 | Peter    |
| 6 | Mary     |
+---+----------+

memos:

+---+----------+
|ID | CONTENT  |
+---+----------+
| 15| Hello    |
| 16| World    |
| 17| Foo      |
| 23| Bar      |
+---+----------+

我想创建一个带有嵌套循环的存储过程，为已读取的备忘录创建一个"读取映射"，创建如下内容：

memos_read:

+---------+----------+
| MEMO_ID | USER_ID  |
+---------+----------+
|    15   |    1     |
|    15   |    4     |
|    15   |    5     |
|    15   |    6     |
|    16   |    1     |
|    16   |    4     |
|    16   |    5     |
|    16   |    6     |
|    17   |    1     |
|    17   |    4     |
|    17   |    5     |
|    17   |    6     |
|    23   |    1     |
|    23   |    4     |
|    23   |    5     |
|    23   |    6     |
+---------+----------+

这可能吗？提前感谢！

Answer 1

@Nick的答案应该对你有效；但是，如果您对创建新表不感兴趣，而是在查询中动态使用"读取映射"；可以使用派生表。

一般的查询结构如下：

SELECT
read_map.MEMO_ID,
read_map.USER_ID,
...
FROM 
(
SELECT 
m.ID AS MEMO_ID, 
u.ID AS USER_ID
FROM users AS u 
CROSS JOIN memos AS m 
) AS read_map 
...  -- here you can join with other tables as required
WHERE ...

Answer 2

您可以使用CREATE TABLE ... SELECT语法创建memos_read表：

CREATE TABLE memos_read AS
SELECT m.ID AS MEMO_ID, u.ID AS USER_ID
FROM memos m 
CROSS JOIN users u

输出符合您的要求。dbfiddle 演示