MySql和在链接表中插入数据，如何？

What is the trick in MySql (Version 4) to write from PHP thtree Inserts and to be sure that we can link tables properly.

MySql(Version 4)中从PHP thtree Inserts编写的技巧是什么,并确保我们可以正确链接表。

[Table1]-[LinkTable]-[Table2]

In the PHP code I create an Insert that add 1 row in table 1 and an other Insert that add an other row in table 2. I get both rows PK (primary key) with two SELECTs that return me the last row of each tables. This way I get the PK (auto-increment) from Table 1 and Table 2 and I insert it on the link table.

在PHP代码中,我创建一个Insert,在表1中添加1行,另一个Insert在表2中添加另一行。我得到两行SELECT(主键),其中两个SELECT返回每个表的最后一行。这样我就可以从表1和表2中获得PK(自动增量),并将其插入到链接表中。

The problem is that is not atomic and now that I get a lot of transaction something it doesn't link properly.

问题是,这不是原子的,现在我得到很多事务,它没有正确链接。

How can I make a link between 2 tables in MySQL 4 that preserve data? I cannot use any stocked procedure.

如何在MySQL 4中保存数据的两个表之间建立链接?我不能使用任何库存程序。

2 个解决方案

#1

your problem is here:

你的问题在这里:

SELECTs that return me the last row of each tables.

SELECT返回每个表的最后一行。

if you did something like this: SELECT MAX(id) FROM table you can get a wrong id for your transaction.

如果您执行了类似这样的操作:SELECT MAX(id)FROM table您可以获得错误的事务ID。

This is a common many-to-many relationship

这是一种常见的多对多关系

as BaileyP say you should use the function mysql_insert_id().

正如BaileyP所说,你应该使用函数mysql_insert_id()。

at http://ar2.php.net/mysql_insert_id

you can read

你可以阅读

Retrieves the ID generated for an AUTO_INCREMENT column by the previous INSERT query.

检索先前INSERT查询为AUTO_INCREMENT列生成的ID。

so dont care if other process insert a row in the same table. you always will get the last id for your current connection.

所以不要在意其他进程是否在同一个表中插入一行。你总会获得当前连接的最后一个id。

#2

Well, if you have the ability to use InnoDB tables (instead of MyISAM) then you can use transactions. Here's some rough code

好吧,如果你有能力使用InnoDB表(而不是MyISAM),那么你可以使用交易。这是一些粗略的代码

<?php

//  Start a transaction
mysql_query( 'begin' );

//  Execute the queries
if ( mysql_query( "insert into table_one (col1, col2) values('hello','world')" ) )
{
    $pk1 = mysql_insert_id();
    if ( mysql_query( "insert into table_two (col1, col2) values('foo', 'bar')" ) )
    {
        $pk2 = mysql_insert_id();
        $success = mysql_query( "insert into link_table (fk1, fk2) values($pk1, $pk2)" );
    }
}

//  Complete the transaction
if ( $success )
{
    mysql_query( 'commit' );
} else {
    mysql_query( 'rollback' );
}

If you can't use InnoDB tables then I suggest looking into the Unit Of Work pattern.

如果您不能使用InnoDB表,那么我建议调查工作单元模式。

#1