MySQL insert data with fixed values and multiple s

问题:

Suppose I have a table structure like below:

notification_table

| id | receiver_id | type | content | time |

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 |

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?

I tried

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)
)

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

回答1:

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;

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.