MySQL插入语句insert into,insert ignore into,insert into ... on duplicate key update,replace into-解决唯一键约束

灰太狼 2020-06-18 08:03 799阅读 0赞

当MySQL表字段设置unique key或者primary key时，被约束的字段就必须是唯一的。新插入数据直接使用insert into，如果出现唯一性冲突，就会抛出异常。我们可以根据需求选择合适的插入语句。

为了演示，我们先新建一张user表，SQL语句如下，其中user\_id为主键，username为唯一索引

SET NAMES utf8mb4;
    SET FOREIGN_KEY_CHECKS = 0;
    
    -- ----------------------------
    -- Table structure for user
    -- ----------------------------
    DROP TABLE IF EXISTS `user`;
    CREATE TABLE `user`  (
      `user_id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '用户id',
      `username` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '用户名',
      `password` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '密码',
      `mobile_phone_number` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '手机号码',
      `email` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '邮箱',
      `delete_state` tinyint(1) UNSIGNED DEFAULT 0 COMMENT '用户状态，1表示删除，0表示未删除',
      `create_time` datetime(0) DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
      `update_time` datetime(0) DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '更新时间',
      PRIMARY KEY (`user_id`) USING BTREE,
      UNIQUE INDEX `uk_username`(`username`) USING BTREE COMMENT '用户名唯一'
    ) ENGINE = InnoDB AUTO_INCREMENT = 9 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
    
    -- ----------------------------
    -- Records of user
    -- ----------------------------
    INSERT INTO `user` VALUES (1, 'JourWon', '123456', '13800000000', 'JourWon@163.com', 0, CURRENT_TIMESTAMP, NULL);
    INSERT INTO `user` VALUES (2, '马云', '123456', '13800000011', 'JackMa@163.com', 0, CURRENT_TIMESTAMP, NULL);
    INSERT INTO `user` VALUES (3, '马化腾', '123456', '13800000022', 'PonyMa@163.com', 0, CURRENT_TIMESTAMP, NULL);
    INSERT INTO `user` VALUES (4, '李彦宏', '123456', '13800000033', 'RobinLee@163.com', 0, CURRENT_TIMESTAMP, NULL);
    INSERT INTO `user` VALUES (5, '任正非', '123456', '13800000044', 'RenZhengfei@163.com', 0, CURRENT_TIMESTAMP, NULL);
    INSERT INTO `user` VALUES (6, 'Jobs', '123456', '13800000055', 'Jobs@163.com', 0, CURRENT_TIMESTAMP, NULL);
    INSERT INTO `user` VALUES (7, 'Bill Gates', '123456', '13800000066', 'Bill Gates@163.com', 0, CURRENT_TIMESTAMP, NULL);
    INSERT INTO `user` VALUES (8, 'Buffett', '123456', '13800000077', 'Buffett@163.com', 0, CURRENT_TIMESTAMP, NULL);
    
    SET FOREIGN_KEY_CHECKS = 1;

## 常用插入语句 ##

### insert into ###

当插入数据时，如果唯一性校验出现重复问题，则报错；

如果没有重复性问题，则执行插入操作。

简单总结：重复则报错，不重复则插入。

示例

INSERT INTO `user`
    VALUES
    	( 8, 'Buffett', '123456', '13800000077', 'Buffett@163.com', 0, CURRENT_TIMESTAMP, NULL );

执行结果

INSERT INTO `user`
    VALUES
    	( 8, 'Buffett', '123456', '13800000077', 'Buffett@163.com', 0, CURRENT_TIMESTAMP, NULL )
    > 1062 - Duplicate entry '8' for key 'user.PRIMARY'
    > 时间: 0.001s

### insert ignore into ###

当插入数据时，如果唯一性校验出现重复问题，则忽略错误，只以警告形式返回，不执行此SQL语句；

如果没有重复性问题，则执行插入操作。

简单总结：重复则忽略，不重复则插入。

示例

INSERT IGNORE INTO `user`
    VALUES
    	( 8, 'Buffett', '123456', '13800000077', 'Buffett@163.com', 0, CURRENT_TIMESTAMP, NULL );

执行结果

INSERT IGNORE INTO `user`
    VALUES
    	( 8, 'Buffett', '123456', '13800000077', 'Buffett@163.com', 0, CURRENT_TIMESTAMP, NULL )
    > Affected rows: 0
    > 时间: 0.001s

### insert into … on duplicate key update ###

当插入数据时，如果唯一性校验出现重复问题，则在原有记录基础上，更新指定字段内容，其它字段内容保留；

如果没有重复性问题，则执行插入操作。

简单总结：重复则更新指定字段，不重复则插入

示例

INSERT INTO `user` ( username, PASSWORD, mobile_phone_number, email )
    VALUES
    	( 'Buffett', '123456', '13800000088', 'Buffett@163.com' ) 
    	ON DUPLICATE KEY UPDATE mobile_phone_number = '13800000088';

执行结果

表记录，mobile\_phone\_number更新了，update\_time也有值了，但是user\_id没有变

| user_id | username | password | mobile_phone_number | email           | delete_state | create_time         | update_time         |
| ------- | -------- | -------- | ------------------- | --------------- | ------------ | ------------------- | ------------------- |
| 8       | Buffett  | 123456   | 13800000088         | Buffett@163.com | 0            | 2020-05-28 09:49:53 | 2020-05-28 10:04:30 |

### replace into ###

replace into表示插入替换数据，当插入数据时，如果唯一性校验出现重复问题，删除旧记录，插入新记录；

如果没有重复性问题，则执行插入操作，效果和insert into是一样的。

简单总结：重复则先删除再插入新记录，不重复则插入

示例

REPLACE INTO `user` ( username, PASSWORD, mobile_phone_number, email )
    VALUES
    	( 'Buffett', '123456', '13800000099', 'Buffett@163.com' );

执行结果

REPLACE INTO `user` ( username, PASSWORD, mobile_phone_number, email )
    VALUES
    	( 'Buffett', '123456', '13800000099', 'Buffett@163.com' )
    > Affected rows: 2
    > 时间: 0.019s

表记录，user\_id和mobile\_phone\_number变了，update\_time变为了空

| user_id | username | password | mobile_phone_number | email           | delete_state | create_time         | update_time |
| ------- | -------- | -------- | ------------------- | --------------- | ------------ | ------------------- | ----------- |
| 10      | Buffett  | 123456   | 13800000099         | Buffett@163.com | 0            | 2020-05-28 09:49:53 |             |

## 使用场景总结 ##

*  如果出现重复异常，希望捕获异常，则使用insert into
 *  如果出现重复异常，希望保存旧纪录，忽略新纪录，则使用insert ignore into
 *  如果出现重复异常，希望更新指定字段，则使用insert into … on duplicate key update
 *  如果出现重复异常，希望删除旧记录，插入新记录，则使用replace into