MySQL production database inserts the same record twice, but the primary key ID is different.

In the Email, I received help from my friend laopan.

Laopan:
Insert into HudsonResult (JobID, EnvironmentID, FirstSessionID, RerunSessionID, State, Desp, OtherInfo) values
(Select ID from Hudson where Stream = 'a7510 _ R52_Integration 'and State = 'N' and pakName = 'needcompile' and User = 'jinhaiz '), 0, 'n ', 'N', 'n', 'smoke _ test ','')
If the same record does not exist, execute the preceding insert command to prevent the operation from being performed twice.
How to write this statement?

Can you understand?
The problem is that the same record is inserted twice, but the primary key ID is different.

Me:
For mysql databases:

1. If your table has a unique health check, you can use this unique health check to select the game.
2. If there is no unique health condition, you need to have a condition to judge whether the data is duplicated. select the condition first to see if it exists. If it does not exist, insert, if overwrite or ignore the execution, multiple SQL statements are required in this process. You are advised to enable the transaction for control.

In Mysql, there is a replace and Insert into... on duplicate key update to determine whether to insert or update duplicate data, but this is determined based on the primary key or the unique health. You can refer to the following article (http://www.jb51.net/article/47090.htm ).


Laopan:
1. How to filter unique keys? The problem is that the key is different, and other columns are the same.

Me:
Send the table structure to me. Let's see, run the show create table HudsonResult command to get the table structure.
If there is no unique health condition, the uniqueness of data cannot be determined from the SQL level, otherwise the resource consumption is too large. Data filtering needs to be verified from the data source perspective of the application.

Laopan:

Mysql> show create table HudsonResult;
+ -------------- + Too many others ----------------------------------------------------------------------------------- +
| Table | Create Table
+ -------------- + Too many others ----------------------------------------------------------------------------------- +
| HudsonResult | create table 'hudsonresult '(
'Jobid' int (32) not null,
'Environmentid' int (32) not null,
'Firstsessionid' varchar (100) default NULL,
'Rerunsessionid' varchar (100) default NULL,
'State' varchar (5) default NULL,
'Id' int (32) not null auto_increment,
'Desc' varchar (100) default NULL,
'Execnum' int (32) default NULL,
'Failnum' int (32) default NULL,
'Attrstarttime' datetime default NULL,
'Cendtime' datetime default NULL,
'Focnumbefore' int (32) default '0 ',
'Focnumafter' int (32) default '0 ',
'Priority 'int (32) default '0 ',
'Focdesp 'varchar (5000) default '',
'Otherinfo' varchar (100) default NULL,
Primary key ('id ')
) ENGINE = MyISAM AUTO_INCREMENT = 14910 default charset = latin1 |
+ -------------- + Too many others ----------------------------------------------------------------------------------- +
1 row in set (0.00 sec)

The background of the problem is as follows: after the insert operation is performed on the webpage, the computer gets stuck. At this time, no response is returned, and the insert operation is performed again, resulting in two records.
The content is the same, but the primary key is different. You have done the same in java. Is there any idea to avoid this?

ME:
It is very easy to process the submit button. After the button is submitted once, it is set to gray. The operator cannot click the button, and only waits for the result to be submitted to continue the next step.

Laopan:

Well, it's easy to use. I think more about it. Thank you.

Summary: it can be seen that the error data is often caused by application bugs. When cleaning up the data, you must handle the problem from the root cause. The data-level SQL statements should not be moved as much as possible.