php - 检查唯一键并更新行或创建一个新的 WordPress

Question

我有一个可拖动的 div，其中位置被保存到数据库，user_id 设置为唯一。我将如何检查 user_id 是否存在……如果存在，则更新其他 3 列。如果不存在，则插入新行。我读过使用“ON DUPLICATE KEY UPDATE”，但很难实现它。有什么想法吗？

正如@N.B. 所解释的。 - 工作解决方案

global $wpdb;

    $_POST['user_id'];
    $_POST['div_id'];
    $_POST['x_pos'];
    $_POST['y_pos'];

    $user_id    = $_POST['user_id'];
    $div_id     = $_POST['div_id'];
    $x_pos      = $_POST['x_pos'];
    $y_pos      = $_POST['y_pos'];

$wpdb->query($wpdb->prepare(" INSERT INTO coords

   (user_id, div_id, x_pos, y_pos)
    VALUES (%d, %s, %d, %d)
    ON DUPLICATE KEY UPDATE
    x_pos = VALUES(x_pos), y_pos = VALUES(y_pos)",

    $user_id,
    $div_id,
    $x_pos,
    $y_pos
    ));

Answer 1

作为@N.B.在评论中指出，虽然我提交的第一种方法有效，但它对竞争条件开放。我要感谢他指出这一点。这是带有竞争条件的第一个解决方案:

$user_exists = $wpdb->get_var(
    $wpdb->prepare("SELECT user_id FROM coords WHERE user_id = %d", $user_id)
);
if($user_exists) {
    // Do Update
}
else {
    // Do Insert
}

这些竞争条件是天文数字，因为插入必须在第一个查询返回和下一个插入查询之间的时间内完成执行。但是竞争条件仍然存在，因此可能会在某个时间点发生。

但是您的数据库仍然是安全的。当它发生时，它不会复制数据，而是会抛出一个 wpdb 错误，表明唯一键已经存在，并且插入将无声地失败(它不会终止脚本，也不会输出错误，除非错误报告已打开)。

WordPress database error: [Duplicate entry '3' for key 'PRIMARY']

令人惊讶的是，上述技术被无数插件和主题作者用于 Wordpress 核心，我只能找到两个在 Wordpress 核心中正确使用“ON DUPLICATE”的实例。因此，互联网上的大部分内容都以这种竞争条件的多个实例运行，这似乎还不错，只是为了让您了解我们正在谈论的天文数字机会。

无论机会如何，使用它都是不好的做法。作为 N.B.评论说，数据库应该担心数据而不是 PHP。

真正的解决方案

无论出于何种原因，Wordpress 都没有“在重复更新时插入”功能，这意味着您必须每次使用 $wpdb->query 编写一个查询，或者构建您自己的函数来处理它。我开始编写一个函数，因为每次编写 wpdb->query 都很痛苦，并且使用户更接近意外的 mysql 注入(inject)。还有开发速度。

/**
 * Insert on Duplicate Key Update.
 *
 * Wordpress does not have an 'insert on duplicate key update' function, which
 * forces user's to create their own or write standard queries. As writing
 * queries is annoying and open to mysql injection via human error, this function
 * automates this custom query in an indentical fashion to the core wpdb functions.
 * Source: http://stackoverflow.com/a/31150317/4248167
 *
 * @global wpdb $wpdb
 * @param string $table The table you wish to update.
 * @param array $data The row you wish to update or insert, using a field => value associative array.
 * @param array $where The unique keys that you want to update at or have inserted, also a field => value array.
 * @param array $data_formats Wordpress formatting array for the data. Will default to %s for every field.
 * @param array $where_formats Wordpress formatting array for the where. Will default to %s for every field.
 * @return boolean True if successfully inserted or updated the row.
 */
function insertOrUpdate($table, $data, $where, $data_formats = array(), $where_formats = array())
{
    if(!empty($data) && !empty($where)) {
        global $wpdb;
        // Data Formats - making sure they match up with the data.
        $default_data_format = (isset($data_formats[0])) ? $data_formats[0] : '%s';
        $data_formats = array_pad($data_formats, count($data), $default_data_format);
        $data_formats = array_splice($data_formats, 0, count($data));
        // Where Formats - making sure they match up with the where data.
        $default_where_format = (isset($where_formats[0])) ? $where_formats[0] : '%s';
        $where_formats = array_pad($where_formats, count($where), $default_where_format);
        $where_formats = array_splice($where_formats, 0, count($where));
        // Get Fields
        $data_fields = array_keys($data);
        $where_fields = array_keys($where);
        // Create Query
        $query =
            "INSERT INTO $table" .
            " (" . implode(', ', array_merge($data_fields, $where_fields)) . ")" .
            " VALUES(" . implode(', ', array_merge($data_formats, $where_formats)) . ")" .
            " ON DUPLICATE KEY UPDATE";
        // Compile update fields and add to query
        $field_strings = array();
        foreach($data_fields as $index => $data_field) {
            $field_strings[] = " $data_field = " . $data_formats[$index];
        }
        $query .= implode(', ', $field_strings);
        // Put it all together - returns true on successful update or insert 
        // and false on failure or if the row already matches the data.
        return !!$wpdb->query(
            $wpdb->prepare(
                $query,
                array_merge(
                    array_merge(
                        array_values($data),
                        array_values($where)
                    ),
                    array_values($data)
                )
            )
        );
    }
    return false;
}

要使用它，您只需像调用 $wpdb->update 函数一样输入参数。

insertOrUpdate(
    'testing_table',
    array('column_a' => 'hello', 'column_b' => 'world'),
    array('id' => 3),
    array('%s', '%s'),
    array('%d')
);

在您的情况下，它将是:

insertOrUpdate(
    'coords', 
    array('div_id' => $div_id, 'x_pos' => $x_pos, 'y_pos' => $y_pos), 
    array('user_id' => $user_id),
    array('%d', '%d', '%d'),
    array('%d')
);

或者您可以只使用默认格式:

insertOrUpdate(
    'coords', 
    array('div_id' => $div_id, 'x_pos' => $x_pos, 'y_pos' => $y_pos), 
    array('user_id' => $user_id),
    array('%d')
);

或者你可以忽略默认格式化为字符串的格式:

insertOrUpdate(
    'coords', 
    array('div_id' => $div_id, 'x_pos' => $x_pos, 'y_pos' => $y_pos), 
    array('user_id' => $user_id)
);

如果您发现任何问题，请告诉我。