

當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 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';
執行結果
INSERT INTO `user` ( username, PASSWORD, mobile_phone_number, email )
VALUES
( 'Buffett', '123456', '13800000088', 'Buffett@163.com' )
ON DUPLICATE KEY UPDATE mobile_phone_number = '13800000088'
> Affected rows: 2
> 時間: 0.022s
表記錄,mobile_phone_number更新了,update_time也有值了,但是user_id沒有變

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變為了空


使用場景總結
如果出現重復異常,希望捕獲異常,則使用insert into
如果出現重復異常,希望保存舊紀錄,忽略新紀錄,則使用insert ignore into
如果出現重復異常,希望更新指定字段,則使用insert into … on duplicate key update
如果出現重復異常,希望刪除舊記錄,插入新記錄,則使用replace into
版權聲明:本文內容由互聯網用戶自發貢獻,該文觀點僅代表作者本人。本站僅提供信息存儲空間服務,不擁有所有權,不承擔相關法律責任。如發現本站有涉嫌抄襲侵權/違法違規的內容, 請發送郵件至 舉報,一經查實,本站將立刻刪除。