php MySQL- 为什么 LAST_INSERT_ID() 对我不起作用？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2033366/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL- Why is LAST_INSERT_ID() not working for me?
提问by Steven
I have the following code:
我有以下代码：
public function createNewGuide($userID,$guideName)
{
$sql =" INSERT INTO myTable(name, updated)
VALUES ('$guideName', 'NOW()')";
//Process query
$this->query($sql); // This inserts the new row
$this->query('LAST_INSERT_ID()'); // This throws an error
return $this->query_result;
}
My query function looks like this:
我的查询函数如下所示：
private function query($sql)
{
$this->query_result = mysql_query($sql, $this->conn)
or die("Unable to query local database <b>". mysql_error()."</b><br>$sql");
}
I get the following error:
我收到以下错误：
MySQL Database Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LAST_INSERT_ID()'
MySQL 数据库错误：您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，了解在“LAST_INSERT_ID()”附近使用的正确语法
I've googled and looked at similar problems, but not found an answer :(
我用谷歌搜索并查看了类似的问题，但没有找到答案:(
I have not tried the PHP function mysql_insert_id(), as I really would like to do this using SQL.
我还没有尝试过PHP 函数 mysql_insert_id()，因为我真的很想使用 SQL 来做到这一点。
回答by John Parker
Why not just use PHP's mysql_insert_id?
为什么不直接使用 PHP 的mysql_insert_id？
Irrespective...
不管...
SELECT LAST_INSERT_ID()
...should work as long as you've an auto-increment column in the table.
...只要您在 table 中有一个自动增量列就应该工作。
回答by Mark Byers
You forgot SELECT:
你忘了选择：
"SELECT LAST_INSERT_ID()"
回答by psaniko
That won't work without a SELECT:
没有 SELECT 就行不通：
SELECT LAST_INSERT_ID();
or just use mysql_insert_id, it's a php function which does the same on the php level. However, use the first method if your table ids are BIGINT.
或者只是使用mysql_insert_id，它是一个 php 函数，它在 php 级别上执行相同的操作。但是，如果您的表 ID 是 BIGINT，请使用第一种方法。
回答by Alessio Nobile
If you have multiple Database links into the same enviroment, you should always specify the Link Identifier.
如果在同一环境中有多个数据库链接，则应始终指定链接标识符。
In case of mysql_insert_idphp function you should always call it using mysql_insert_id( $link_id );
在mysql_insert_idphp 函数的情况下，你应该总是使用mysql_insert_id( $link_id );
In case you call it by SQL query using SELECT LAST_INSERT_ID( link_id ).
如果您使用SELECT LAST_INSERT_ID( link_id ).
回答by Pascal MARTIN
As you are using the mysql_*functions, why not just use the mysql_insert_idfunction, instead of calling LAST_INSERT_ID()yourself ?
当您使用这些mysql_*函数时，为什么不直接使用该mysql_insert_id函数，而不是调用LAST_INSERT_ID()自己呢？
Still, the SQL error you are getting is probably because the SQL query you are sending to the server is this one :
尽管如此，您收到的 SQL 错误可能是因为您发送到服务器的 SQL 查询是这样的：
LAST_INSERT_ID()
Instead of this one :
而不是这个：
select LAST_INSERT_ID()
There should be a select, if you are doing an SQL query to... select... some data.
应该有一个select, 如果您正在执行 SQL 查询以...选择...一些数据。
回答by Clash
The guys have already answered that you were missing the SELECT prefix.
这些人已经回答说您缺少 SELECT 前缀。
By the way, you should watch your INSERT statement... it has a clear door for SQL injection if $guideNameis not escaped.
顺便说一句，你应该注意你的 INSERT 语句......如果$guideName没有转义，它有一个明确的 SQL 注入门。
回答by Bill Karwin
LAST_INSERT_ID()returns zero if no row was inserted.
LAST_INSERT_ID()如果没有插入行，则返回零。
You should check that your INSERTactually succeeded. Always test the return value of mysql_query()and other functions, which is usually FALSEif an error occurred.
你应该检查你是否INSERT真的成功了。始终测试mysql_query()和其他函数的返回值，这通常FALSE是在发生错误时。
$sql =" INSERT INTO myTable(name, updated)
VALUES ('$guideName', 'NOW()')";
if ($this->query($sql) === FALSE) {
die(mysql_error());
}
if (($result = $this->query("SELECT LAST_INSERT_ID()")) === FALSE) {
die(mysql_error());
}
if (($row = mysql_fetch_array($result)) === FALSE) {
die(mysql_error());
}
$id = $row[0];
回答by Marco Demaio
I agree with whoever says you should use mysql_insert_id, but if you want to use LAST_INSERT_ID, you can use this:
我同意谁说你应该使用mysql_insert_id，但如果你想使用LAST_INSERT_ID，你可以使用这个：
function getLastInsertId($db_connection)
{
$result = 0;
if($query_result = mysql_query("SELECT LAST_INSERT_ID();", $db_connection))
{
$temp = mysql_fetch_row($query_result);
$result = $temp[0];
}
return $result;
}
回答by Thangaraj
I think your table has datetime/timestampcolumn and see your query has NOW()varcharvalue instead of datetimevalue, so your SQL queryshould have return false.
我认为你的表有datetime/timestamp列，并且看到你的查询有NOW()varchar值而不是datetime值，所以你SQL query应该有 return false。
If the query return falseyou will not get last inserted id (always for current connection).
如果查询返回，false您将不会获得最后插入的 id（始终用于当前连接）。
回答by Kris Krause
SELECT LAST_INSERT_ID();
If I were you. I would get your insert/select last_insert_id to work from the command line or query browser first, before php. At minimum, this will at least confirm or deny correct sql syntax.
如果我是你。我会让你的插入/选择 last_insert_id 在 php 之前首先从命令行或查询浏览器工作。至少，这将至少确认或拒绝正确的 sql 语法。
