ON DUPLICATE KEY UPDATE实现/设计 [英] ON DUPLICATE KEY UPDATE implementation/design

问题描述

我有一张桌子，sessionBasket，其中包含我网站的访问者购物篮中的项目列表。它看起来像：

  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY 
 usersessid VARCHAR 
 date_added DATETIME 
 product_id INT 
 qty INT 
  

我添加到购物车脚本首先检查是否存在该项与此表中的当前product_id与usersessid关联，如果找到一个，则更新数量。如果没有，单独的查询将插入一个新行与相关的信息。

我发现有一个条件ON DUPLICATE KEY UPDATE，但我不知道我需要改变什么才能使其正常工作。我需要两个键 - product_id和usersessid，如果有一行与这两个键匹配我试图插入的那个，则更新条件被做出。我相信有比我已经做的更好的办法。此外，我检查product_id是否有效，以防它被欺骗，所以我总是做两个查询只是为了检查东西，然后另一个来做更新/插入。

以下是单独的查询：

  //选择查询来验证项目ID 
 $ check_sql =SELECT * FROM aromaProducts1 WHERE id ='。intval（$ _ GET [productid]）。'; 
 $ check_res = mysqli_query（$ mysqli，$ check_sql）或者error_log（mysqli_error（$ mysqli））\r\\\
）; 
 
 //选择查询以检查篮子中的项目ID 
 $ duplicate_sql =SELECT qty FROM sessionBasket WHERE product_id ='。intval（$ _ GET [productid]）。 'AND usersessid ='。session_id（）。'; 
 $ duplicate_res = mysqli_query（$ mysqli，$ duplicate_sql）或者error_log（mysqli_error（$ mysqli））\r\\\
）; 
 
 //购物篮中的项目 - 添加另一个
 $ add_sql =UPDATE sessionBasket SET qty = qty +。intval（$ _ GET [qty]）。WHERE usersessid =' session_id（）。'AND product_id ='。intval（$ _ GET [productid]）。'; 
 $ add_res = mysqli_query（$ mysqli，$ add_sql）或者error_log（mysqli_error（$ mysqli））\r\\\
）; 
 
 // insert query 
 $ insert_sql =INSERT INTO。$ table。（userid，usersessid，date_added，product_id，qty，notes）VALUES（
'。 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ .intval（$ _ GET [qty]）。'，
'.htmlspecialchars（$ notes）。'）; 
 $ insert_res = mysqli_query（$ mysqli，$ insert_sql）或error_log（mysqli_error（$ mysqli））\r\\\
）; 
  

请不要回复有关SQL注入 - 我的清理比这些片段更加彻底！ p>

任何帮助缩小这些下降将是太棒了。这可能是我的表格不够标准化。我认为可以创建一个新的唯一字段，其中包含usersessid和product_id连接，将唯一索引折叠成一个而不是两个字段，但不太理想。

解决方案

首先，您需要（usersessid，product_id）上的唯一索引。我不知道您是否实际使用自动生成的列 id ，但如果不是，您应该将主键更改为（usersessid，product_id ）。然后，而不是运行一个单独的 UPDATE 查询，只运行一个 INSERT 查询：

  INSERT INTO sessionBasket（userid，usersessid，date_added，product_id，qty，notes）
 VALUES（？，？，now（），？ ？）
 ON DUPLICATE KEY UPDATE qty = qty +？ 
  

只是为了弄清楚唯一索引应如下所示：

  CREATE UNIQUE INDEX sessionBasket_uniq ON sessionBasket（usersessid，product_id）; 
  

或主键：

  ALTER TABLE sessionBasket ADD CONSTRAINT sessionBasket_pkey PRIMARY KEY（usersessid，product_id）; 
  

I have a table, sessionBasket, that holds a list of the items in the shopping baskets of visitors to my site. It looks like:

id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
usersessid VARCHAR
date_added DATETIME
product_id INT
qty INT

My add to basket script first checks for the presence of an item with the current product_id in this table associated with the usersessid and if it finds one, it updates the qty. If not, a separate query inserts a new row with the relevant information.

I have since discovered there is a condition ON DUPLICATE KEY UPDATE but I'm not sure what I need to change to cause this to function correctly. I would need two keys here - product_id and usersessid, and if there is a row with both of these matching the ones I'm trying to insert, the update condition is made. I am sure there is a better way to do it than I already do. In addition, I check the product_id is valid in case it is spoofed somehow, so overall I make two queries just to check stuff, and then another to do the update/insert.

Here are the separate queries:

//do select query to verify item id
$check_sql = "SELECT * FROM aromaProducts1 WHERE id='".intval($_GET["productid"])."'";
$check_res = mysqli_query($mysqli, $check_sql) or  error_log(mysqli_error($mysqli)."\r\n");

  //do select query to check for item id already in basket
  $duplicate_sql = "SELECT qty FROM sessionBasket WHERE product_id='".intval($_GET["productid"])."' AND usersessid='".session_id()."'";
  $duplicate_res = mysqli_query($mysqli, $duplicate_sql) or  error_log(mysqli_error($mysqli)."\r\n");

    //item in basket - add another
    $add_sql = "UPDATE sessionBasket SET qty=qty+".intval($_GET["qty"])."  WHERE usersessid='".session_id()."'AND product_id='".intval($_GET["productid"])."'";
    $add_res = mysqli_query($mysqli, $add_sql) or  error_log(mysqli_error($mysqli)."\r\n");

  //insert query
  $insert_sql = "INSERT INTO ".$table." (userid, usersessid, date_added, product_id, qty, notes) VALUES (
  '".$userid."',
  '".session_id()."',
  now(),
  '".htmlspecialchars($productid)."',
  '".intval($_GET["qty"])."',
  '".htmlspecialchars($notes)."')";
  $insert_res = mysqli_query($mysqli, $insert_sql) or  error_log(mysqli_error($mysqli)."\r\n");

Please no replies about SQL injection - my sanitizing is much more thorough than these snippets let on!

Any help shrinking these down would be fantastic. It's possible my tables aren't normalized enough. I was thinking it would be possible to create a new unique field which contains the usersessid and product_id concatenated which would collapse the unique indices into one instead of two fields, but it's not ideal.

解决方案

First you need an unique index on (usersessid, product_id). I'm not sure if you are actually using the automatically generated column id, but if not, you should change the primary key to (usersessid, product_id). Then instead of running a separate UPDATE query, only run a single INSERT query:

INSERT INTO sessionBasket (userid, usersessid, date_added, product_id, qty, notes)
VALUES (?, ?, now(), ?, ?, ?)
ON DUPLICATE KEY UPDATE qty = qty + ?

Just to make it clear how the unique index should look like:

CREATE UNIQUE INDEX sessionBasket_uniq ON sessionBasket (usersessid, product_id);

Or primary key:

ALTER TABLE sessionBasket ADD CONSTRAINT sessionBasket_pkey PRIMARY KEY (usersessid, product_id);

这篇关于ON DUPLICATE KEY UPDATE实现/设计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！