In PHP and MySQL, how to prevent data duplication insert?

Because there is a delay when running.

SQL1 = SELECT * from posts WHERE mid = 2

SQL2 = INSERT into posts ( id , mid , other ) VALUES (",". $c. ", ' Def ');

Because a For loop is used. The first is to detect if there is a Sql1, or sql2 if it does not exist.
Repeat the loop again. However, the current SQL2 does not run the insert for the first time, the second loop executes SQL1, and then executes SQL2 again, resulting in a two-time sql2 run.

A link may be triggered at the same time, resulting in repeated insertions.
I decided not to insert the data in this loop, combined with the statements of several friends.

But it looks like:
INSERT into posts ( id , mid , other ) VALUES (",". $c. ", ' Def '), (', ' C2 ', ' Def2 '), (' ', ' C2 ', ' Def2 '), (', ' C2 ', ' Def2 ') );
This is not possible, because if one of them goes wrong, then the whole bar seems to be unable to insert.

Thank you for the answer, I went to try the next I think of several ways ~ Thank you ~

How do we deal with this?

I hear I have to deal with transactions, but I don't know how to use them. The handbook has been read, but I don't understand ...

[Here are just a few examples]
$c is a variable, the mid field cannot be set to "unique"

$c = '10000';for($a = 0;$a < 100; $a++){    $sql1 =  "SELECT * FROM `posts` WHERE `mid` = ".$c;    $query1 = mysql_query($sql1);    $row = mysql_num_rows($query1);    if($row == 0){        $sql2 = "INSERT INTO `posts` (`id`,`mid`,`other`) VALUES ('',".$c.",'def')";        $query2 = mysql_query($sql2);    }

}

Reply content:

Because there is a delay when running.

SQL1 = SELECT * from posts WHERE mid = 2

SQL2 = INSERT into posts ( id , mid , other ) VALUES (",". $c. ", ' Def ');

Because a For loop is used. The first is to detect if there is a Sql1, or sql2 if it does not exist.
Repeat the loop again. However, the current SQL2 does not run the insert for the first time, the second loop executes SQL1, and then executes SQL2 again, resulting in a two-time sql2 run.

A link may be triggered at the same time, resulting in repeated insertions.
I decided not to insert the data in this loop, combined with the statements of several friends.

But it looks like:
INSERT into posts ( id , mid , other ) VALUES (",". $c. ", ' Def '), (', ' C2 ', ' Def2 '), (' ', ' C2 ', ' Def2 '), (', ' C2 ', ' Def2 ');
This is not possible, because if one of them goes wrong, then the whole bar seems to be unable to insert.

Thank you for the answer, I went to try the next I think of several ways ~ Thank you ~

How do we deal with this?

I hear I have to deal with transactions, but I don't know how to use them. The handbook has been read, but I don't understand ...

[Here are just a few examples]
$c is a variable, the mid field cannot be set to "unique"

$c = '10000';for($a = 0;$a < 100; $a++){    $sql1 =  "SELECT * FROM `posts` WHERE `mid` = ".$c;    $query1 = mysql_query($sql1);    $row = mysql_num_rows($query1);    if($row == 0){        $sql2 = "INSERT INTO `posts` (`id`,`mid`,`other`) VALUES ('',".$c.",'def')";        $query2 = mysql_query($sql2);    }

}

The problem description is not very clear,

However, the current SQL2 does not run the insert for the first time, the second loop executes SQL1, and then executes SQL2 again, resulting in a two-time sql2 run.

I don't know what that means.

First, it is quite inefficient to do SQL queries inside the loop, and it is strongly recommended that you modify the logic to avoid this situation

Two optimization scenarios for the section of the For Loop code you give an example
One is to insert an array of inserts in the loop, presumably with the following code

$d = [];for($i = 0; $i < 100; $i++){    $r = mt_rand(1, 100);    //排除重复值，    if(!in_array($r, $d))    {         $d[] = $r;    }}$pdo = new PDO($dsn, $user, $passwd);$pdo->prepare("insert into table(col1,col2) values(?,?)");foreach($d as $v){    $pdo->execute(array($v, $v));}

As above, the INSERT statement template is reused, and the number of repeatable inserts is also excluded, and if you have special handling for each set of values, you can also put it in a foreach processing
I highly recommend that you use this approach, which is also safe and can prevent SQL injection in a good way, without having to think about too many SQL injection issues.

Second, splicing a long insert statement,

insert into table_name(col1,col2, ......) values(v1,v2,......),(v1,v2,......),......

Both of these may not be appropriate for test instructions because there is a possibility that duplicate data exists and is currently inserted in the library
In MySQL there are the following SQL syntax

INSERT INTO TABLE (a,b,c)VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1;

In the sentence above, insert a statement into the table, update the value of the field C if the inserted data and the unique index in the table or the primary key are duplicated

Combined with this SQL statement, it's a perfect solution to the problem of duplicate insertion values.

In the loop, dare to play like this!

Do not understand the $sql1 meaning of your judgment, set id This is the only key is not good? and the same @ song small North cycle inside dare to play like this!!

See an article today and this question very fit, hereby fill in: Http://blog.sae.sina.com.cn/archives/3491-2014/04/17 12:41

As long as the "posts" table, plus the only unique, or, of course, can be used

INSERT into
Table (Column1,column2,column3 ... columnn)
SELECT value1,value2,value3 ... Valuen
From dual
WHERE not EXISTS (
SELECT *
From table
WHERE value =?
);

Query the available mid before inserting the data

$variable _array = Array (), $c = 1000;for ($a = 0; $a <; $a + +) {$variable _array[] = $c; }if (Count ($variable _array) = = 0) {return false;} Go to heavy $variable_array = Array_unique ($variable _array);//Get Available Mid$sql_head = "Select ' mid ' from ' posts ' WHERE ' mid ' in (#var iable#) ";//Plus single quotation marks, if ok are numbers, then do not add $mid_array = Array_map (function ($value) {return" ' $value ' ";}, $variable _array); $mid _    List = Array (), foreach (Array_chunk ($mid _array, as $piece) {$variable _str = implode (",", $piece);    $sql _piece = Str_replace ("#variable #", $variable _str, $sql _head);    $query _piece = mysql_query ($sql _piece);    Get mid $mid _piece $mid _piece = mysql_fetch_array ($query _piece); $mid _list = Array_merge ($mid _list, Array_diff ($piece, $mid _piece););} Insert Database $insert_str_array = Array_map (function ($value) {return "(' ', ' $value ', ' Def '),"}, $mid _list); $insert _head = " INSERT into ' posts ' (' IDs ', ' mid ', ' other ') VALUES "; foreach (Array_chunk ($insert _str_array, as $piece) {$variable _s TR = ImplodE (",", $piece);    $insert _sql = $insert _head. $variable _str; mysql_query ($insert _sql);}

For a long time did not write PHP code, how to get mysql_query results forgotten!!! Use Mysql_fetch_array directly.

1. Unique index. 2. According to the characteristics of the relevant data, to compare

Give it a try

replace into `table` values set  key=val

