以前使用SQL Server进行表分区的时候就碰到很多关于唯一索引的问题,今天我们来了解MySQL唯一索引的一些知识:包括如何创建,如何批量插入,还有一些技巧上SQL,感兴趣的朋友可以了解下
一、背景
以前使用SQL Server进行表分区的时候就碰到很多关于唯一索引的问题:Step8:SQL Server 当表分区遇上唯一约束,没想到在MySQL的分区中一样会遇到这样的问题:MySQL表分区实战。
今天我们来了解MySQL唯一索引的一些知识:包括如何创建,如何批量插入,还有一些技巧上SQL;
这些问题的根源在什么地方?有什么共同点?MySQL中也有分区对齐的概念?唯一索引是在很多系统中都会出现的要求,有什么办法可以避免?它对性能的影响有多大?
二、过程
(一) 导入差异数据,忽略重复数据,IGNORE INTO的使用
在MySQL创建表的时候,我们通常创建一个表的时候是以一个自增ID值作为主键,那么MySQL就会以PRIMARY KEY作为聚集索引键和主键,既然是主键,那当然是唯一的了,所以重复执行下面的插入语句会报1062错误:如Figure1所示;
代码如下:
-- 创建测试表
CREATE TABLE `testtable` (
`Id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`UserId` INT(11) DEFAULT NULL,
`UserName` VARCHAR(10) DEFAULT NULL,
`UserType` INT(11) DEFAULT NULL,
PRIMARY KEY (`Id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
-- 插入测试数据
INSERT INTO testtable(Id,UserId,UserName,UserType)
VALUES(1,101,'aa',1),(2,102,'bbb',2),(3,103,'ccc',3);
但是在实际的生产环境中,需求往往是需要在UserId键值中设置唯一索引,今天我就以这个作为示例,进行唯一索引的测试:
代码如下:
-- 创建测试表1
CREATE TABLE `testtable1` (
`Id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`UserId` INT(11) DEFAULT NULL,
`UserName` VARCHAR(10) DEFAULT NULL,
`UserType` INT(11) DEFAULT NULL,
PRIMARY KEY (`Id`),
UNIQUE KEY `IX_UserId` (`UserId`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
-- 创建测试表2
CREATE TABLE `testtable2` (
`Id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`UserId` INT(11) DEFAULT NULL,
`UserName` VARCHAR(10) DEFAULT NULL,
`UserType` INT(11) DEFAULT NULL,
PRIMARY KEY (`Id`),
UNIQUE KEY `IX_UserId` (`UserId`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
-- 插入测试数据1
INSERT INTO testtable1(Id,UserId,UserName,UserType)
VALUES(1,101,'aa',1),(2,102,'bbb',2),(3,103,'ccc',3);
-- 插入测试数据2
INSERT INTO testtable2(Id,UserId,UserName,UserType)
VALUES(1,201,'aaa',1),(2,202,'bbb',2),(3,203,'ccc',3),(4,101,'xxxx',5);
(Figure2:记录)
(Figure3:testtable2记录)
通过执行上面的SQL脚本,我们在testtable1和testtable2都创建了唯一索引:UNIQUE KEY `IX_UserId` (`UserId`),这就说明UserId在testtable1和testtable2表中都是唯一的,如果把testtable2的数据批量导入到testtable1,如果执行下面【导入1】的SQL,就会出现1062的错误,导致整个过程会回滚,没有达到导入差异数据的目的。
代码如下:
INSERT INTO testtable1(UserId,UserName,UserType)
SELECT UserId,UserName,UserType FROM testtable2;
(Figure4:Duplicate entry '101' for key 'IX_UserId')
MySQL提供一个关键字:IGNORE,这个关键字判断每条记录是否存在,是否违反饿了表中的唯一索引,如果存在就不插入,而不存在的记录就会插入。
代码如下:
-- 导入2
INSERT IGNORE INTO testtable1(UserId,UserName,UserType)
SELECT UserId,UserName,UserType FROM testtable2;
所以执行完【导入2】,就会产生Figure5的结果,这已经达到了我们的目的了,但是你有没发现自增的ID值跳过了一些值,这是因为我们之前执行【导入1】失败造成的,虽然我们的事务回滚了,但是自增ID会出现断层。在SQL Server中也会有这样的问题。扩展阅读:简单实用SQL脚本Part:查找SQL Server 自增ID值不连续记录
(5:IGNORE效果)
1. 把testtable1和testtable2分别回滚到Figure2和Figure3的状态(使用TRUNCATE TABLE命名再执行Insert语句),这个时候再执行下面的SQL,看有什么效果:
代码如下:
-- 导入3
REPLACE INTO testtable1(UserId,UserName)
SELECT UserId,UserName FROM testtable2;
(Figure6:REPLACE效果)
从上图Figure6中,我们可以看到:UserId为101的记录发生了改变,不单UserName修改了,而且UserType也变为NULL了。
所以,如果导入中发现了重复的,先删除再插入,如果记录有多个字段,在插入的时候如果有的字段没有赋值,那么新插入的记录这些字段为空(新插入记录的UserType都为NULL)。
需要注意的是,当你replace的时候,如果被插入的表如果没有指定列,会用NULL表示,而不是这个表原来的内容。如果插入的内容列和被插入的表列一样,则不会出现NULL。
2. 如果我们表结构UserType字段不允许为空,而且没有默认值的情况,执行【导入3】会发生什么事情呢?
(Figure7:返回警告信息)
(Figure8:UserType被设置为0)
通过Figure7和Figure8,我们知道数据记录还是插入了,只是返回Field 'UserType' doesn't have a default value的警告,插入记录的UserType字段都被设置为0('UserType' 为int数据类型)。
3. 如果我们希望导入的时候一起更新UserType字段的值,这自然很简单了,使用下面的SQL脚本就可以解决:
代码如下:
-- 导入4
REPLACE INTO testtable1(UserId,UserName,UserType)
SELECT UserId,UserName,UserType FROM testtable2;
把testtable1和testtable2分别回滚到Figure2和Figure3的状态(使用TRUNCATE TABLE命名再执行Insert语句),这个时候再执行下面的SQL,看有什么效果:
代码如下:
-- 导入5
INSERT INTO testtable1(UserId,UserName)
SELECT UserId,UserName FROM testtable2
ON DUPLICATE KEY UPDATE
testtable1.UserName = testtable2.UserName;
UserId为101的记录:更新了UserName的值,保留了UserType的值;但是由于【导入5】中没有指定UserType,所以新插入记录的UserType是为NULL的。
代码如下:
-- 导入6
INSERT INTO testtable1(UserId,UserName,UserType)
SELECT UserId,UserName,UserType FROM testtable2
ON DUPLICATE KEY UPDATE
testtable1.UserName = testtable2.UserName;
(Figure11:保留UserType值)
对比Figure2、Figure3与Figure11,只插入testtable2表的UserId,UserName字段,但是保留testtable1表的UserType字段。如果发现有重复的记录,做更新操作;在原有记录基础上,更新指定字段内容,其它字段内容保留。
(四) 总结
当在一个UNIQUE键上插入包含重复值的记录时,默认的insert会报1062错误,MYSQL可以通过以上三种不同的方式和你的业务逻辑进行处理。
三、参考文献

MySQL適合初學者學習數據庫技能。 1.安裝MySQL服務器和客戶端工具。 2.理解基本SQL查詢,如SELECT。 3.掌握數據操作:創建表、插入、更新、刪除數據。 4.學習高級技巧:子查詢和窗口函數。 5.調試和優化:檢查語法、使用索引、避免SELECT*,並使用LIMIT。

MySQL通過表結構和SQL查詢高效管理結構化數據,並通過外鍵實現表間關係。 1.創建表時定義數據格式和類型。 2.使用外鍵建立表間關係。 3.通過索引和查詢優化提高性能。 4.定期備份和監控數據庫確保數據安全和性能優化。

MySQL是一個開源的關係型數據庫管理系統,廣泛應用於Web開發。它的關鍵特性包括:1.支持多種存儲引擎,如InnoDB和MyISAM,適用於不同場景;2.提供主從復制功能,利於負載均衡和數據備份;3.通過查詢優化和索引使用提高查詢效率。

SQL用於與MySQL數據庫交互,實現數據的增、刪、改、查及數據庫設計。 1)SQL通過SELECT、INSERT、UPDATE、DELETE語句進行數據操作;2)使用CREATE、ALTER、DROP語句進行數據庫設計和管理;3)複雜查詢和數據分析通過SQL實現,提升業務決策效率。

