CODEWITHSUNDEEP
mysqlsql
Diego Manenti Martins
Diego Manenti Martins

Reputation: 1

Insert related multiple records in mysql

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?

Upvotes: 0

Views: 1332

Answers (1)

eggyal
eggyal

Reputation: 125855

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

Upvotes: 2

Related Questions