MySql insert with multiple values in a single query

Question

I wonder if there are any advantage or disadvantage using multiples values in a single query, or whether is better to insert those values into a separate queries?

For example, supposed that I have the table tester_tbl which has 2 columns of type int both, id1 and id2 respectively and I want insert the values 2, 3, 4 to the column id2 and the value 1 to the column id1, something like this :

+-----+-----+
| id1 | id2 |
+-----+-----+
|   1 |   2 |
|   1 |   3 |
|   1 |   4 |
+-----+-----+

To accomplish it, what do you recommends me?, use a single query that inserted those values, so INSERT INTO tester_tbl (id1, id2) VALUES (1, 2), (1,3), (1,4);, or do multiples queries to insert those values, so

`INSERT INTO tester_tbl (id1, id2) VALUES (1, 2);`,
`INSERT INTO tester_tbl (id1, id2) VALUES (1, 3);`,
`INSERT INTO tester_tbl (id1, id2) VALUES (1, 4);`

If anyone can tell me a better way to do this, I'd be grateful.

Best regards.

Jordan · Accepted Answer · 2013-05-01 15:47:57Z

3

According to the documentation, MySQL inserts with multiple values in one query is more efficient and faster.

answered May 1, 2013 at 15:47

Jordan

3,0222 gold badges23 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Uncle Iroh · Accepted Answer · 2013-05-01 15:46:30Z

1

The first way is better. More efficient and faster, especially with many inserts. A lot faster. I speak from experience.

answered May 1, 2013 at 15:46

Uncle Iroh

6,0956 gold badges55 silver badges65 bronze badges

1 Comment

avolquez Over a year ago

Thanks for your answer @Uncle Iroh, I'm grateful about you.

Mike Brant · Accepted Answer · 2013-05-01 15:53:22Z

0

In my mind the only reason you might want to use separate queries, is if you needed to enforce transactional integrity on each one individually. Outside of having that need, use a single query.

answered May 1, 2013 at 15:53

Mike Brant

71.4k10 gold badges102 silver badges104 bronze badges

Collectives™ on Stack Overflow

MySql insert with multiple values in a single query

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related