MySQL Insert is implemented if the primary key in the datasheet is duplicated, then four methods are inserted without duplication

• "Csdn Download" PowerDesigner Design primary key code cannot be duplicated and so on
• "CSDN Blog" The consequences of using a sequence in Oracle to create a primary key auto-increment function for two tables
• "CSDN blog" MySQL self-increment primary key after deletion repeat problem
• "CSDN blog" MySQL master-slave replication principle to prevent primary key duplication problems (Must SEE)
• "CSDN Blog" replace into causes MySQL auto-increment to cause primary key repetition problem analysis
• "Csdn Blog" a loop updates the value method for all non-primary key columns for all tables in a library (SQL 2005 & MySQL)
• "CSDN Blog" MySQL insert implementation if the primary key in the data table is repeated update, there is no repetition of the four methods inserted
• "CSDN Blog" tests a table with two foreign keys as a composite primary key, and two foreign key null values
• "CSDN Blog" MySQL ignores primary key conflicts and avoids repeated insertions in several ways
• "CSDN Blog" MySQL ignores primary key conflicts and avoids repeated insertions in several ways
• "CSDN Blog" SQL Server multiple rows of all data are duplicated, unable to update or delete the problem, usually a table without a primary key
• "CSDN blog" MySQL self-increment primary key after deletion insert new record primary key repeat problem

个人实例：

INSERT into W_dashboard (dashboard_name, content, Createtime)
VALUES ("AA", "CC", "2018-01-11 16:58:03")
On DUPLICATE KEY UPDATE dashboard_name=values (dashboard_name);

Or

INSERT IGNORE into W_dashboard (dashboard_name, content, Createtime)
VALUES ("AA", "CC", "2018-01-11 16:58:03");

1、replace语句：替换已有的行    replace语句是insert语句的一个变种    当添加新行时        1）如果主键值重复，那么覆盖表中已有的行        2）如果没有主键值重复，则插入该行2、ignore    insert语句可以使用ignore选项来当insert语句出现错误时，不显示错误信息，但是insert语句不执行。    insert ignore into 。。。。。    3、可以采用异常抓捕的方式来实现handler，相当于sqlserver中的try catch4、如果在INSERT语句末尾指定了ON DUPLICATE KEY UPDATE，并且插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值，则在出现重复值的行执行UPDATE；如果不会导致唯一值列重复的问题，则插入新行。

The four methods are divided into two steps:
The Action table test table structure is as follows

1、replace语法    select * from test;

Now insert (1, ' xiaohong ') data, found error, error prompt (primary key repeat input ' 1 '), prove that when insert inserted the same primary key will be error.

Now we have to replace the statement test will not error, execution success

Check our table information and find that the previous record (1, ' Xiaozhang ') has been replaced by (1, ' Xiaohong '),
Proof that we inserted the same primary key information, replace replaces the original information.

Replace syntax execution and the data that the primary key repeats in the database replaces the old information, so what happens when the different information is executed? (inserts are performed when the execution and data in the database are not duplicated by the primary key)

2, ignore
Here we apply insert ignore to perform the insertion (1, ' Xiaoplan '), run successfully, but the data is not inserted into the test table.


Insert ignore and update combined (discovery of ' xiaohong ' with primary key ' 1 ' being updated to ' Xiaolan ')

Run the result diagram
3, can use the way of abnormal capture to achieve handler, equivalent to the try catch in SQL Server
Handler is a custom exception handling for MySQL.

        定义异常处理语法        DECLARE handler_type HANDLER FOR condition_value sp_statement        语法解析        handler_type：为异常的处理方式，也就是当发生异常时怎么处理，有三个参数            1）exit ，表示遇到异常马上退出            2）continue ，表示遇到异常不处理，继续执行sql代码            3）undo ，mysql不支持，是一个回滚操作        condition_value:表示错误类型            1)SQLSTATE [VALUE] sqlstate_value  为包含5个字符的字符串错误值            2）SQLWARNING  匹配所有以01开头的SQLSTATE错误代码            3）NOT FOUND  匹配所有以02开头的SQLSTATE错误代码            4）SQLEXCEPTION  匹配所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE错误代码            5）mysql_error_code  匹配数值类型错误代码        condition_name：标志定义错误的名称

Exception definition reference: Https://www.2cto.com/database/201410/341132.html want to get a closer look at

This way, we're going to implement an insert if the primary key is duplicated, the primary key does not repeat, so we need to understand what the primary key duplicates, then catch the exception, define the exception handling type as continue, and then perform our update operation
First find the exception number of the primary key repeat error, labeled ' 1062 '

Directly on the code

    drop procedure if exists test;    create procedure test()         #创建存储过程    BEGIN    DECLARE done INT default 0 ;  #定义变量        #定义主键重复异常发生时将done赋值为1；同时异常的处理方式是continue，异常发生继续执行         DECLARE CONTINUE HANDLER for 1062 SET done=1;          #执行插入操作，插入过程中可能发生主键重复，如果主键重复那么done被赋值为1        insert into test(id,name) values(1,‘xiaowang‘);         #如果done的值为1的话，实现更新原有数据        if done = 1 then             update test set name=‘xiaowang‘ where id=1;        end if;    END

After executing the stored procedure results (you can try to build a table operation), the functional implementation

4. If the on duplicate key update is specified at the end of the INSERT statement

insert into test(id,name) values(1,‘如来‘) on duplicate key update name=values(name);      #on duplicate key update 后面加入如果主键重复更新的列和更新的值，这里面更新test表的name列，更新的值是values(‘如来‘)，但是要写成values(name).

Run to achieve the effect of updating existing data

Insert different data, if the primary key is different, insert

See if the primary key can update multiple columns in the same situation (can be performed)

MySQL Insert is implemented if the primary key in the datasheet is duplicated, then four methods are inserted without duplication