MySQL insert ignore and replace into

MySQL Insert data , If the inserted data already exists in the table （ Primary key or unique key already exists ）, Use insert ignore Syntax can ignore inserting duplicate data .

CREATE TABLE `users` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `email` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `info` json DEFAULT NULL,
  `email_verified_at` timestamp NULL DEFAULT NULL,
  `password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `remember_token` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `phone` varchar(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci GENERATED ALWAYS AS (json_unquote(json_extract(`info`,_utf8mb4'$.phone_number'))) VIRTUAL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `users_email_unique` (`email`),
  UNIQUE KEY `idx_phone` (`phone`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

insert data

INSERT INTO users ( NAME, email, `password` ) VALUES ( 'gan', '[email protected]', 1 ),( 'gan', '[email protected]', 1 );

because email=‘[email protected]’ Already exists , So wrong reporting

INSERT INTO users ( NAME, email, password ) VALUES ( ‘gan’, ‘[email protected]’, 1 ),( ‘gan’, ‘[email protected]’, 1 )
1062 - Duplicate entry ‘[email protected]’ for key ‘users_email_unique’
Time : 0.014s

INSERT INTO： Skip repeating lines , Insert remaining

increase IGNORE, Successful implementation （ Skip existing data directly ）

INSERT IGNORE INTO users ( NAME, email, `password` ) VALUES ( 'gan', '[email protected]', 1 ),( 'gan', '[email protected]', 1 );

If the business logic needs to insert duplicate data, it will be automatically ignored , Might as well try. MySQL Of insert ignore function .

REPLACEINTO： Delete duplicate lines , reinsert

REPLACE INTO users ( NAME, email, `password` ) VALUES ( 'gan1', '[email protected]', 1 ),( 'gan2', '[email protected]', 2 );

Why is the number of affected rows 4 That's ok . When inserting the first row, it is found that it already exists , So delete the existing data , Insert again ; When inserting the first row, it is found that it already exists , So delete the existing data , Insert again . Delete two lines , Insert two lines .