MySQL MySQL插入多个表？（数据库规范化？）

Question

提问by Jay Wit

I tried searching a way to insertinformation in multiple tables in the same query, but found out it's impossible? So I want to insertit by simply using multiple queries i.e;

我尝试在insert同一个查询中的多个表中搜索信息，但发现这是不可能的？所以我想insert通过简单地使用多个查询来实现它；

INSERT INTO users (username, password) VALUES('test', 'test')
INSERT INTO profiles (userid, bio, homepage) VALUES('[id of the user here?]','Hello world!', 'http://www.stackoverflow.com')

But how can I give the auto-increment idfrom the usersto the "manual" useridfor the profiletable?

但我怎么可以给自动增量id从users“手动”userid为profile表？

Answer 1

回答by Konerak

No, you can't insert into multiple tables in one MySQL command. You can however use transactions.

不，您不能在一个 MySQL 命令中插入多个表。但是，您可以使用事务。

BEGIN;
INSERT INTO users (username, password)
  VALUES('test', 'test');
INSERT INTO profiles (userid, bio, homepage) 
  VALUES(LAST_INSERT_ID(),'Hello world!', 'http://www.stackoverflow.com');
COMMIT;

Have a look at LAST_INSERT_ID()to reuse autoincrement values.

看看LAST_INSERT_ID()重用自动增量值。

Edit: you said "After all this time trying to figure it out, it still doesn't work. Can't I simply put the just generated ID in a $var and put that $var in all the MySQL commands?"

编辑：你说“经过这么长时间的尝试，它仍然不起作用。我不能简单地将刚刚生成的 ID 放在 $var 中并将该 $var 放在所有 MySQL 命令中吗？“

Let me elaborate: there are 3 possible ways here:

让我详细说明：这里有 3 种可能的方法：

In the code you see above. This does it all in MySQL, and the LAST_INSERT_ID()in the second statement will automatically be the value of the autoincrement-column that was inserted in the first statement.
Unfortunately, when the second statement itself inserts rows in a table with an auto-increment column, the LAST_INSERT_ID()will be updated to that of table 2, and not table 1. If you still need that of table 1 afterwards, we will have to store it in a variable. This leads us to ways 2 and 3:

Will stock the LAST_INSERT_ID()in a MySQL variable:

INSERT ...
SELECT LAST_INSERT_ID() INTO @mysql_variable_here;
INSERT INTO table2 (@mysql_variable_here, ...);
INSERT INTO table3 (@mysql_variable_here, ...);

Will stock the LAST_INSERT_ID()in a php variable (or any language that can connect to a database, of your choice):
- INSERT ...
- Use your language to retrieve the LAST_INSERT_ID(), either by executing that literal statement in MySQL, or using for example php's mysql_insert_id()which does that for you
- INSERT [use your php variable here]

在上面的代码中。这在 MySQL 中完成， LAST_INSERT_ID()第二个语句中的值将自动成为插入到第一个语句中的 autoincrement-column 的值。
不幸的是，当第二条语句本身在具有自动递增列的表中插入行时，LAST_INSERT_ID()将更新为表 2 的行，而不是表 1。如果之后您仍然需要表 1 的行，我们将不得不存储它在一个变量中。这将我们引向方式 2 和方式 3：

将存储LAST_INSERT_ID()在 MySQL 变量中：

INSERT ...
SELECT LAST_INSERT_ID() INTO @mysql_variable_here;
INSERT INTO table2 (@mysql_variable_here, ...);
INSERT INTO table3 (@mysql_variable_here, ...);

将存储LAST_INSERT_ID()在 php 变量中（或任何可以连接到数据库的语言，您选择）：
- INSERT ...
- 使用您的语言来检索LAST_INSERT_ID()，通过在 MySQL 中执行该文字语句，或使用例如mysql_insert_id()为您执行此操作的 php
- INSERT [use your php variable here]

WARNING

警告

Whatever way of solving this you choose, you must decide what should happen should the execution be interrupted between queries(for example, your database-server crashes). If you can live with "some have finished, others not", don't read on.

无论您选择哪种解决方法，您都必须决定如果执行在查询之间中断（例如，您的数据库服务器崩溃）会发生什么。如果您可以接受“有些已经完成，有些还没有”，请不要继续阅读。

If however you decide "either all queries finish, or none finish - I do not want rows in some tables but no matching rows in others, I always want my database tables to be consistent", you need to wrap all statements in a transaction. That's why I used the BEGINand COMMIThere.

但是，如果您决定“要么所有查询都完成，要么没有完成 - 我不希望某些表中的行但其他表中没有匹配的行，我总是希望我的数据库表保持一致”，则需要将所有语句包装在一个事务中。这就是我在这里使用BEGIN和的原因COMMIT。

Comment again if you need more info :)

如果您需要更多信息，请再次发表评论:)

