dongzhuoxie1244 2017-11-22 04:21
浏览 437
已采纳

Laravel:插入后LAST_INSERT_ID()为0?

I have the following working query:

$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;

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

So if the table officeRechNr is empty, and I execute the code 5 times, then the output is

1, 2, 3, 4, 5

I tried to translate this in Laravel 5 with

\DB::select(DB::raw('INSERT INTO officeRechNr (jahr,monat,zahl) VALUES (?,?,1) ON DUPLICATE KEY UPDATE zahl = LAST_INSERT_ID(zahl+1)'),[2019,6]);

return DB::select('SELECT LAST_INSERT_ID()');

However, if I execute this code 5 times I get

0, 2, 3, 4, 5

Although it returns 0 after the first insert, there is a 1 in the zahl column.

enter image description here

Why does my Laravel Code not return 1 after the first insert?

  • 写回答

1条回答 默认 最新

  • duanhe1965 2017-11-23 15:02
    关注

    Short Answer: SELECT_LAST_INSERT_ID() will not work on first insert(see example below). Instead use \DB::getPdo()->lastInsertId();


    Why SELECT_LAST_INSERT_ID() will not work on first insert

    Its stated here: https://dev.mysql.com/doc/refman/5.7/en/information-functions.html#function_last-insert-id

    With no argument, LAST_INSERT_ID() returns a BIGINT UNSIGNED (64-bit) value representing the first automatically generated value successfully inserted for an AUTO_INCREMENT column as a result of the most recently executed INSERT statement.

    With automatically generated is meant that the auto column increased itself and is not set by me.

    Example:

    Image a table test like this:

    CREATE TABLE test (
           id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
           name VARCHAR(10) NOT NULL
           );
    

    Now

    $sql = 'INSERT INTO test ( id, name ) VALUES (33, "ADAM")';
    $db->query($sql);
    echo $db->insert_id;
    $result =  $db->query('SELECT LAST_INSERT_ID()');
    $row = $result->fetch_assoc();
    print_r($row);
    

    returns

    33

    array('SELECT_LAST_INSERT_ID()' => 0);

    Doing the same with

    $sql = 'INSERT INTO test (  name ) VALUES ("Eba")';
    

    returns

    1

    array('SELECT_LAST_INSERT_ID()' => 1);

    So in the first statement:

    $stmt         = $db->prepare('INSERT INTO officeRechNr (jahr,monat,zahl) VALUES (?,?,1) ON DUPLICATE KEY UPDATE zahl = LAST_INSERT_ID(zahl+1)');
    

    SELECT_LAST_INSERT_ID() is 0 because the AI key was not automatically generated, although the id is 1 in the table. The reason why it works for the sequent numbers is

    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().

    展开全部

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
编辑
预览

报告相同问题?

手机看
程序员都在用的中文IT技术交流社区

程序员都在用的中文IT技术交流社区

专业的中文 IT 技术社区,与千万技术人共成长

专业的中文 IT 技术社区,与千万技术人共成长

关注【CSDN】视频号,行业资讯、技术分享精彩不断,直播好礼送不停!

关注【CSDN】视频号,行业资讯、技术分享精彩不断,直播好礼送不停!

客服 返回
顶部