Shulou(Shulou.com)06/01 Report--

Execute INSERT... ON DUPLICATE KEY UPDATE statement, if the value inserted by the INSERT statement duplicates an existing UNIQUE index or primary key, MySQL updates the existing row. Test tables without primary keys and UNIQUE indexes

Mysql > select * from dept2

+-+

| | deptno | dname | report_date | |

+-+

| | 10 | Research | 2016-06-03 |

| | 20 | Maintenance | 2016-06-03 |

| | 30 | Leader | 2016-06-03 |

| | 40 | Market | 2015-08-02 | |

+-+

4 rows in set (0.00 sec)

Mysql > desc dept2

+-+ +

| | Field | Type | Null | Key | Default | Extra | |

+-+ +

| | deptno | int (5) | NO | MUL | NULL |

| | dname | varchar (14) | YES | | NULL |

| | report_date | date | YES | MUL | NULL |

+-+ +

3 rows in set (0.00 sec)

Mysql > INSERT INTO dept2 (deptno,dname,report_date) VALUES

-> ON DUPLICATE KEY UPDATE report_date='2010-10-30'

Query OK, 1 row affected (0.01sec)

Mysql > select * from dept2

+-+

| | deptno | dname | report_date | |

+-+

| | 10 | Research | 2016-06-03 |

| | 20 | Maintenance | 2016-06-03 |

| | 30 | Leader | 2016-06-03 |

| | 40 | Market | 2015-08-02 | |

| | 20 | Development | 2010-10-30 |

+-+

5 rows in set (0.00 sec)

Mysql > delete from dept2 where deptno=20 and report_date=date'2010-10-30'

Query OK, 1 row affected (0.01sec)

Mysql > select * from dept2

+-+

| | deptno | dname | report_date | |

+-+

| | 10 | Research | 2016-06-03 |

| | 20 | Maintenance | 2016-06-03 |

| | 30 | Leader | 2016-06-03 |

| | 40 | Market | 2015-08-02 | |

+-+

4 rows in set (0.00 sec)

Add the primary key and then test

Mysql > alter table dept2 add primary key (deptno)

Query OK, 0 rows affected (0.28 sec)

Records: 0 Duplicates: 0 Warnings: 0

Mysql > INSERT INTO dept2 (deptno,dname,report_date) VALUES

-> ON DUPLICATE KEY UPDATE report_date='2010-10-30'

Query OK, 2 rows affected (0.14 sec)

Mysql > select * from dept2

+-+

| | deptno | dname | report_date | |

+-+

| | 10 | Research | 2016-06-03 |

| | 20 | Maintenance | 2010-10-30 |

| | 30 | Leader | 2016-06-03 |

| | 40 | Market | 2015-08-02 | |

+-+

4 rows in set (0.00 sec)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.