我一直在排除我的代码的故障,搜索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/

10-08 22:28
查看更多