mysql一次插入两个表中有主键和外键 [英] mysql inserting into 2 tables at once that have primary key and foreign key
问题描述
我有两张表
书籍表 如何将book表同时插入bookAwards表中? 当我试图插入到书籍表中时,它给出了外键导致的错误? 我想插入到books表中价值,然后奖励名称与一年bookAwards? 任何帮助将不胜感激。 在应用程序级别或任何您想要调用此过程的源代码中, 为了安全起见,您仍然可以参数化过程,例如 I have two tables books table bookAwards table ID of bookAwards is foreign key of ID in books table. How can I insert into books table at the same time into bookAwards table? When I am trying to insert into books table it gives the error that foreign key causes? I want to insert into books table the values and then an awardname with a year into bookAwards? Any help will be much appreciated. You can use And on the application level or on any sources that you want to call this procedure, For security purposes, you can still parameterized the procedure, eg
这篇关于mysql一次插入两个表中有主键和外键的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
$ b bookAwards表
$ b
bookAwards的ID是books表中的ID的外键。
STORED PROCEDURE
,所以你只能从t调用一次他应用程序级别。例子
$ $ p $ DELIMITER $$
CREATE PROCEDURE InsertBook
(
IN _Title INT,
IN _AwardName VARCHAR(35),
IN _Year INT
)
BEGIN
插入书籍(标题)
VALUES(_Title);
- 由于ID设置为AUTO_INCREMENT
,因此有两种方法可以从书籍表中获取ID
- 并将其插入
- 在BookAwards上
- 第一种方式
- 通过使用LAST_INSERT_ID()
SET @last_ID = LAST_INSERT_ID();
- SECOND WAY
- 使用MAX()
- SET @last_ID =(SELECT MAX(ID)FROM Books);
插入BookAwards(ID,AwardName,Year)
VALUES(@last_ID,_AwardName,_Year);
END $$
DELIMITER;
CALL InsertBook('Lost Art','Best in Churva',2013);
CALL InsertBook(?,?,?);
STORED PROCEDURE
on this so you will only call once from the application level. Example,DELIMITER $$
CREATE PROCEDURE InsertBook
(
IN _Title INT,
IN _AwardName VARCHAR(35),
IN _Year INT
)
BEGIN
INSERT INTO Books (Title)
VALUES(_Title);
-- since the ID is set as AUTO_INCREMENT
-- there are two ways to do how you can get the ID
-- from the Books Table and insert it
-- on BookAwards
-- FIRST WAY
-- by using LAST_INSERT_ID()
SET @last_ID = LAST_INSERT_ID();
-- SECOND WAY
-- by using MAX()
-- SET @last_ID = (SELECT MAX(ID) FROM Books);
INSERT INTO BookAwards(ID, AwardName, Year)
VALUES (@last_ID, _AwardName, _Year);
END $$
DELIMITER ;
CALL InsertBook('Lost Art', 'Best in Churva', 2013);
CALL InsertBook(?, ?, ?);