在SQL中检查行的简单方法，如果不存在则添加？ （超过100k行）

Question

I have a script that adds about 100,000 entries to SQL if it doesn't exist. But it normally takes about 30 hours to fully check each row and add if it doesnt exist. Is there an easier way to do this?

my code currently uses a for Loop, within the loop is this.

$query = mysql_query("SELECT EXISTS (SELECT * FROM linkdb WHERE link='$currentlink')");

if (mysql_result($query, 0) == 1){

}else{
  $qry = "INSERT INTO linkdb(link,title) VALUES('$link','$title')";
  $result = @mysql_query($qry);

}

the code above takes very long time because it has to normally go through thousands of entries. If I don't check the table first using SELECT EXIST and use only INSERT INTO, 90,000 entries are added within 1 min. But that adds duplicate entries of the same row.

Please give me some advice on what I could do. These rows need to be updated almost everyday.

Answer 1

You're looking for ON DUPLICATE KEY UPDATE. Add an index on link and then:

INSERT INTO linkdb(link,title) VALUES('$link','$title') ON DUPLICATE KEY UPDATE link=link;

With that said, you should not be using ext/mysql since it is deprecated. Instead look into PDO or mysqli. It would be much better to use parametrized queries for this to prevent SQL injection.