MySQL:LAST_INSERT_ID()返回0

时间:2022-09-21 21:26:15

I've got this test table:

我有这个测试表:

CREATE TABLE IF NOT EXISTS `test` (    `id_huh` bigint(10) unsigned
NOT NULL AUTO_INCREMENT,   `title` varchar(255) NOT NULL DEFAULT
'test',   PRIMARY KEY (`id_huh`),   KEY `id_huh` (`id_huh`) )
ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

inserting using either of these three

使用这三个中的任何一个插入

INSERT INTO `test` (`id_huh`, `title`) VALUES (NULL, 'test')
INSERT INTO `test` (`id_huh`, `title`) VALUES (0, 'test')
INSERT INTO `test` (`title`) VALUES ('test')

and issuing

和发行

SELECT LAST_INSERT_ID();

but the query always results in 0.

但是查询总是得到0。

PHP's mysql_insert_id and PDO::lastInsertId() yield no result either.

PHP的mysql_insert_id和PDO::lastInsertId()也没有结果。

I've been toying with this whole day and can't get it to work. Ideas?

我花了一整天的时间才把它修好。想法吗?

5 个解决方案

#1


15  

The problem seemed to be in MySQL's phpmyadmin config file PersistentConnections set to FALSE which resulted in a new CONNECTION_ID every time a query was issued - therefore rendering SELECT LAST_INSERT_ID() ineffective.

问题似乎出现在MySQL的phpadmin myconfig文件PersistentConnections中,该文件设置为FALSE,导致每次发出查询时都有一个新的CONNECTION_ID—因此导致SELECT LAST_INSERT_ID()无效。

more info in the subsequent topic Every query creates a new CONNECTION_ID()

在后续主题中的更多信息,每个查询都会创建一个新的CONNECTION_ID()

Also thanks dnagirl for help

也感谢dnagirl的帮助

#2


9  

you have to combine

你必须结合

INSERT INTO test (title) VALUES ('test');SELECT LAST_INSERT_ID();

Then you will get the last insert id

然后您将获得最后的插入id

#3


8  

Just my 50 cents for this issue, I simply noticed that you won't get a LAST_INSERT_ID greater than 0 if your table has no AUTO_INCREMENT set to an index.

对于这个问题,我只花了50美分,我只是注意到,如果您的表没有为索引设置AUTO_INCREMENT集,则不会得到LAST_INSERT_ID大于0。

I wasted about half hour on this. Turns out I keep getting a LAST_INSERT_ID() of 0, which for this table is actually ok.

我在这上面浪费了半个小时。结果是,我一直得到LAST_INSERT_ID()为0,对于这个表来说,这是可以的。

#4


1  

I had the same issue. mysql_insert_id() or LAST_INSERT_ID() returned 0 when requested inside a PHP function with an INSERT query.

我也有同样的问题。mysql_insert_id()或LAST_INSERT_ID()在PHP函数中使用INSERT查询请求时返回0。

I sorted the issue by requesting the value of mysql_insert_id() from a separate PHP function called right after the function that INSERT query.

我通过请求mysql_insert_id()的值从一个单独的PHP函数中进行排序,该函数在插入查询的函数之后调用。

#5


-2  

it work perfectly...try it...

这工作完全…试一试……

    $result = mysql_query("INSERT INTO `test` (`title`) VALUES ('test')");
    if ($result) {
        $id = mysql_insert_id(); // last inserted id
        $result = mysql_query("SELECT * FROM tablename WHERE id = $id") or die(mysql_error());
        // return user details
        if (mysql_num_rows($result) > 0) {
            return mysql_fetch_array($result);
        } else {
            return false;
        }
    } else {
        return false;
    }

#1


15  

The problem seemed to be in MySQL's phpmyadmin config file PersistentConnections set to FALSE which resulted in a new CONNECTION_ID every time a query was issued - therefore rendering SELECT LAST_INSERT_ID() ineffective.

问题似乎出现在MySQL的phpadmin myconfig文件PersistentConnections中,该文件设置为FALSE,导致每次发出查询时都有一个新的CONNECTION_ID—因此导致SELECT LAST_INSERT_ID()无效。

more info in the subsequent topic Every query creates a new CONNECTION_ID()

在后续主题中的更多信息,每个查询都会创建一个新的CONNECTION_ID()

Also thanks dnagirl for help

也感谢dnagirl的帮助

#2


9  

you have to combine

你必须结合

INSERT INTO test (title) VALUES ('test');SELECT LAST_INSERT_ID();

Then you will get the last insert id

然后您将获得最后的插入id

#3


8  

Just my 50 cents for this issue, I simply noticed that you won't get a LAST_INSERT_ID greater than 0 if your table has no AUTO_INCREMENT set to an index.

对于这个问题,我只花了50美分,我只是注意到,如果您的表没有为索引设置AUTO_INCREMENT集,则不会得到LAST_INSERT_ID大于0。

I wasted about half hour on this. Turns out I keep getting a LAST_INSERT_ID() of 0, which for this table is actually ok.

我在这上面浪费了半个小时。结果是,我一直得到LAST_INSERT_ID()为0,对于这个表来说,这是可以的。

#4


1  

I had the same issue. mysql_insert_id() or LAST_INSERT_ID() returned 0 when requested inside a PHP function with an INSERT query.

我也有同样的问题。mysql_insert_id()或LAST_INSERT_ID()在PHP函数中使用INSERT查询请求时返回0。

I sorted the issue by requesting the value of mysql_insert_id() from a separate PHP function called right after the function that INSERT query.

我通过请求mysql_insert_id()的值从一个单独的PHP函数中进行排序,该函数在插入查询的函数之后调用。

#5


-2  

it work perfectly...try it...

这工作完全…试一试……

    $result = mysql_query("INSERT INTO `test` (`title`) VALUES ('test')");
    if ($result) {
        $id = mysql_insert_id(); // last inserted id
        $result = mysql_query("SELECT * FROM tablename WHERE id = $id") or die(mysql_error());
        // return user details
        if (mysql_num_rows($result) > 0) {
            return mysql_fetch_array($result);
        } else {
            return false;
        }
    } else {
        return false;
    }