问题描述
假设我有一个如下表结构:
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.
这篇关于MySQL插入具有固定值和多选结果的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！