1. 首頁 > 生活 > >

mysql建立唯一索引的語句 添加唯一索引的sql語句

小知識



文章插圖
mysql建立唯一索引的語句 添加唯一索引的sql語句

文章插圖

當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', [email protected]', 0, CURRENT_TIMESTAMP, NULL);INSERT INTO `user` VALUES (2, '馬云', '123456', '13800000011', [email protected]', 0, CURRENT_TIMESTAMP, NULL);INSERT INTO `user` VALUES (3, '馬化騰', '123456', '13800000022', [email protected]', 0, CURRENT_TIMESTAMP, NULL);INSERT INTO `user` VALUES (4, '李彥宏', '123456', '13800000033', [email protected]', 0, CURRENT_TIMESTAMP, NULL);INSERT INTO `user` VALUES (5, '任正非', '123456', '13800000044', [email protected]', 0, CURRENT_TIMESTAMP, NULL);INSERT INTO `user` VALUES (6, 'Jobs', '123456', '13800000055', [email protected]', 0, CURRENT_TIMESTAMP, NULL);INSERT INTO `user` VALUES (7, 'Bill Gates', '123456', '13800000066', 'Bill [email protected]', 0, CURRENT_TIMESTAMP, NULL);INSERT INTO `user` VALUES (8, 'Buffett', '123456', '13800000077', [email protected]', 0, CURRENT_TIMESTAMP, NULL);SET FOREIGN_KEY_CHECKS = 1;常用插入語句
insert into
當插入數據時，如果唯一性校驗出現重復問題，則報錯；
如果沒有重復性問題，則執行插入操作 。
簡單總結：重復則報錯，不重復則插入 。
示例
INSERT INTO `user`VALUES( 8, 'Buffett', '123456', '13800000077', [email protected]', 0, CURRENT_TIMESTAMP, NULL );執行結果
INSERT INTO `user`VALUES( 8, 'Buffett', '123456', '13800000077', [email protected]', 0, CURRENT_TIMESTAMP, NULL )> 1062 - Duplicate entry '8' for key 'user.PRIMARY'> 時間: 0.001sinsert into … on duplicate key update
當插入數據時，如果唯一性校驗出現重復問題，則在原有記錄基礎上，更新指定字段內容，其它字段內容保留；
如果沒有重復性問題，則執行插入操作 。
【mysql建立唯一索引的語句 添加唯一索引的sql語句】簡單總結：重復則更新指定字段，不重復則插入
示例
INSERT INTO `user` ( username, PASSWORD, mobile_phone_number, email )VALUES( 'Buffett', '123456', '13800000088', [email protected]' ) ON DUPLICATE KEY UPDATE mobile_phone_number = '13800000088';執行結果
INSERT INTO `user` ( username, PASSWORD, mobile_phone_number, email )VALUES( 'Buffett', '123456', '13800000088', [email protected]' ) 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', [email protected]' );執行結果
REPLACE INTO `user` ( username, PASSWORD, mobile_phone_number, email )VALUES( 'Buffett', '123456', '13800000099', [email protected]' )> 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