插入SQL表或更新记录（如果存在）

Question

我想向数据库表中添加一行，但如果存在具有相同唯一键的行，我想更新该行。

我的问题是：

$query = "INSERT INTO audit_section_markrecord(`audit_section_id`,`form_details_subquestion_id`,`form_details_section_id`,`mark`) VALUES ('$audit_no','$form_details_subquestion_id','$form_details_section_id','$mark') 
ON DUPLICATE KEY UPDATE mark = VALUES($mark)";

但是，它将继续插入新的记录。假设audit\u section\u id，form\u details\u subquestion\u id，form\u details\u section\u id是唯一键。如果audit\u section\u id、form\u details\u subquestion\u id、form\u details\u section\u id存在，则不会将新记录插入数据库；同时更新记录。

我也试过这个：

REPLACE INTO audit_section_markrecord(`audit_section_id`,`form_details_subquestion_id`,`form_‌​details_section_id`,`mark`)
VALUES ('$audit_no','$form_details_subquestion_id','$form_details_section_id','$mark')

我不确定我的代码是否存在for循环问题。

    for ($i=0; $i < ($_POST['count']); $i++)
{
    $form_details_subquestion_id    = $_POST['form_details_subquestion_id'][$i];
    $form_details_section_id        = $_POST['form_details_section_id'][$i];
    $mark                           = $_POST['mark'][$i];
    $remark                         = $_POST['remark'][$i]; 


    //$query = "INSERT INTO `table` (`id`, `name`, `email`) VALUES (' ".$_POST['id']." ', ' ".$_POST['name']." ', ' ".$_POST['email']." ')";    

    $query = "REPLACE INTO audit_section_markrecord(`audit_section_id`,`form_details_subquestion_id`,`form_details_section_id`,`mark`)
    VALUES ('$audit_no','$form_details_subquestion_id','$form_details_section_id','$mark')";
    $result = $db->query($query);
}

我使用for循环插入所有数据。

这是echo$query的输出；

  REPLACE INTO audit_section_markrecord(`audit_section_id`,`form_details_subquestion_id`,`form_details_section_id`,`mark`) VALUES ('602633','89','1','1.00')
REPLACE INTO audit_section_markrecord(`audit_section_id`,`form_details_subquestion_id`,`form_details_section_id`,`mark`) VALUES ('602633','86','1','1.00')
REPLACE INTO audit_section_markrecord(`audit_section_id`,`form_details_subquestion_id`,`form_details_section_id`,`mark`) VALUES ('602633','87','1','1.00')
REPLACE INTO audit_section_markrecord(`audit_section_id`,`form_details_subquestion_id`,`form_details_section_id`,`mark`) VALUES ('602633','88','1','1.00')
REPLACE INTO audit_section_markrecord(`audit_section_id`,`form_details_subquestion_id`,`form_details_section_id`,`mark`) VALUES ('602633','85','1','1.00')
REPLACE INTO audit_section_markrecord(`audit_section_id`,`form_details_subquestion_id`,`form_details_section_id`,`mark`) VALUES ('602633','83','1','1.00')
REPLACE INTO audit_section_markrecord(`audit_section_id`,`form_details_subquestion_id`,`form_details_section_id`,`mark`) VALUES ('602633','84','1','1.00')
REPLACE INTO audit_section_markrecord(`audit_section_id`,`form_details_subquestion_id`,`form_details_section_id`,`mark`) VALUES ('602633','81','1','1.00')
REPLACE INTO audit_section_markrecord(`audit_section_id`,`form_details_subquestion_id`,`form_details_section_id`,`mark`) VALUES ('602633','82','1','1.00')
REPLACE INTO audit_section_markrecord(`audit_section_id`,`form_details_subquestion_id`,`form_details_section_id`,`mark`) VALUES ('602633','98','1','2')
REPLACE INTO audit_section_markrecord(`audit_section_id`,`form_details_subquestion_id`,`form_details_section_id`,`mark`) VALUES ('602633','99','1','2')
REPLACE INTO audit_section_markrecord(`audit_section_id`,`form_details_subquestion_id`,`form_details_section_id`,`mark`) VALUES ('602633','100','1','2')
REPLACE INTO audit_section_markrecord(`audit_section_id`,`form_details_subquestion_id`,`form_details_section_id`,`mark`) VALUES ('602633','101','1','2')
REPLACE INTO audit_section_markrecord(`audit_section_id`,`form_details_subquestion_id`,`form_details_section_id`,`mark`) VALUES ('602633','102','1','2')
REPLACE INTO audit_section_markrecord(`audit_section_id`,`form_details_subquestion_id`,`form_details_section_id`,`mark`) VALUES ('602633','96','1','2')
REPLACE INTO audit_section_markrecord(`audit_section_id`,`form_details_subquestion_id`,`form_details_section_id`,`mark`) VALUES ('602633','97','1','2')
REPLACE INTO audit_section_markrecord(`audit_section_id`,`form_details_subquestion_id`,`form_details_section_id`,`mark`) VALUES ('602633','90','1','2')
REPLACE INTO audit_section_markrecord(`audit_section_id`,`form_details_subquestion_id`,`form_details_section_id`,`mark`) VALUES ('602633','91','1','2')
REPLACE INTO audit_section_markrecord(`audit_section_id`,`form_details_subquestion_id`,`form_details_section_id`,`mark`) VALUES ('602633','92','1','2')
REPLACE INTO audit_section_markrecord(`audit_section_id`,`form_details_subquestion_id`,`form_details_section_id`,`mark`) VALUES ('602633','93','1','2')
REPLACE INTO audit_section_markrecord(`audit_section_id`,`form_details_subquestion_id`,`form_details_section_id`,`mark`) VALUES ('602633','94','1','2')
REPLACE INTO audit_section_markrecord(`audit_section_id`,`form_details_subquestion_id`,`form_details_section_id`,`mark`) VALUES ('602633','95','1','2')

