问题描述
我有两个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中插入相关的多条记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!