Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to use the REPLACE sentence in MySQL 5.7. It is very detailed and has a certain reference value. Friends who are interested must finish it!

REPLACE is similar to INSERT in that it depends on the primary key or unique index. If the primary key or unique key of the inserted row already exists in the table, the old record will be updated; if the primary or unique key of the inserted row does not exist in the table, the record will be inserted. REPLACE is MySQL's extension of the SQL standard.

Example ①, because the second record has the same primary key as the first record, the first record is updated

CREATE TABLE test (id INT UNSIGNED NOT NULL AUTO_INCREMENT, data VARCHAR (64) DEFAULT NULL, ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id)); mysql > REPLACE INTO test VALUES (1, 'Old',' 2014-08-20 18 row affected 47 sec); Query OK, 1 row affected (0.04 sec) mysql > REPLACE INTO test VALUES (1, 'New',' 2014-08-20 184742 sec'); Query OK, 2 rows affected (0.04 sec) mysql > SELECT * FROM test +-+ | id | data | ts | +-+ | 1 | New | 2014-08-20 18:47:42 | +-+-- -+ 1 row in set (0.00 sec)

Example ②, where the primary key of the second record is different from that of the first record, the second record is inserted into the table and the first record is retained

CREATE TABLE test2 (id INT UNSIGNED NOT NULL AUTO_INCREMENT, data VARCHAR (64) DEFAULT NULL, ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id, ts)); mysql > REPLACE INTO test2 VALUES (1, 'Old',' 2014-08-20 1847 sec); Query OK, 1 row affected (0.05 sec) mysql > REPLACE INTO test2 VALUES (1, 'New',' 2014-08-20 1847) Query OK, 1 row affected (0.06 sec) mysql > SELECT * FROM test2 +-+ | id | data | ts | +-+ | 1 | Old | 2014-08-20 18:47:00 | | 1 | New | 2014-08-20 18:47:42 | +-+-- -+-+ 2 rows in set (0.00 sec) above are all the contents of the article "how to use REPLACE statements in MySQL 5.7" Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

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.