MySQL插入具有固定值和多选结果的数据

2022-01-09 00:00:00 sql select insert mysql

假设我有一个如下表结构:

Suppose I have a table structure like below:

notification_table

| id | receiver_id | type | content | time |

接收者 id 来自用户表:

The receiver id is from a user table:

user_table

| id | username |

并且内容和时间都来自一个广播表:

And the content and time are from a broadcast table:

broadcast_table

| id | content | time |

所以当我需要插入这个通知表时，我需要从用户中选择id，从广播中选择内容+时间，类型固定为system_broadcast".我可以在一个查询中执行此操作吗?

So when I need to insert into this notification table, I need to select id from users and select content+time from broadcasts, and the type is fixed to "system_broadcast". Can I do this in one query?

我试过了

INSERT INTO notification_table (receiver_id, type, content, time) 
VALUES (
       (SELECT id FROM user_table WHERE username='test' LIMIT 1), 
        'system_broadcast', 
       (SELECT content, time FROM broadcast_table)
)

但它会返回类似mysql Operand should contain 1 column(s)"的错误.

But it returns error like "mysql Operand should contain 1 column(s)".

推荐答案

是的，你可以使用 insert 来做到这一点...选择.这似乎符合您原始查询的意图:

Yes, you can do this using insert . . . select. This seems to match the intention of your original query:

INSERT INTO notification_table (receiver_id, type, content, time) 
    SELECT (SELECT id FROM user_table WHERE username = 'test' LIMIT 1), 
           'system_broadcast',
           content, time
    FROM broadcast_table;

请注意，这将为 broadcast_table 中的每一行插入一行.您可能需要 where 子句或 limit 来仅获取特定行.

Note that this will insert one row for every row in broadcast_table. You might want a where clause or limit to get only particular rows.

相关文章