如何使用SQL-INSERT ... ON DUPLICATE KEY UPDATE? [英] How to use SQL - INSERT...ON DUPLICATE KEY UPDATE?

问题描述

我有一个脚本，可捕获推文并将其放入数据库中.我将在cronjob上运行脚本，然后从数据库中在我的网站上显示推文，以防止达到twitter API的限制.

I have a script which captures tweets and puts them into a database. I will be running the script on a cronjob and then displaying the tweets on my site from the database to prevent hitting the limit on the twitter API.

所以我不想在我的数据库中有重复的tweet，我知道我可以使用'INSERT ... ON DUPLICATE KEY UPDATE'来实现，但是我不太了解如何使用它.

So I don't want to have duplicate tweets in my database, I understand I can use 'INSERT...ON DUPLICATE KEY UPDATE' to achieve this, but I don't quite understand how to use it.

我的数据库结构如下.

表格-哈希 ID(自动递增) 鸣叫 用户 user_url

Table - Hash id (auto_increment) tweet user user_url

当前我要插入的SQL如下:

And currently my SQL to insert is as follows:

$tweet = $clean_content[0];
$user_url = $clean_uri[0];
$user = $clean_name[0];

$query='INSERT INTO hash (tweet, user, user_url) VALUES ("'.$tweet.'", "'.$user.'", "'.$user_url.'")';
mysql_query($query);

我如何正确地使用"INSERT ... ON DUPLICATE KEY UPDATE"仅在不存在时进行插入，而在不存在时进行更新?

How would I correctly use 'INSERT...ON DUPLICATE KEY UPDATE' to insert only if it doesn't exist, and update if it does?

谢谢

推荐答案

您需要在表上使用一些UNIQUE KEY，如果user_url是tweer_url，则应该合适(每条tweet都具有唯一的url，id会更好). /p>

you need some UNIQUE KEY on your table, if user_url is tweer_url, then this should fit (every tweet has a unique url, id would be better).

CREATE TABLE `hash` (
  `user_url` ...,
  ...,
  UNIQUE KEY `user_url` (`user_url`)
);

最好在案件中使用INSERT IGNORE

and its better to use INSERT IGNORE on your case

$query='INSERT IGNORE INTO hash (tweet, user, user_url) VALUES ("'.$tweet.'", "'.$user.'", "'.$user_url.'")';

当您需要更新现有行但只想插入一次时，ON DUPLICATE KEY很有用

ON DUPLICATE KEY is useful when you need update existing row but you want to insert just once

这篇关于如何使用SQL-INSERT ... ON DUPLICATE KEY UPDATE?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！