我想向数据库表添加一行,但是如果存在具有相同唯一键的行,我想更新该行。
这是我的查询:
$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`)
)
在你最后一个问题之后编辑:
在你的表中有:
`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)
现在,如果您创建第二个表,除了主键现在覆盖2列之外,与第一个表几乎相同,如下所示(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列的值都必须与要替换的现有行的值相匹配;否则,插入一行。