PHP mysqli:如果唯一冲突，则插入行或返回id - PHP mysqli: Insert row or return id if unique conflict

PHP mysqli: Insert row or return id if unique conflict

我有一个包含权重值的表：

TABLE_WEIGHT
    WEIGHT_ID bigint(20) NOT NULL PRIMARY KEY AUTO_INCREMENT,
    WEIGHT float NOT NULL UNIQUE

我插入值：

$query = "INSERT INTO TABLE_WEIGHT (WEIGHT) VALUES(100)";
if ($stmt = $this->mysqli->prepare($query)) : 
    if (!stmt->execute()) : 
        // some error
    else :
        // alright
    endif;
endif;

我第一次这样做不会有任何问题。如果我第二次这样做，值100已经存在。现在有没有一种方法可以只获取冲突行的id，而不需要新的select语句？

您可以通过INSERT。。。关于重复密钥更新和LAST_INSERT_ID（expr）的鲜为人知的形式

<?php
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'localonly', 'localonly', array(
    PDO::ATTR_EMULATE_PREPARES=>false,
    PDO::MYSQL_ATTR_DIRECT_QUERY=>false,
    PDO::ATTR_ERRMODE=>PDO::ERRMODE_EXCEPTION
));
setup($pdo);
$stmt = $pdo->prepare('
    INSERT INTO
        soFoo
        (WEIGHT)
    VALUES
        (?)
    ON DUPLICATE KEY UPDATE
        WEIGHT_ID=LAST_INSERT_ID(WEIGHT_ID),
        dummy=NOT dummy
');
foreach( array(100,200,300,100,400) as $w ) {
    $stmt->execute( array($w) );
    echo $w, ' -> ', $pdo->lastInsertId(), "'r'n";
}

foreach( $pdo->query('SELECT WEIGHT_ID,WEIGHT FROM soFoo', PDO::FETCH_ASSOC) as $row) {
    echo join(', ', $row), "'r'n";
}

function setup($pdo) {
    $pdo->exec('
        CREATE TEMPORARY TABLE soFoo (
            WEIGHT_ID bigint(20) NOT NULL AUTO_INCREMENT,
            WEIGHT float NOT NULL UNIQUE,
            dummy   int  NOT NULL DEFAULT 0,
            PRIMARY KEY(WEIGHT_ID),
            UNIQUE KEY(WEIGHT)
        )
    ');
}

打印

（注意缺少WEIGHT_ID=4）