我有一个脚本,我已经设置了一个CRON来通过JSON(cURL)从第三方服务器获取值
现在,每次cron运行时,它都会插入一个全新的记录.造成重复,并导致我手动删除公仔.
我将如何防止重复,并且仅更新丢失的信息或与$var值不同的信息?
我想做的事
如果新值不是旧值,请使用旧值,否则请使用新值;
$prep_stmt = "SELECT * FROM members WHERE record NOT LIKE record=? ";
$stmt = $mysqli->prepare($prep_stmt);
if ($stmt) {
$stmt->bind_param('s');
$stmt->execute();
$stmt->store_result();
if ($stmt->num_rows !== 1) {
if ($insert_stmt = $mysqli->prepare("
INSERT INTO members (
start_date
)
VALUES (?)"))
{
$insert_stmt->bind_param('s',$repStartDate);
if (! $insert_stmt->execute()) {header('Location: ../error.php?err=Registration failure: INSERT');}
}
}
}
解决方法:
如果您不想在记录存在时进行更新,则可以使用“插入忽略”,如下所示.
mysql> SELECT * FROM visit;
Empty set (0.00 sec)
mysql> INSERT IGNORE INTO visit (user_id, total_visit) VALUES (32, 1);
Query OK, 1 row affected (0.01 sec)
mysql> INSERT IGNORE INTO visit (user_id, total_visit) VALUES (32, 1);
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM visit;
+----+---------+-------------+
| id | user_id | total_visit |
+----+---------+-------------+
| 1 | 32 | 1 |
+----+---------+-------------+
1 row in set (0.00 sec)
如果要在记录存在时进行更新,则可以使用“重复键更新”,如下所示.
mysql> INSERT IGNORE INTO visit (user_id, total_visit) VALUES (32, 1) ON DUPLICATE KEY UPDATE total_visit = total_visit + VALUES(total_visit);
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM visit;
+----+---------+-------------+
| id | user_id | total_visit |
+----+---------+-------------+
| 1 | 32 | 1 |
+----+---------+-------------+
1 row in set (0.00 sec)
mysql> INSERT IGNORE INTO visit (user_id, total_visit) VALUES (32, 1) ON DUPLICATE KEY UPDATE total_visit = total_visit + VALUES(total_visit);
Query OK, 2 rows affected (0.00 sec)
mysql> SELECT * FROM visit;
+----+---------+-------------+
| id | user_id | total_visit |
+----+---------+-------------+
| 1 | 32 | 2 |
+----+---------+-------------+
1 row in set (0.00 sec)
标签:curl,mysql,php
来源: https://codeday.me/bug/20191119/2036040.html