Mysql insert/update usage

Some insert/update operations of mysql may use the following statements when inserting data using conditions in MySQL. Create a simple table for testing:

CREATE TABLE `books` (  `id` INT(11) NOT NULL AUTO_INCREMENT,  `name` VARCHAR(200) NOT NULL,  PRIMARY KEY (`id`),  UNIQUE KEY `NewIndex1` (`name`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

1. insert ignore into: when data is inserted, if an error occurs, such as duplicate data, no error is returned and only returned as a warning. Therefore, use ignore to ensure that the statement is correct, otherwise it will be ignored. For example:

INSERT IGNORE INTO books (name) VALUES ('MySQL Manual')

 

2. on duplicate key update when primary or unique is repeated, the update statement is executed. For example, if it is a useless statement after update, for example, id = id, the function is the same as 1, however, errors are not ignored. For example, to insert duplicate name data without an error, you can use the following statement:

INSERT INTO books (name) VALUES ('MySQL Manual') ON duplicate KEY UPDATE id = id

 

3. insert... Select... Where not exist determines whether to insert data based on the select condition. It can be determined not only by primary and unique, but also by other conditions. For example:

INSERT INTO books (name) SELECT 'MySQL Manual' FROM dual WHERE NOT EXISTS (SELECT id FROM books WHERE id = 1)

 

4. replace into: If a record with the same primary or unique exists, delete it first. Insert a new record.

REPLACE INTO books SELECT 1, 'MySQL Manual' FROM books