LAST_INSERT_ID()是否等于$ db-> insert_id? [英] LAST_INSERT_ID() is unequal to $db->insert_id?

问题描述

我有以下查询:

$year         = 2019;
$month        = 6;
$stmt         = $db->prepare('INSERT INTO officeRechNr (jahr,monat,zahl) VALUES (?,?,1) ON DUPLICATE KEY UPDATE zahl = LAST_INSERT_ID(zahl+1)');
$stmt->bind_param('ii', $year, $month);
$stmt->execute();
echo $db->insert_id;

echo '|';

$sql = 'SELECT LAST_INSERT_ID() as number';
$result = $db->query($sql);
$row = $result->fetch_assoc();
echo $row['number'];
echo '<br>';

表officeRechNr具有唯一的主索引['jahr','monat']，而zahl是具有autoincrement的index.

The table officeRechNr has the unique primary index ['jahr','monat'] and zahl is an index with autoincrement.

如果表officeRechNr为空，并且我执行了3次代码，则输出为

If the table officeRechNr is empty, and I execute the code 3 times, then the output is

1 | 0

1|0

2 | 2

3 | 3 ...

3|3 ...

为什么LAST_INSERT_ID()插入后为零，但升级后正确? 我该如何更改查询，以便两个函数在插入后输出相同的数字(1)?

Why is LAST_INSERT_ID() zero after insert, but correct after upgrade? How do I need to change my query, so that both functions output the same number (1) after insert?

代码的目的是，我需要在特定的年月中创建的每个发票，使用第三个唯一的升序编号.因此，例如，如果我们在2015年和5月(3)月份有7张发票，那么我将拥有以下编号

The purpose of the code is that I need for each invoice that is created in a specific year and month a third unique ascending number. So for example if we have 7 invoices in the year 2015 and month May (3),then I would have the folloing numbers

2015-3-1
2015-3-2
2015-3-3
2015-3-4
2015-3-5
2015-3-6
2015-3-7

因此，在数据库的行中，我存储了当前的发票编号，并使用上面显示的SQL命令可以获取下一个编号.列zahl是autoincrement字段的唯一原因是该数字由insert_id返回(请参见

So in the row in the database I have stored the current invoice number and with the SQL command presented above I can get the next number. The only reason why the column zahl is an autoincrement field is that the number is returned by insert_id (see https://dev.mysql.com/doc/refman/5.7/en/getting-unique-id.html). Its also necessary to get it through insert_id in case that people create simultaneously invoices.

推荐答案

问题在于，带有参数的LAST_INSERT_ID(...);不会返回生成的ID，而是在LAST_INSERT_ID()的内存"中设置给定值，并且返回它.因此，在第一次执行时，不会生成自动递增的ID(您自己提供了值)，并且LAST_INSERT_ID()返回0.在接下来的执行中，将值next+1保存在LAST_INSERT_ID()的内部存储器中，该内部存储器返回该值.在 12.14信息功能:

The problem is that LAST_INSERT_ID(...); with an argument doesn't return the generated ID but instead set the given value in the "memory" of LAST_INSERT_ID() and returns it. So, in your first execution no auto incremented ID was generated (you provided the value by yourself) and LAST_INSERT_ID() return 0. In your following executions you save the value next+1 in the internal storage of LAST_INSERT_ID(), which returns the value. This behavior is described in the MySQL in 12.14 Information Functions:

如果将expr用作LAST_INSERT_ID()的参数，则该参数的值由函数返回，并记住该值作为LAST_INSERT_ID()返回的下一个值.

If expr is given as an argument to LAST_INSERT_ID(), the value of the argument is returned by the function and is remembered as the next value to be returned by LAST_INSERT_ID().

实际上，您可以跳过LAST_INSERT_ID()呼叫并在没有呼叫的情况下工作.

In fact, you can skip the LAST_INSERT_ID() call and work without it.

INSERT INTO
    officeRechNr (jahr,monat,zahl)
VALUES
    (?,?,1)
ON DUPLICATE KEY UPDATE zahl = zahl+1

这将插入行(具有给定的值)或增加计数器.

This will insert the row (with the given value) or increase the counter.

如果想要给定年份和月份的当前计数器，则运行简单的SELECT语句.请记住，您可能需要事务或锁，因为其他客户端可能会在使用SELECT语句获取计数器之前增加计数器.

If you want the current counter for a given year and month you run a simple SELECT statement. Keep in mind that you might need transactions or locks because a different client could increase the counter before you fetched it with the SELECT statement.

这篇关于LAST_INSERT_ID()是否等于$ db-> insert_id?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！