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

MySQL insert data with fixed values and multiple select results(MySQL插入具有固定值和多选结果的数据)

本文介绍了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.

                        这篇关于MySQL插入具有固定值和多选结果的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！

问题描述

推荐答案

基础教程推荐