php - 如果存在则更新 SQL 行，如果不存在则创建(在 PHP 中)

Question

我试图在不知道用户是否存在的情况下更新该用户的值。

基本上:如果用户不存在，则创建它并更新值。如果存在，则更新该值。

我是否正确使用了ON DUPLICATE？我可以在使用 ON DUPLICATE 时使用 WHERE 吗？

$sql = "INSERT INTO Users ('$column_name[$i]') VALUES ('$value[$i]') ON DUPLICATE KEY UPDATE '$column_name[$i]'='$value[$i]' WHERE LoginName = '$login_name'";

我在这行代码中遇到的错误是:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''record_id') VALUES ('5287469') ON DUPLICATE KEY UPDATE 'record_id' at line 1

关于如何形成查询有什么建议吗？

Answer 1

WHERE 子句不能与 INSERT ... VALUES 语句一起使用。

有没有 ON DUPLICATE KEY 子句并不重要。

MySQL 引用手册揭示(以一种神秘的方式)INSERT ... VALUES 语句中没有 WHERE 子句:

https://dev.mysql.com/doc/refman/5.7/en/insert.html

(INSERT ... SELECT 语句可以在语句的 SELECT 部分包含 WHERE 子句，就像普通的 SELECT语句。)

<小时/>

请勿在标识符(例如列名称)周围使用单引号。

如果需要转义，请使用单个反引号字符。

<小时/>

在 SQL 文本中包含潜在不安全的值会引发一系列令人讨厌的漏洞，这些漏洞被归类为 SQL 注入(inject)。

https://www.owasp.org/index.php/SQL_Injection

这可能是 SQL 文本中包含的值已被正确清理或列入白名单。但我们在代码中没有看到这一点，因此敲响了警钟。

<小时/>

规范模式(仅 SQL)将是这样的:

 INSERT INTO Users
 ( `primary_or_unique_key_column` 
 , `some_column_name` 
 ) VALUES 
 ( 'safe_unique_key_value'
 , 'safe_column_value'
 )
 ON DUPLICATE KEY
    UPDATE `some_column_name` = VALUES(`some_column_name`)

<小时/>

如果LoginName是表中的主键或唯一键

 INSERT INTO Users
 ( `loginname`
 , `record_id` 
 ) VALUES 
 ( 'safe_loginname_value'
 , 'safe_record_id_value'
 )
 ON DUPLICATE KEY
    UPDATE `record_id` = VALUES(`record_id`)

请注意，在列名称周围使用反引号，在文字字符串值周围使用单引号。

另请注意 VALUES 函数的用法，如果 INSERT 成功，该函数将引用已插入到列中的值。 (避免我们必须两次提供相同的值。

使用带有绑定(bind)占位符的准备好的语句，我们需要正确验证合并到 SQL 文本中的列名(以避免 SQL 注入(inject)漏洞...但是值可以通过占位符。

SQL 文本如下所示:

 INSERT INTO Users
 ( `loginname`
 , `record_id` 
 ) VALUES 
 ( ?
 , ?
 )
 ON DUPLICATE KEY
    UPDATE `record_id` = VALUES(`record_id`)

代码(假设 PDO)将采用以下形式:

$sth=$dbh->prepare($sql_text);
# check return from prepare

$sth->bindValue(1, $loginname_val, PDO::PARAM_STR);
$sth->bindValue(2, $record_id_val, PDO::PARAM_INT);

$sth->execute();
# check return from execute