MySQL插⼊语句insertinto,insertignoreinto,insertin。。。⽂章⽬录
当MySQL表字段设置unique key或者primary key时，被约束的字段就必须是唯⼀的。新插⼊数据直接使⽤insert into，如果出现唯⼀性冲突，就会抛出异常。我们可以根据需求选择合适的插⼊语句。
为了演⽰，我们先新建⼀张user表，SQL语句如下，其中user_id为主键，username为唯⼀索引
1. SET NAMES utf8mb4;
2. SET FOREIGN_KEY_CHECKS = 0;
3.
4. -- ----------------------------
5. -- Table structure for user
6. -- ----------------------------
7. DROP TABLE IF EXISTS `user`;
8. CREATE TABLE `user` (
9. `user_id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '⽤户id',
0. `username` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '⽤户名',
1. `password` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '密码',
2. `mobile_phone_number` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '⼿机号码',
3. `email` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '邮箱',
4. `delete_state` tinyint(1) UNSIGNED DEFAULT 0 COMMENT '⽤户状态，1表⽰删除，0表⽰未删除',
5. `create_time` datetime(0) DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
6. `update_time` datetime(0) DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '更新时间',
7. PRIMARY KEY (`user_id`) USING BTREE,
8. UNIQUE INDEX `uk_username`(`username`) USING BTREE COMMENT '⽤户名唯⼀'
9. ) ENGINE = InnoDB AUTO_INCREMENT = 9 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
20.
1. -- ----------------------------
2. -- Records of user
3. -- ----------------------------
4. INSERT INTO `user` VALUES (1, 'JourWon', '123456', '138********', 'JourWon@163', 0, CURRENT_TIMESTAMP, NULL);
5. INSERT INTO `user` VALUES (2, '马云', '123456', '138********', 'JackMa@163', 0, CURRENT_TIMESTAMP, NULL);
6. INSERT INTO `user` VALUES (3, '马化腾', '123456', '138********', 'PonyMa@163', 0, CURRENT_TIMESTAMP, NULL);
7. INSERT INTO `user` VALUES (4, '李彦宏', '123456', '138********', 'RobinLee@163', 0, CURRENT_TIMESTAMP, NULL);
8. INSERT INTO `user` VALUES (5, '任正⾮', '123456', '138********', 'RenZhengfei@163', 0, CURRENT_TIMESTAMP, NULL);
9. INSERT INTO `user` VALUES (6, 'Jobs', '123456', '138********', 'Jobs@163', 0, CURRENT_TIMESTAMP, NULL);
0. INSERT INTO `user` VALUES (7, 'Bill Gates', '123456', '138********', 'Bill Gates@163', 0, CURRENT_TIMESTAMP, NULL);
1. INSERT INTO `user` VALUES (8, 'Buffett', '123456', '138********', 'Buffett@163', 0, CURRENT_TIMESTAMP, NULL);
32.
3. SET FOREIGN_KEY_CHECKS = 1;
常⽤插⼊语句
insert into
当插⼊数据时，如果唯⼀性校验出现重复问题，则报错；
如果没有重复性问题，则执⾏插⼊操作。
简单总结：重复则报错，不重复则插⼊。
⽰例
1. INSERT INTO `user`
2. VALUES
3. ( 8, 'Buffett', '123456', '138********', 'Buffett@163', 0, CURRENT_TIMESTAMP, NULL );
执⾏结果
1. INSERT INTO `user`
2. VALUES
3. ( 8, 'Buffett', '123456', '138********', 'Buffett@163', 0, CURRENT_TIMESTAMP, NULL )
4. > 1062 - Duplicate entry '8' for key 'user.PRIMARY'
5. > 时间: 0.001s
insert ignore into
当插⼊数据时，如果唯⼀性校验出现重复问题，则忽略错误，只以警告形式返回，不执⾏此SQL语句；
如果没有重复性问题，则执⾏插⼊操作。
简单总结：重复则忽略，不重复则插⼊。
⽰例
1. INSERT IGNORE INTO `user`insert语句字段顺序
2. VALUES
3. ( 8, 'Buffett', '123456', '138********', 'Buffett@163', 0, CURRENT_TIMESTAMP, NULL );
执⾏结果
1. INSERT IGNORE INTO `user`
2. VALUES
3. ( 8, 'Buffett', '123456', '138********', 'Buffett@163', 0, CURRENT_TIMESTAMP, NULL )
4. > Affected rows: 0
5. > 时间: 0.001s
insert into … on duplicate key update
当插⼊数据时，如果唯⼀性校验出现重复问题，则在原有记录基础上，更新指定字段内容，其它字段内容保留；如果没有重复性问题，则执⾏插⼊操作。
简单总结：重复则更新指定字段，不重复则插⼊
⽰例
1. INSERT INTO `user` ( username, PASSWORD, mobile_phone_number, email )
2. VALUES
3. ( 'Buffett', '123456', '138********', 'Buffett@163' )
4. ON DUPLICATE KEY UPDATE mobile_phone_number = '138********';
执⾏结果
1. INSERT INTO `user` ( username, PASSWORD, mobile_phone_number, email )
2. VALUES
3. ( 'Buffett', '123456', '138********', 'Buffett@163' )
4. ON DUPLICATE KEY UPDATE mobile_phone_number = '138********'
5. > Affected rows: 2
6. > 时间: 0.022s
表记录，mobile_phone_number更新了，update_time也有值了，但是user_id没有变
user_id username password mobile_phone_number email delete_state create_time update_time
8Buffett12345613800000088Buffett@16302020-05-28
09:49:53
2020-05-28
10:04:30
replace into
replace into表⽰插⼊替换数据，当插⼊数据时，如果唯⼀性校验出现重复问题，删除旧记录，插⼊新记录；
如果没有重复性问题，则执⾏插⼊操作，效果和insert into是⼀样的。
简单总结：重复则先删除再插⼊新记录，不重复则插⼊
⽰例
1. REPLACE INTO `user` ( username, PASSWORD, mobile_phone_number, email )
2. VALUES
3. ( 'Buffett', '123456', '138********', 'Buffett@163' );
执⾏结果
1. REPLACE INTO `user` ( username, PASSWORD, mobile_phone_number, email )
2. VALUES
3. ( 'Buffett', '123456', '138********', 'Buffett@163' )
4. > Affected rows: 2
5. > 时间: 0.019s
表记录，user_id和mobile_phone_number变了，update_time变为了空
user_id username password mobile_phone_number email delete_state create_time update_time
10Buffett12345613800000099Buffett@16302020-05-28 09:49:53
使⽤场景总结
如果出现重复异常，希望捕获异常，则使⽤insert into
如果出现重复异常，希望保存旧纪录，忽略新纪录，则使⽤insert ignore into
如果出现重复异常，希望更新指定字段，则使⽤insert into … on duplicate key update 如果出现重复异常，希望删除旧记录，插⼊新记录，则使⽤replace into
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论