Detect duplicate key issues when using mysqli::get_warnings

Why do I need to use mysqli::get_warnings() ?

When executing batch INSERT operations, if INSERT IGNORE or ON DUPLICATE KEY UPDATE is used, MySQL will not directly report an error, but will generate warnings. At this time, it is difficult for us to obtain specific conflict information through the error capture mechanism. mysqli::get_warnings() can get these warnings to help us understand which records conflict.

Sample code

Suppose there is the following data table users , and a unique key email is defined:

 CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50),
  email VARCHAR(100) UNIQUE
);

We try to insert multiple pieces of data in batches with duplicate emails :

 <?php
$mysqli = new mysqli("localhost", "root", "password", "testdb");

if ($mysqli->connect_errno) {
    die("Failed to connect to the database: " . $mysqli->connect_error);
}

// Prepare batch insertionSQLStatement
$sql = "INSERT IGNORE INTO users (name, email) VALUES 
        ('Alice', '[email protected]'), 
        ('Bob', '[email protected]'),
        ('Charlie', '[email protected]')";  // Pay attention to theemailrepeat

if ($mysqli->query($sql)) {
    echo "Insert complete。<br>";

    // pass get_warnings() 检测是否有repeat键冲突警告
    if ($warning = $mysqli->get_warnings()) {
        do {
            echo "Warning level: " . $warning->get_errno() . "<br>";
            echo "Warning message: " . $warning->get_message() . "<br>";
        } while ($warning->next());
    } else {
        echo "No warning message。";
    }
} else {
    echo "Insert failed: " . $mysqli->error;
}

$mysqli->close();

In the above code, we use INSERT IGNORE to try to insert data. When a duplicate email is encountered, MySQL ignores the insertion of the record, but generates a warning. These warning information can be obtained through $mysqli->get_warnings() to know that there is a duplicate key conflict.

Detailed explanation of mysqli_warning object

mysqli::get_warnings() returns a mysqli_warning object. You can call the following method to get detailed information:

• get_errno() : Get the error code for the warning

• get_message() : Get the warning message text

• next() : Move to the next warning (if any)

Usually, the warning code for repeated key conflicts is 1062, and the corresponding message will contain "Duplicate entry".

Things to note

1. If INSERT IGNORE or similar policies are not used, duplicate key conflicts can directly cause the query to fail, returning an error rather than a warning.

2. get_warnings() returns a warning chain, and requires loop traversal to get all warnings.

3. The warning information is relatively detailed and can be used to accurately locate conflicting records.

Through mysqli::get_warnings() , we can handle duplicate key conflicts more elegantly during batch insertion, which not only ensures the continuity of data insertion, but also promptly understands data quality issues and improves the robustness of the program.

 <?php
$mysqli = new mysqli("localhost", "root", "password", "testdb");

if ($mysqli->connect_errno) {
    die("Failed to connect to the database: " . $mysqli->connect_error);
}

$sql = "INSERT IGNORE INTO users (name, email) VALUES 
        ('Alice', '[email protected]'), 
        ('Bob', '[email protected]'),
        ('Charlie', '[email protected]')";

if ($mysqli->query($sql)) {
    echo "Insert complete。<br>";

    if ($warning = $mysqli->get_warnings()) {
        do {
            echo "Warning level: " . $warning->get_errno() . "<br>";
            echo "Warning message: " . $warning->get_message() . "<br>";
        } while ($warning->next());
    } else {
        echo "No warning message。";
    }
} else {
    echo "Insert failed: " . $mysqli->error;
}

$mysqli->close();