SQL表

   CREATE TABLE IF NOT EXISTS `audit_section_markrecord` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `audit_section_id` int(10) unsigned NOT NULL,
  `form_details_subquestion_id` int(10) unsigned NOT NULL,
  `form_details_section_id` int(10) unsigned NOT NULL,
  `mark` decimal(5,2) unsigned NOT NULL,
  `dateofmodify` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
)

Answer 1

在最后一个问题后编辑编辑：

您的表格中有以下内容：

`id` int(11) NOT NULL AUTO_INCREMENT,
....
 PRIMARY KEY (`id`)

您的id（主键）是自动递增的，您的问题是：

我想向数据库表中添加一行，但如果存在具有相同唯一键的行，我想更新该行。

对于查询，您永远不会有相同的id，因为您从未在查询中设置id值，因此您的查询是：

REPLACE INTO audit_section_markrecord(`audit_section_id`,`form_details_subquestion_id`,`form_details_section_id`,`mark`) VALUES ('602633','95','1','2')

如果您没有在查询中设置id值，数据库系统会自动递增id列值，并且您永远不必更新行。

这应该是您的查询：

$query = "REPLACE INTO audit_section_markrecord(`audit_section_id`,`form_details_subquestion_id`,`form_details_section_id`,`mark`)
    VALUES ('$audit_no','$form_details_subquestion_id','$form_details_section_id','$mark')";

但是审核部分\u id、表格详细信息\u子问题\u id和表格详细信息\u部分\u id列值必须与要替换的行的现有行值匹配；否则，将插入一行。

您可以在以下网址阅读更多内容：

http://dev.mysql.com/doc/refman/5.7/en/replace.html

例：

考虑下面的CREATETABLE语句创建的表：

CREATE TABLE test (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  data VARCHAR(64) DEFAULT NULL,
  ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
);

创建此表并运行mysql客户端中显示的语句时，结果如下：

mysql> REPLACE INTO test VALUES (1, 'Old', '2014-08-20 18:47:00');
Query OK, 1 row affected (0.04 sec)

mysql> REPLACE INTO test VALUES (1, 'New', '2014-08-20 18:47:42');
Query OK, 2 rows affected (0.04 sec)

mysql> SELECT * FROM test; 
+----+------+---------------------+
| id | data | ts                  |
+----+------+---------------------+
|  1 | New  | 2014-08-20 18:47:42 |
+----+------+---------------------+
1 row in set (0.00 sec)

现在，如果您创建的第二个表几乎与第一个表相同，除了主键现在覆盖2列，如图所示（PRIMARYKEY（id， ts））：

CREATE TABLE test2 (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  data VARCHAR(64) DEFAULT NULL,
  ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id, ts)
);

当您在test2上运行与我们在原始（第一个）测试表上相同的两个REPLACE语句时，我们得到了不同的结果：

mysql> REPLACE INTO test2 VALUES (1, 'Old', '2014-08-20 18:47:00');
Query OK, 1 row affected (0.05 sec)

mysql> REPLACE INTO test2 VALUES (1, 'New', '2014-08-20 18:47:42');
Query OK, 1 row affected (0.06 sec)

mysql> SELECT * FROM test2;
+----+------+---------------------+
| id | data | ts                  |
+----+------+---------------------+
|  1 | Old  | 2014-08-20 18:47:00 |
|  1 | New  | 2014-08-20 18:47:42 |
+----+------+---------------------+
2 rows in set (0.00 sec)

这是因为，当在test2上运行时，要替换的行的id和ts列值都必须与现有行的值相匹配；否则，将插入一行。