Answer 2

回答by Jon Black

fairly simple if you use stored procedures:

如果您使用存储过程，则相当简单：

call insert_user_and_profile('f00','http://www.f00.com');

full script:

完整脚本：

drop table if exists users;
create table users
(
user_id int unsigned not null auto_increment primary key,
username varchar(32) unique not null
)
engine=innodb;

drop table if exists user_profile;
create table user_profile
(
profile_id int unsigned not null auto_increment primary key,
user_id int unsigned not null,
homepage varchar(255) not null,
key (user_id)
)
engine=innodb;

drop procedure if exists insert_user_and_profile;

delimiter #

create procedure insert_user_and_profile
(
in p_username varchar(32),
in p_homepage varchar(255)
)
begin
declare v_user_id int unsigned default 0;

insert into users (username) values (p_username);
set v_user_id = last_insert_id(); -- save the newly created user_id

insert into user_profile (user_id, homepage) values (v_user_id, p_homepage);

end#

delimiter ;

call insert_user_and_profile('f00','http://www.f00.com');

select * from users;
select * from user_profile;

Answer 3

回答by Zasega Anonimno

What would happen, if you want to create many such records ones (to register 10 users, not just one)? I find the following solution (just 5 queryes):

如果您想创建许多这样的记录（注册 10 个用户，而不仅仅是一个），会发生什么？我找到了以下解决方案（只有 5 个查询）：

Step I:Create temporary table to store new data.

第一步：创建临时表来存储新数据。

CREATE TEMPORARY TABLE tmp (id bigint(20) NOT NULL, ...)...;

Next, fill this table with values.

接下来，用值填充此表。

INSERT INTO tmp (username, password, bio, homepage) VALUES $ALL_VAL

Here, instead of $ALL_VALyou place list of values: ('test1','test1','bio1','home1'),...,('testn','testn','bion','homen')

在这里，而不是$ALL_VAL放置值列表： ('test1','test1','bio1','home1'),...,('testn','testn','bion','homen')

Step II:Send data to 'user' table.

第二步：将数据发送到“用户”表。

INSERT IGNORE INTO users (username, password)
SELECT username, password FROM tmp;

Here, "IGNORE" can be used, if you allow some users already to be inside. Optionaly you can use UPDATE similar to step III, before this step, to find whom users are already inside (and mark them in tmp table). Here we suppouse, that username is declared as PRIMARYin users table.

在这里，可以使用“忽略”，如果您允许一些用户已经在里面。可选地，您可以在此步骤之前使用类似于步骤 III 的 UPDATE 来查找已在其中的用户（并在 tmp 表中标记它们）。这里我们假设，用户名PRIMARY在 users 表中声明。

Step III:Apply update to read all users id from users to tmp table. THIS IS ESSENTIAL STEP.

第三步：应用更新从用户中读取所有用户 id 到 tmp 表。这是必不可少的步骤。

UPDATE tmp JOIN users ON tmp.username=users.username SET tmp.id=users.id

Step IV:Create another table, useing read id for users

第四步：创建另一个表，为用户使用读取ID

INSERT INTO profiles (userid, bio, homepage) 
SELECT id, bio, homepage FROM tmp

Answer 4

回答by diEcho

try this

尝试这个

$sql= " INSERT INTO users (username, password) VALUES('test', 'test') ";
mysql_query($sql);
$user_id= mysql_insert_id();
if(!empty($user_id) {

$sql=INSERT INTO profiles (userid, bio, homepage) VALUES($user_id,'Hello world!', 'http://www.stackoverflow.com');
/* or 
 $sql=INSERT INTO profiles (userid, bio, homepage) VALUES(LAST_INSERT_ID(),'Hello   world!', 'http://www.stackoverflow.com'); */
 mysql_query($sql);
};

References
PHP
MYSQL

参考
PHP
MYSQL

Answer 5

回答by Daniel Kutik

have a look at mysql_insert_id()

看看 mysql_insert_id()

here the documentation: http://in.php.net/manual/en/function.mysql-insert-id.php

这里的文档：http: //in.php.net/manual/en/function.mysql-insert-id.php

Answer 6

回答by SebastianZdroana

This is the way that I did it for a uni project, works fine, prob not safe tho

这是我为 uni 项目所做的方式，工作正常，但可能不安全

$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = '';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);

$title =    $_POST['title'];            
$name =     $_POST['name'];         
$surname =  $_POST['surname'];                  
$email =    $_POST['email'];            
$pass =     $_POST['password'];     
$cpass =    $_POST['cpassword'];        

$check = 1;

