MySQL SQL prevents repeated inserts of the same record instance

MySQL Tutorial SQL prevents repeated inserts of the same record instance

Home Let's take a look at Prevent page repeat refresh Insert Prevent method

Method:
Form page Setcookie ("Pass", "OK");

Working with pages
if ($_cookie["pass"]== "OK") {
mysql_query ("Insert Inot ...");
}
else{
echo "page has expired, please do not repeat refresh";
Exit
}
Setcookie ("Pass", "");

And take a look at the effective way to prevent duplicate data insertion in MySQL operations

If the table contains an AutoIncrement field auto_increment, and the INSERT ... Update inserts a row, the function last_insert_id () returns the Auto_increment value, and if the statement updates a row, last_insert_id () makes no sense. However, you can make it meaningful by using last_insert_id (expr), and if the ID field is an AutoIncrement column, the method that makes last_insert_id () meaningful for the UPDATE statement is as follows:

INSERT into table (a,b,c) VALUES (1,2,3) on DUPLICATE KEY UPDATE id=last_insert_id (id), c=3;

On DUPLICATE KEY Update command statement, the duplicate contents of the database tutorial record are not inserted under the unique index or the primary index, but the old records in the database are updated. For example, a field A is declared as a unique index and contains only records with a value of 1, and the following two statements achieve the same effect:

INSERT into table (a,b,c) VALUES (1,2,3) on DUPLICATE KEY UPDATE c=c+1;

Second, UPDATE table SET c=c+1 WHERE a=1;

You can insert the UPDATE statement ... The VALUES (field name) function is used in UPDATE to associate a row of records. That is, values (field names) can be used in an UPDATE statement to update the value of a field without duplicate keys. This function is especially useful in multiple rows of inserts. But the function VALUES () are only used when the INSERT ... In the UPDATE statement, otherwise NULL is returned. For example:

INSERT into table (a,b,c) VALUES (1,2,3), (4,5,6) on DUPLICATE KEY UPDATE c=values (a) +values (b);

Other methods

Insert into Songinfo (songname,songtime,songpath) Select ' AAA ', ' BB ', ' cc ' to dual where NOT EXISTS (SELECT * from Songinfo where songname= ' 123 ')

The above command cannot be performed on the mysql3.x version and 5.0.24 can be

For example, some of my MySQL database exists URL This table, now have the C API to url,url_hash,domain these three variables into the database, Url_hash is the primary key, in order not to insert duplicates, should be judged, Now as long as this one of the SQL statement on the line, nothing, reduce a lot of inquiries and judgment work, I can only say: it's cool. As follows:

SQL statement:
Insert INTO TABLE (field0,field1 ...) select Value0,value1,... from dual where does exists (SELECT * from table where fieldn= Valuen)

Code:
int Store_url (char *url)
{
Char *key, Url_hash[hashlen], Domain[domainlen], Sqlstr[sqlstrlen];

......

sprintf (sqlstr, insert into URL (url,url_hash,domain) Select '%s ', '%s ', '%s ' to dual where NOT EXISTS (SELECT * from URL Where url_hash= '%s '), URL, url_hash, domain, url_hash);
if (mysql_query (conn, sqlstr))////////////////////////////////////////
{

printf ("Error:%sn", MYSQL_ERROR (conn));
Return
}
}

Finally, there is a relatively simple way is to query the current data in the database is the same, if there are prompts such as
*/
$title = ' www.111cn.net ';
$sql = "SELECT * FROM tablename where title= ' $title '";
$query = mysql_query ($sql);
if (mysql_num_rows ($query))
{
Exit (' cannot repeat the same record ');
}
Else
{
$sql = "Insert tablename VALUES (' $title ')";
mysql_query ($sql);
Exit (' Save record successful ');
}
The bad thing about this is that the database is queried multiple times, and the code is redundant, so that you can use the method of excluding duplicate data to your own situation.