如何在单个查询中将数据插入多个表 - Mysql

I have 8 query's to insert into 8 tables. I have tried with this But no use

我有8个查询要插入8个表。我试过这个但没用

$mysql_db_hostname = "localhost";
$mysql_db_user = "root";
$mysql_db_password = "";
$mysql_db_database = "emp";

$con = mysqli_connect($mysql_db_hostname, $mysql_db_user, $mysql_db_password) or die("Could not connect database");
mysqli_select_db($con, $mysql_db_database) or die("<div class='loginmsg'>Could not select database</div>");

if(mysqli_multi_query($con,"INSERT INTO t_emp (`e_id`,``,``) VALUES ('','',''); INSERT INTO t_emp_add (`e_id`,``,``) VALUES ('','',''); INSERT INTO t_emp_att (`e_id`,``,``) VALUES ('','',''); INSERT INTO t_emp_dep (`e_id`,``,``) VALUES ('','','');.....(etc);"))
{
 echo "Inserted";
}
else{
 echo "Not Inserted";
 }

Is there any method to store into multiple tables..?

是否有任何方法存储到多个表..？

4 个解决方案

#1

use transactions

使用交易

BEGIN;
INSERT INTO tab1 (col1, col2)
  VALUES('1', '2');
INSERT INTO tab2 (col1, col2,col3) 
  VALUES(1,2,3);
COMMIT;

MySQL doesn't support multi-table insertion in a single INSERT statement.

MySQL不支持在单个INSERT语句中插入多表。

#2

As the question was closed in other posting, I'll put this here - to create a stored procedure:-

由于这个问题在其他帖子中被关闭了，我会把它放在这里 - 创建一个存储过程： -

delimiter $$
CREATE PROCEDURE `spInsertTemp`( IN `p_emp_no` INT, IN `p_e_id` INT, IN `p_att_s_no` INT, IN `p_dep_s_no` INT )
    LANGUAGE SQL
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
begin
    insert into `t_emp` (`emp_s_no`,`e_id` ) values ( p_emp_no, p_e_id );
    insert into `t_emp_add` ( `e_id` ) values ( p_e_id );
    insert into `t_emp_att` (`att_s_no`,`e_id` ) values ( p_att_s_no, p_e_id );
    insert into `t_emp_dep` ( `dep_s_no`, `e_id`) values ( p_dep_s_no, p_e_id );
end $$;
delimiter ;

Run that in your gui, use like:-

在你的gui中运行，使用如下： -

$sql='call `spInsertTemp`(1,2,4,5);';

#3

No, MySQL hasn't expression for inserting by one query in multiple tables.

不，MySQL没有表达式来插入多个表中的一个查询。

If you want, you can use MySQL stored procedures or functions and/or use transactions for that.

如果需要，可以使用MySQL存储过程或函数和/或使用事务。

#4

You can use After Insert Trigger to insert record in multiple table. It will automatically fired when a insert statement is excuted within that database.

您可以使用After Insert Trigger在多个表中插入记录。当在该数据库中执行insert语句时，它将自动触发。

#1