if (){
}
else{
    $check = 1;
}   
if ($check == 1){

require_once('website_data_collecting/db.php');

$sel_user = "SELECT * FROM users WHERE user_email='$email'";
$run_user = mysqli_query($con, $sel_user);
$check_user = mysqli_num_rows($run_user);

if ($check_user > 0){
    echo    '<div style="margin: 0 0 10px 20px;">Email already exists!</br>
             <a href="recover.php">Recover Password</a></div>';
}
else{
    $users_tb = "INSERT INTO users ". 
           "(user_name, user_email, user_password) ". 
        "VALUES('$name','$email','$pass')";

    $users_info_tb = "INSERT INTO users_info".
           "(user_title, user_surname)".
        "VALUES('$title', '$surname')";

    mysql_select_db('dropbox');
    $run_users_tb = mysql_query( $users_tb, $conn );
    $run_users_info_tb = mysql_query( $users_info_tb, $conn );

    if(!$run_users_tb || !$run_users_info_tb){
        die('Could not enter data: ' . mysql_error());
    }
    else{
        echo "Entered data successfully\n";
    }

    mysql_close($conn);
}

}

Answer 7

回答by Your Common Sense

Just a remark about your saying

只是对你的说法的评论

Hi, I tried searching a way to insert information in multiple tables in the same query

嗨，我尝试搜索一种在同一查询中的多个表中插入信息的方法

Do you eat all your lunch dishes mixed with drinks in the same bowl?
I suppose - no.

您是否将所有午餐菜肴与饮料混合在同一个碗中？
我想 - 不。

Same here.
There are things we do separately.
2 insert queries are 2 insert queries. It's all right. Nothing wrong with it. No need to mash it in one.
Same for select. Query must be sensible and do it's job. That's the only reasons. Number of queries is not.

同样在这里。
有些事情我们分开做。
2 个插入查询是 2 个插入查询。没关系。没什么不对的。没有必要把它混为一谈。
选择相同。查询必须是明智的，并做好它的工作。这是唯一的原因。查询次数不是。

As for the transactions - you may use them, but it's not THAT big deal for the average web-site. If it happened once a year (if ever) that one user registration being broken you'll be able to fix, no doubt.
there are hundreds of thousands sites running mysql with no transaction support driver. Have you heard of terrible disasters breaking these sites apart? Me neither.

至于交易 - 您可以使用它们，但对于普通网站来说并不是什么大问题。如果每年发生一次（如果有的话）一个用户注册被破坏，您将能够修复，毫无疑问。
有数十万个站点运行 mysql 而没有事务支持驱动程序。您听说过可怕的灾难使这些站点分崩离析吗？我也不。

And mysql_insert_id() has noting to do with transactions. you may include in into transaction all right. it's just different matters. Someone raised this question out of nowhere.

而 mysql_insert_id() 与事务无关。你可以包括在交易中。这只是不同的事情。有人突然提出这个问题。

Answer 8

回答by ocnet

For PDO You may do this

对于 PDO 你可以这样做

$stmt1 = "INSERT INTO users (username, password) VALUES('test', 'test')"; 
$stmt2 = "INSERT INTO profiles (userid, bio, homepage) VALUES('LAST_INSERT_ID(),'Hello world!', 'http://www.stackoverflow.com')";

$sth1 = $dbh->prepare($stmt1);
$sth2 = $dbh->prepare($stmt2);

BEGIN;
$sth1->execute (array ('test','test'));
$sth2->execute (array ('Hello world!','http://www.stackoverflow.com'));
COMMIT;

MySQL MySQL插入多个表？（数据库规范化？）

提问by Jay Wit

回答by Konerak

Edit: you said "After all this time trying to figure it out, it still doesn't work. Can't I simply put the just generated ID in a $var and put that $var in all the MySQL commands?"

编辑：你说“经过这么长时间的尝试，它仍然不起作用。我不能简单地将刚刚生成的 ID 放在 $var 中并将该 $var 放在所有 MySQL 命令中吗？“

WARNING

警告

回答by Jon Black

回答by Zasega Anonimno

回答by diEcho

回答by Daniel Kutik

回答by SebastianZdroana

回答by Your Common Sense

回答by ocnet

相关推荐

最近更新

标签

MySQL MySQL插入多个表？（数据库规范化？）

提问by Jay Wit

回答by Konerak

Edit: you said "After all this time trying to figure it out, it still doesn't work. Can't I simply put the just generated ID in a $var and put that $var in all the MySQL commands?"

编辑：你说“经过这么长时间的尝试，它仍然不起作用。我不能简单地将刚刚生成的 ID 放在 $var 中并将该 $var 放在所有 MySQL 命令中吗？“

WARNING

警告

回答by Jon Black

回答by Zasega Anonimno

回答by diEcho

回答by Daniel Kutik

回答by SebastianZdroana

回答by Your Common Sense

回答by ocnet

相关推荐

MySQL MySQL中的for循环示例

MySQL #1072 - 表中不存在键列“role_id”

从命令行连接到 MySQL

MySQL 如何比较where子句中的时间戳

相关推荐

最近更新

标签