Insert related multiple records in mysql

Question

I have two MySQL tables and want to insert multiple records instead of creating one by one, get id and insert related records

here are the tables:

CREATE TABLE `visit` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ip_address` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
)

CREATE TABLE `visitmeta` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `page_visit_id` int(11) NOT NULL,
  `key` varchar(255) NOT NULL,
  `value` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
)

Currently I insert one record on visit, get its id and insert records on visit meta. Is there a way to create a new record into visit and in the same query create visit meta records?

Can you show us what you have now so we get an idea of the data and exact commands you're trying to run? — Mike Parkhill
– Mike Parkhill, Commented Oct 14, 2012 at 22:51

eggyal · Accepted Answer · 2012-10-14 22:50:47Z

2

It's not possible to insert records in two tables with a single query, but you can do it in just two queries using MySQL's LAST_INSERT_ID() function:

INSERT INTO visit
  (ip_address)
VALUES
  ('1.2.3.4')
;

INSERT INTO visitmeta
  (page_visit_id, key, value)
VALUES
  (LAST_INSERT_ID(), 'foo', 'bar'),
  (LAST_INSERT_ID(), 'baz', 'qux')
;

Note also that it's often more convenient/performant to store IP addresses in their raw, four-byte binary form (one can use MySQL's INET_ATON() and INET_NTOA() functions to convert to/from such form respectively).

answered Oct 14, 2012 at 22:50

eggyal

127k18 gold badges221 silver badges245 bronze badges

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

1 Comment

Diego Manenti Martins Over a year ago

That's what I'm currently doing. Thanks.

Collectives™ on Stack Overflow

Insert related multiple records in mysql

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related