假设我们有一个mysql表,如下所示
id, userid, days, start, and close
每列的数据都是这样-
1, 3, mon, 01.00, 02.00,
2, 3, tue, 03.00, 05.00,
3, 3, wed, 04.00, 06.00,
4, 3, thu, 05.00, 07.00,
5, 3, fri, 06.00, 08.00,
6, 3, sat, 07.00, 10.00,
7, 3, sun, 08.00, 12.00,对于这些数据,我需要更新或插入我的表。(如果表中不存在userid,则应该插入,或者如果db中存在userid,则应该进行更新。)
我可以知道,是否有一种方法可以使mysql对此进行单次查询?我尝试过使用INSERT ... ON DUPLICATE KEY UPDATE,在那里,我只能编辑单行,这意味着我不能使用INSERT ... ON DUPLICATE KEY UPDATE插入或更新多行表。
目前,我已经使用了两个不同的内联和更新查询。
这是我的插入查询-
$q = "INSERT INTO availability (userid, days, opentime, closetime)
VALUES (?, 'Monday', ?, ?),
(?, 'Tuesday', ?, ?),
(?, 'Wednesday', ?, ?),
(?, 'Thursday', ?, ?),
(?, 'Friday', ?, ?),
(?, 'Saturday', ?, ?),
(?, 'Sunday', ?, ?)";
$stmt = mysqli_prepare($dbc, $q);
mysqli_stmt_bind_param($stmt, 'issississississississ',
$userId, $monOpen, $monClose,
$userId, $tueOpen, $tueClose,
$userId, $wedOpen, $wedClose,
$userId, $thuOpen, $thuClose,
$userId, $friOpen, $friClose,
$userId, $satOpen, $satClose,
$userId, $sunOpen, $sunClose);
// Execute the query:
mysqli_stmt_execute($stmt);发布于 2013-09-06 06:44:28
如果在(userid, days)上添加唯一索引
-- run this only once
ALTER TABLE availability
ADD UNIQUE INDEX userid_days_UQ -- just a name for the index
(userid, days) ;然后可以使用ON DUPLICATE KEY UPDATE语法:
$q = " INSERT INTO availability
(userid, days, opentime, closetime)
VALUES
(?, 'Monday', ?, ?),
(?, 'Tuesday', ?, ?),
(?, 'Wednesday', ?, ?),
(?, 'Thursday', ?, ?),
(?, 'Friday', ?, ?),
(?, 'Saturday', ?, ?),
(?, 'Sunday', ?, ?)
ON DUPLICATE KEY UPDATE
opentime = VALUES(opentime),
closetime = VALUES(closetime) ; "; 发布于 2013-09-06 06:38:29
有单独的查询是一种非常普遍的做法,没有任何问题。但是,至少mySQL提供了一个replace into命令:
REPLACE INTO availability
(userid, days, opentime, closetime) VALUES
(?, 'Monday', ?, ?);唯一的缺点是,您不能指定where子句。
https://stackoverflow.com/questions/18651248
复制相似问题