MySQL Mysql插入2个表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3053593/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Mysql insert into 2 tables
提问by Spidfire
I want to make a insert into 2 tables
我想插入 2 个表
visits:
访问：
visit_id int | card_id int
registration:
登记：
registration_id int | type enum('in','out') | timestamp int | visit_id int
I want something like:
我想要这样的东西：
INSERT INTO `visits` as v ,`registration` as v
(v.`visit_id`,v.`card_id`,r.`registration_id`, r.`type`, r.`timestamp`, r.`visit_id`)
VALUES (NULL, 12131141,NULL, UNIX_TIMESTAMP(), v.`visit_id`);
I wonder if its possible
我想知道它是否可能
回答by RaYell
It's not possible with one query as INSERTcan only insert data to one table in mysql. You can either
一个查询是不可能的，因为INSERT只能向 mysql 中的一个表插入数据。你可以
- write this as two queries and execute them as a batch
- create a stored procedure that would execute two insert command
- 将其写为两个查询并作为批处理执行它们
- 创建一个将执行两个插入命令的存储过程
You can wrap those inserts in transaction if you need to make sure that both queries will write the data.
如果您需要确保两个查询都会写入数据，您可以将这些插入包装在事务中。
回答by Ike Walker
It seems like the problem you are trying to solve is to get the auto-increment value from the "visits" row to insert into "registration". Am I right?
您试图解决的问题似乎是从“访问”行中获取自动增量值以插入到“注册”中。我对吗？
If so, you can just use the LAST_INSERT_ID()function like this:
如果是这样，您可以像这样使用LAST_INSERT_ID()函数：
INSERT INTO `visits` (`visit_id`,`card_id`)
VALUES (NULL, 12131141);
INSERT INTO `registration` (`registration_id`, `type`, `timestamp`, `visit_id`)
VALUES (NULL, 'in', UNIX_TIMESTAMP(), LAST_INSERT_ID());
回答by supersan
You can always do something like this
你总是可以做这样的事情
INSERT IGNORE INTO `table2` VALUES ((select id from table1 where col="value"), 3, 4, 5)
回答by kapil
INSERT INTO designation as de,
department as da,
profile as pr
(designation_name,
depart_id,
id,
username,
department,
designation)
select de.designation_name,
de.depart_id,da.id,
pr.username,
pr.department,
pr.designation
from
designation,
department,
profile
de.designation_name='project manager' AND de.id='1' OR
de.depart_id='2' AND de.id='2' OR
da.id='2' OR
pr.username='kapil.purohit' AND pr.id='9' AND pr.status='1' OR
pr.department='1' AND pr.id='9' OR
pr.designation='3' AND pr.id='9' AND pr.status='1'
WHERE
de.id = da.id AND
da.id = pr.id AND
de.id = pr.id AND
ORDER BY de.id DESC
