我一直在排除我的代码的故障,搜索stackoverflow以找到正确的语法来使用PHP。我无法理解ON DUPLICATE KEY UPDATE语法如何与准备好的语句一起工作:
INSERT INTO placements_by_date (DateVal, PlacementName, PlacementId,
CampaignId, AdName, Format, TagId, Impressions, Clicks, Leads, MediaCost)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
ON DUPLICATE KEY UPDATE
// HERE COMES THE TRICKY SYNTAX:
DateVal = VALUES(DateVal), PlacementName = VALUES(PlacementName),
PlacementId = VALUES(PlacementId), CampaignId = VALUES(CampaignId),
AdName = VALUES(AdName), Format = VALUES(Format), TagId = VALUES(TagId),
Impressions = ?, Clicks = ?, Leads = ?, MediaCost = ?
我尝试过很多变化:
...ON DUPLICATE KEY UPDATE
DateVal = VALUES('$DateVal'), PlacementName = VALUES('$PlacementName'),
PlacementId = VALUES('$PlacementId'), CampaignId = VALUES('$CampaignId'),
AdName = VALUES('$AdName'), Format = VALUES('$Format'), TagId = VALUES('$TagId'),
Impressions = VALUES(?), Clicks = VALUES(?), Leads = VALUES(?),
MediaCost = VALUES(?)";
我将其存储为字符串,
$sql
,然后执行常规操作:$mysqli = new mysqli(...
...
$stmt = $mysqli -> prepare($sql);
$stmt -> bind_param('sissiiiis', $PlacementName, $PlacementId,
$AdName, $Format, $TagId, $Impressions,
$Clicks, $Leads, $MediaCost);
...
但也许参数的界限应该有所不同??
我试着阅读反馈,但这太笼统了,没有可操作的见解。
$driver = new mysqli_driver();
$driver->report_mode = MYSQLI_REPORT_ALL;
希望你能分享一些见解?
谢谢!
最佳答案
为什么不继续使用VALUES()
方法?
INSERT INTO placements_by_date(DateVal, PlacementName, PlacementId,
CampaignId, AdName, Format, TagId,
Impressions, Clicks, Leads, MediaCost
)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
ON DUPLICATE KEY UPDATE
DateVal = VALUES(DateVal), PlacementName = VALUES(PlacementName),
PlacementId = VALUES(PlacementId), CampaignId = VALUES(CampaignId),
AdName = VALUES(AdName), Format = VALUES(Format), TagId = VALUES(TagId),
Impressions = VALUES(Impressions), Clicks = VALUES(Clicks), Leads = VALUES(Leads),
MediaCost = VALUES(MediaCost);
您没有指定
unique
键是什么。但是,您不需要在update
语句中包含这些列,因为它们已经相同了。如果您希望增加最后四个值而不是仅仅分配它们,那么您也可以这样做:
ON DUPLICATE KEY UPDATE
DateVal = VALUES(DateVal), PlacementName = VALUES(PlacementName),
PlacementId = VALUES(PlacementId), CampaignId = VALUES(CampaignId),
AdName = VALUES(AdName), Format = VALUES(Format), TagId = VALUES(TagId),
Impressions = Impressions + VALUES(Impressions),
Clicks = Clicks + VALUES(Clicks),
Leads = Leads + VALUES(Leads),
MediaCost = MediaCost + VALUES(MediaCost)
编辑:
以下内容仅用于更新最后四列:
INSERT INTO placements_by_date(DateVal, PlacementName, PlacementId,
CampaignId, AdName, Format, TagId,
Impressions, Clicks, Leads, MediaCost
)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
ON DUPLICATE KEY UPDATE
Impressions = VALUES(Impressions), Clicks = VALUES(Clicks), Leads = VALUES(Leads),
MediaCost = VALUES(MediaCost);
关于php - 准备将MySQL INSERT INTO ON DUPLICATE KEY UPDATE的正确语法?,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/23973548/