MySQL的基本操作包括創建數據庫、表格,及使用SQL進行數據的CRUD操作。 1.創建數據庫:CREATEDATABASEmy_first_db;2.創建表格:CREATETABLEbooks(idINTAUTO_INCREMENTPRIMARYKEY,titleVARCHAR(100)NOTNULL,authorVARCHAR(100)NOTNULL,published_yearINT);3.插入數據:INSERTINTObooks(title,author,published_year)VA

MySQL在Web應用中的主要作用是存儲和管理數據。 1.MySQL高效處理用戶信息、產品目錄和交易記錄等數據。 2.通過SQL查詢,開發者能從數據庫提取信息生成動態內容。 3.MySQL基於客戶端-服務器模型工作,確保查詢速度可接受。

構建MySQL數據庫的步驟包括:1.創建數據庫和表,2.插入數據,3.進行查詢。首先,使用CREATEDATABASE和CREATETABLE語句創建數據庫和表,然後用INSERTINTO語句插入數據,最後用SELECT語句查詢數據。

MySQL適合初學者,因為它易用且功能強大。 1.MySQL是關係型數據庫,使用SQL進行CRUD操作。 2.安裝簡單,需配置root用戶密碼。 3.使用INSERT、UPDATE、DELETE、SELECT進行數據操作。 4.複雜查詢可使用ORDERBY、WHERE和JOIN。 5.調試需檢查語法,使用EXPLAIN分析查詢。 6.優化建議包括使用索引、選擇合適數據類型和良好編程習慣。


熱AI工具

Undresser.AI Undress
人工智慧驅動的應用程序,用於創建逼真的裸體照片

AI Clothes Remover
用於從照片中去除衣服的線上人工智慧工具。

Undress AI Tool
免費脫衣圖片

Clothoff.io
AI脫衣器

AI Hentai Generator
免費產生 AI 無盡。

熱門文章

熱工具

Atom編輯器mac版下載
最受歡迎的的開源編輯器

MantisBT
Mantis是一個易於部署的基於Web的缺陷追蹤工具,用於幫助產品缺陷追蹤。它需要PHP、MySQL和一個Web伺服器。請查看我們的演示和託管服務。

SublimeText3 Mac版
神級程式碼編輯軟體(SublimeText3)

記事本++7.3.1
好用且免費的程式碼編輯器

SublimeText3漢化版
中文版,非常好用