是否可以先插入然后再选择插入的行? [英] Is it possible to INSERT and then SELECT the inserted row one after another?

问题描述

我执行两个查询，一个接一个:一个是INSERT，另一个是SELECT，用于选择插入的行. 尽管该行已成功插入(我可以在数据库中看到它)，但选择查询无法返回该行.

当我再次执行SELECT查询时，它将返回正确的结果.

插入:

    $stmt = $pdo->prepare('INSERT INTO user (id ,name, lastname ,birthday, social_type, social_id) VALUES(NULL, :name, :lastname, :birthday, :social_type, :social_id)');
    $success=$stmt->execute(array(
        ':name' => $user['name'],
        ':lastname' => $user['lastname'],
        ':birthday' => $user['birthday'],
        ':social_type' => $user['social_type'],
        ':social_id' => $user['social_id']      
    ));

选择

    $stmt = $pdo->prepare('SELECT * FROM user WHERE social_id = :social_id AND social_type = :social_type LIMIT 1');
    $stmt->execute(array(
        'social_id' => $user['social_id'],
        'social_type' => $user['social_type'] ));
    $result = $stmt->fetch(PDO::FETCH_ASSOC);

解决方案

如果您使用的是INNODB:

如果您使用的是INNODB，则由于您已验证已插入行，因此该行应该已经 只要SELECT正在查询实际行的键，就用SELECT返回 那是被插入的. (您确定没有使用INSERT DELAYED之类的功能吗? 可能会阻止该行被返回.)

如果您正在使用MYISAM:

由于MyISAM不支持事务，所以SELECT应该返回插入，但是我 无法找到任何说明这实际上得到保证的东西.

注意:下面列出的第一个URL指出您是否正在使用MYISAM(根据此链接的默认设置)， 插入将锁定表.但是，第二个URL指出由插入内容放置的锁是可读锁，因此不应阻止该表被读取.

http://www.sitepoint.com/mysql-mistakes-php-开发人员/

http://aarklondatabasetrivia.blogspot.com/2009/04/how-to-lock-and-unlock-tables-in-mysql.html

如果您正在使用INNODB(续):

如果系统中使用了AUTOCOMMIT(不确定)，您应该已经看到 选定的行(此问题说明已插入的行已验证为具有 已添加到数据库中.)

如果正在使用事务，则该事务必须已提交(此问题 指出插入的行已被验证为已添加到数据库中.

您确定第一次执行的SELECT查询是否相同 作为第二次?

确定$user['social_id']是相同的值 插入并在执行SELECT时?

如果您引用的是另一事务中插入的行，而不是 在执行插入的会话中，然后输入以下网址:

http://blogs.innodb.com/wp/2011/04/get-started-with-innodb-memcached-daemon-plugin/

声明您需要选择读未提交"以找到刚刚插入的行:"

I.E.将会话TRANSACTION ISOLATION LEVEL设置为未提交；

http://dev.mysql.com/doc/refman/5.0/en/set-transaction.html

(此功能可能取决于使用的MYSQL版本)

如果由于某种原因您正在使用INSERT DELAYED，则可能不会返回该行

---

注意: 根据此URL，如果您已开始交易，则选择的行 显示在下一个SELECT语句中(不在PHP中):

http://zetcode.com/databases/mysqltutorial/transactions/

此语句表示，如果您开始交易，则无需 设置自动提交:

"MySQL还会自动提交不属于事务一部分的语句."

此URL描述了如何在PHP中启动事务:

PHP + MySQL事务示例

I execute two queries one after another: one is INSERT another one is SELECT that selects the inserted row. Although the row was inserted successfully (i can see it in the db) the select query fails to return the row.

When I execute the SELECT query again it returns the correct result.

Insert:

    $stmt = $pdo->prepare('INSERT INTO user (id ,name, lastname ,birthday, social_type, social_id) VALUES(NULL, :name, :lastname, :birthday, :social_type, :social_id)');
    $success=$stmt->execute(array(
        ':name' => $user['name'],
        ':lastname' => $user['lastname'],
        ':birthday' => $user['birthday'],
        ':social_type' => $user['social_type'],
        ':social_id' => $user['social_id']      
    ));

Select

    $stmt = $pdo->prepare('SELECT * FROM user WHERE social_id = :social_id AND social_type = :social_type LIMIT 1');
    $stmt->execute(array(
        'social_id' => $user['social_id'],
        'social_type' => $user['social_type'] ));
    $result = $stmt->fetch(PDO::FETCH_ASSOC);

解决方案

IF YOU ARE USING INNODB:

If you are using INNODB, since you verified the row was inserted, it should have been returned with the SELECT, as long as the SELECT was querying the key of the actual row that was inserted. (Are you sure you aren't using a feature like INSERT DELAYED? That could prevent the row from being returned.)

IF YOU ARE USING MYISAM:

Since MyISAM doesn't support transactions, the SELECT should return the insert, but I cannot find anything that states this is actually guaranteed.

NOTE: The first URL listed below states if you are using MYISAM (the default according to this link), INSERTS will lock the table. However, the second URL states that the lock placed by an insert is a readable lock, so that should not have prevented the table from being read.

http://www.sitepoint.com/mysql-mistakes-php-developers/

http://aarklondatabasetrivia.blogspot.com/2009/04/how-to-lock-and-unlock-tables-in-mysql.html

IF YOU ARE USING INNODB (CONTINUED):

If AUTOCOMMIT is in use in your system (I am not sure) you should have seen the selected row (this question states the row inserted was verified as having been added to the database).

If a transaction is in use, the transaction must have been committed (this question states the row inserted was verified as having been added to the database).

Are you sure the SELECT query that is executed the first time is the same as the one the second time?

Are you sure $user['social_id'] is the same value after the INSERT and at the time of the SELECT?

If you are referring to a row inserted in another transaction, instead of in the session that is doing the insert, then this URL:

http://blogs.innodb.com/wp/2011/04/get-started-with-innodb-memcached-daemon-plugin/

states "you will need to do "read uncommitted" select to find the just inserted rows:"

I.E. set session TRANSACTION ISOLATION LEVEL read uncommitted;

http://dev.mysql.com/doc/refman/5.0/en/set-transaction.html

(This feature may be dependent on the version of MYSQL in use)

If, for some reason, you are using INSERT DELAYED, the row may not be returned

---

NOTES: According to this URL, IF you have started a transaction, the selected rows are shown in the next SELECT statement (not in PHP):

http://zetcode.com/databases/mysqltutorial/transactions/

This statement implies that if you begin a transaction, you don't need to set AUTOCOMMIT:

"MySQL also automatically commits statements that are not part of a transaction."

This URL describes how to start a transaction in PHP:

PHP + MySQL transactions examples

这篇关于是否可以先插入然后再选择插入的行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！