0

I need to do a multiple insert in a stored procedure with MySQL. I've been looking about this but I didn't find almost any information or examples at all.

The number of messages (inserts) varies. Sometimes could be one, sometimes could be twenty.

In short, I need to do the following: let's say the users could belong to a group. If one of them leave the group, I need to advice about this action to the other users with a message.

The inserts would be something like this:

INSERT INTO messages (author, dest, title, msg)
VALUES (ldr,id1,'Same title for all messages','Same body message for all'),
(ldr,id2,'Same title for all messages','Same body message for all'),
...

I've tried to do it with single inserts into a loop but the time it lasts is, as you can guess, terribly high.

The possible ways I've thought or seen in other pages are:

  • Take the ID's of the users into a text variable, prepare the insert statement with PREPARE and execute it.
  • Take the ID's of the users, create a temporary table and introduce the ID's, and finally insert all the messages from there.
  • Any other way to do it...¿?

I don't know which one of them is the best way to do it, in the sense of good practice and performance/efficiency.

Do you have any ideas?

In case you know how to do it, please, could you write a simple example?

Thank you so much.

cooper
  • 635
  • 1
  • 8
  • 23
  • `VALUES` should be appear once in an INSERT query. – Grijesh Chauhan Mar 15 '14 at 19:30
  • @Grijesh Chauhan Right, my mistake. It's already corrected. – cooper Mar 15 '14 at 19:33
  • You could split out `dest` to it's own table (message_id, dest), so only those would need to be added. – Wrikken Mar 15 '14 at 19:36
  • @Wrikken I don't understand what you mean. I already know which are the group of 'dest', if that was what you wanted to point out – cooper Mar 15 '14 at 19:56
  • No, I wanted to point out that you don't need to duplicate subject & bodies. – Wrikken Mar 15 '14 at 20:18
  • It depends a little on how the message is delivered, you might even want to employ 3 tables: `message`, `message_receivers`, but also `message_groups`. It all depends on how this is delivered further on: if in a site-context, you might want to search for messages to the user, but also to the groups the user belongs to. If it's a temporary location for email to send, the sending script will need to know how to expand group-ids to their individual receivers. – Wrikken Mar 15 '14 at 20:27
  • Ok Wrikken, I understand you, but it's more simple than that. Although the users belongs to a group, there're no shared messages. I mean, the messages are always sent to each user. The problem is that if a user deletes the message, the rest of the members of that group couldn't read the message anymore. And it's nothing about the email service. It's just about inserting a row that corresponds to a message. – cooper Mar 15 '14 at 21:59

0 Answers0