在mysql中插入相关的多条记录 [英] Insert related multiple records in mysql

问题描述

我有两个MySQL表，想插入多条记录而不是一条一条创建，获取id并插入相关记录

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

表格如下:

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

目前我在访问时插入一条记录，获取其 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?

推荐答案

使用单个查询在两个表中插入记录是不可能的，但是您可以使用 MySQL 的 LAST_INSERT_ID() 函数:

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

还要注意，以原始的四字节二进制形式存储 IP 地址通常更方便/更高效(可以使用 MySQL 的 INET_ATON() 和 INET_NTOA() 函数分别转换为这种形式.

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

这篇关于在mysql中插入相关的多条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！