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

debugcn 发表于 Dev

安德鲁

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

这是我的查询：

$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_section_id, form_details_subquestion_id, form_details_section_id是唯一键。如果audit_section_id, form_details_subquestion_id, form_details_section_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`)
)

阿德里安·西德·阿尔玛格（Adrian Cid Almaguer）

在最后一个问题后编辑：

您在表中有以下内容：

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

您的id（PRIMARY KEY）是自动递增的，您的问题是：

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

对于您的查询，您将永远不会拥有相同的查询，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')";

但是audit_section_id，form_details_subquestion_id和form_details_section_id列的值必须与现有行的值匹配，才能替换该行；否则，将插入一行。

您可以在以下位置阅读更多内容：

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

例子：

考虑下面的CREATE TABLE语句创建的表：

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)

现在，如果您创建与第二张表几乎相同的第二张表，除了主键现在覆盖两列，如此处（PRIMARY KEY (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列值都必须与现有行的值匹配才能替换该行；否则，将插入一行。

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。