MSSQL中的表变量,MySQL批量插入遇上绝无仅有索引制止方式

如今在看《Microsoft SQL
Server200五本事内幕:T-SQL程序设计》

近日在看《Microsoft SQL
Server200伍本事内幕:T-SQL程序设计》

一、背景

一、背景

一、表变量的业务上下文中涉及,表变量不受外部事务回滚影响。

一、表变量的政工上下文中关系,表变量不受外部事务回滚影响。

在此以前使用SQL
Server举行表分区的时候就碰着大多关于唯一索引的难题:Step捌:SQL Server
当表分区遇上绝无仅有约束,没悟出在MySQL的分区中同样会高出这么的标题:MySQL表分区实战。

在此以前使用SQL
Server实行表分区的时候就遭受很多有关唯一索引的难点:Step捌:SQL Server
当表分区遇上绝无仅有约束,没悟出在MySQL的分区中一律会遇上那样的标题:MySQL表分区实战。

举个例子:

举个例子:

后天我们来理解MySQL唯一索引的1些文化:包蕴哪些创建,怎么着批量布置,还有一些本事上SQL;

后天我们来打探MySQL唯一索引的壹部分文化:蕴含什么样成立,怎么着批量布置,还有部分本领上SQL;

DECLARE 
@TA TABLE(col INT);
INSERT
@TA VALUES(0);
SELECT
* FROM @TA;
BEGIN
TRAN
  
 INSERT @TA VALUES(1);
  
 SELECT * FROM @TA;
ROLLBACK;
SELECT
* FROM @TA;

DECLARE 
@TA TABLE(col INT);
INSERT
@TA VALUES(0);
SELECT
* FROM @TA;
BEGIN
TRAN
  
 INSERT @TA VALUES(1);
  
 SELECT * FROM @TA;
ROLLBACK;
SELECT
* FROM @TA;

这么些难题的来源于在如何地方?有怎样共同点?MySQL中也有分区对齐的定义?唯一索引是在重重系统中都会出现的渴求,有哪些方式能够制止?它对质量的影响有多大?

那几个主题素材的起点在如何地点?有怎么样共同点?MySQL中也有分区对齐的概念?唯一索引是在数不清系统中都会冒出的渴求,有哪些措施可防止止?它对品质的熏陶有多大?

/*——————————————–*/

/*——————————————–*/

二、过程

二、过程

率先个SELECT输出结果:

先是个SELECT输出结果:

(一) 导入差别数据,忽略重复数据,IGNORE INTO的利用

(1) 导入差距数据,忽略重复数据,IGNORE INTO的行使

col

col

在MySQL创立表的时候,我们常见制造3个表的时候是以三个自增ID值作为主键,那么MySQL就会以P索罗德IMA奔驰G级Y
KEY作为聚集索引键和主键,既然是主键,那本来是绝无仅有的了,所以重复实行上面包车型大巴插入语句会报106二错误:如Figure壹所示;

在MySQL创设表的时候,我们平常创造一个表的时候是以四个自增ID值作为主键,那么MySQL就会以PBMWX3IMA哈弗Y
KEY作为聚集索引键和主键,既然是主键,那当然是绝无仅有的了,所以再度实践下边包车型地铁插入语句会报106二张冠李戴:如Figure一所示;

0

MSSQL中的表变量,MySQL批量插入遇上绝无仅有索引制止方式。0

复制代码 代码如下:

复制代码 代码如下:

/*——————————————–*/

/*——————————————–*/

— 创造测试表
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;

— 创设测试表
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;

第二个SELECT输出结果:

第四个SELECT输出结果:

— 插入测试数据
INSERT INTO testtable(Id,UserId,UserName,UserType)
VALUES(1,101,’aa’,1),(2,102,’bbb’,2),(3,103,’ccc’,3);

— 插入测试数据
INSERT INTO testtable(Id,UserId,UserName,UserType)
VALUES(1,101,’aa’,1),(2,102,’bbb’,2),(3,103,’ccc’,3);

col

col

997755.com澳门葡京 1

997755.com澳门葡京 2

0

0

(Figure1:Duplicate entry ‘1’ for key
‘PRIMARY’)

(Figure1:Duplicate entry ‘1’ for key
‘PRIMARY’)

1

1

唯独在其实的生育条件中,需要往往是需求在UserId键值中设置唯一索引,昨天本身就以那一个作为示范,进行唯一索引的测试:

可是在实际上的生育环境中,需要往往是索要在UserId键值中安装唯一索引,后天本身就以这一个作为示范,举行唯一索引的测试:

/*——————————————–*/

/*——————————————–*/

复制代码 代码如下:

复制代码 代码如下:

 

 

— 创建测试表一
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;

— 创立测试表一
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;

其四个SELECT输出结果:

其多个SELECT输出结果:

— 成立测试表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;

— 成立测试表二
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;

 

 

997755.com澳门葡京 ,– 插入测试数据一
INSERT INTO testtable1(Id,UserId,UserName,UserType)
VALUES(1,101,’aa’,1),(2,102,’bbb’,2),(3,103,’ccc’,3);

— 插入测试数据一
INSERT INTO testtable1(Id,UserId,UserName,UserType)
VALUES(1,101,’aa’,1),(2,102,’bbb’,2),(3,103,’ccc’,3);

col

col

— 插入测试数据二
INSERT INTO testtable2(Id,UserId,UserName,UserType)
VALUES(1,201,’aaa’,1),(2,202,’bbb’,2),(3,203,’ccc’,3),(4,101,’xxxx’,5);

— 插入测试数据二
INSERT INTO testtable2(Id,UserId,UserName,UserType)
VALUES(1,201,’aaa’,1),(2,202,’bbb’,2),(3,203,’ccc’,3),(4,101,’xxxx’,5);

 

 

997755.com澳门葡京 3

997755.com澳门葡京 4

0

0

(Figure2:testtable1记录)

(Figure2:testtable1记录)

 

 

997755.com澳门葡京 5

997755.com澳门葡京 6

1

1

(Figure3:testtable2记录)

(Figure3:testtable2记录)

 

 

透过进行上边的SQL脚本,我们在testtable1和testtable贰都成立了唯一索引:UNIQUE
KEY `IX_UserId`
(`UserId`),那就注明UserId在testtable1和testtable2表中都以唯一的,如若把testtable二的数量批量导入到testtable一,假使实行上面【导入一】的SQL,就会出现十6二的错误,导致整个经过会回滚,没有直达导入差别数据的目标。

透过试行上面包车型地铁SQL脚本,大家在testtable一和testtable二都成立了唯一索引:UNIQUE
KEY `IX_UserId`
(`UserId`),那就表明UserId在testtable一和testtable2表中都以绝无仅有的,借使把testtable二的多寡批量导入到testtable1,假若奉行上面【导入一】的SQL,就会产出106二的谬误,导致整个经过会回滚,未有高达导入差别数据的目标。

/*——————————————–*/

/*——————————————–*/

复制代码 代码如下:

复制代码 代码如下:

原作中举了1个事例,总结起来正是如若在触发器(inserted/deleted)中回滚数据变动,但又想记录这个更换,怎么做吧?能够把inserted/deleted中的退换数据保存到表变量中,然后工作回滚数据变动操作。

原来的作品中举了二个事例,归纳起来便是借使在触发器(inserted/deleted)中回滚数据变动,但又想记录那一个改造,如何做啊?能够把inserted/deleted中的退换数据保存到表变量中,然后职业回滚数据变动操作。

INSERT INTO testtable1(UserId,UserName,UserType)
SELECT UserId,UserName,UserType FROM testtable2;

INSERT INTO testtable1(UserId,UserName,UserType)
SELECT UserId,UserName,UserType FROM testtable2;

小编还论及,利用表变量这一个特点,不仅能够记下修改后撤消的数量,而且比较一时半刻表(一时表会发寿辰志操作和锁操作,但涉及的锁比数据表少),能够减去日志操作和锁操作。

作者还关系,利用表变量那本个性,不仅能够记录修改后撤消的数码,而且相比较权且表(权且表会时有发生日志操作和锁操作,但涉及的锁比数据表少),能够减去日志操作和锁操作。

997755.com澳门葡京 7

997755.com澳门葡京 8

贰、使用表变量时候的限制标准

2、使用表变量时候的范围标准

(Figure4:Duplicate entry ‘101’ for key ‘IX_UserId’)

(Figure4:Duplicate entry ‘101’ for key ‘IX_UserId’)

壹)表变量只好成立主键key和唯一索引,不扶助非唯一索引。假诺急需把某部非主键字段col一营造为索引,能够将key和col一营形成两个主键key。比如说,产品表变量@Product,主键为Id_Guid,今后内需将@Product中的产品编码字段Code_Nvarchar字段插足索引,能够将(Id_Guid,Code_Nvarchar)构建为主键Key

一)表变量只好创设主键key和唯一索引,不扶助非唯一索引。倘使必要把某部非主键字段col一构建为索引,可以将key和col一构建成2个主键key。比如说,产品表变量@Product,主键为Id_Guid,以后内需将@Product中的产品编码字段Code_Nvarchar字段到场索引,能够将(Id_Guid,Code_Nvarchar)创设为主键Key

MySQL提供叁个最首要字:IGNORE,那些重点字决断每条记下是还是不是存在,是或不是违背饿了表中的绝无仅有索引,假若存在就不插入,而不存在的笔录就会插入。

MySQL提供三个重点字:IGNORE,那个重点字判定每条记下是或不是存在,是还是不是违背饿了表中的绝无仅有索引,假使存在就不插入,而不存在的记录就会插入。

2)表变量创造之后,就不可能改改它的结构。比如说创立了表变量@Product(Id_Guid,Code_Nvarchar)之后,就无法为@Product再增加或然去除贰个字段。表变量的那些界定标准也能够减去编写翻译次数。(表的框架结构改变之后会导致重新编写翻译)

二)表变量创设之后,就不能够改改它的构造。比如说成立了表变量@Product(Id_Guid,Code_Nvarchar)之后,就不能够为@Product再增多也许去除三个字段。表变量的那些限制标准也得以减我译次数。(表的框架结构退换之后会招致重新编译)

复制代码 代码如下:

复制代码 代码如下:

三)表变量中,不可能以表名.列表的方法来访问列。比如不可能用@Product.Id_Guid来访问表变量@Product的Id_Guid字段。

叁)表变量中,不能以表名.列表的章程来访问列。比如不可能用@Product.Id_Guid来访问表变量@Product的Id_Guid字段。

— 导入2
INSERT IGNORE INTO testtable1(UserId,UserName,UserType)
SELECT UserId,UserName,UserType FROM testtable2;

— 导入2
INSERT IGNORE INTO testtable1(UserId,UserName,UserType)
SELECT UserId,UserName,UserType FROM testtable2;

DECLARE 
@TA TABLE(col INT);
DECLARE  @TB TABLE(col INT);
INSERT @TA VALUES(1);
INSERT @TB VALUES(1);
SELECT * FROM @TA A JOIN @TB B
ON(A.col=B.col);
/*上面那一个语句会报语法错误*/
–SELECT * FROM @TA JOIN @TB
ON(@TA.col=@TB.col);

DECLARE 
@TA TABLE(col INT);
DECLARE  @TB TABLE(col INT);
INSERT @TA VALUES(1);
INSERT @TB VALUES(1);
SELECT * FROM @TA A JOIN @TB B
ON(A.col=B.col);
/*上面那几个语句会报语法错误*/
–SELECT * FROM @TA JOIN @TB
ON(@TA.col=@TB.col);

之所以实行完【导入贰】,就会时有产生Figure5的结果,那1度高达了我们的指标了,然而你有没觉察自增的ID值跳过了某个值,那是因为大家在此以前实行【导入壹】战败致使的,即使我们的作业回滚了,但是自增ID会现出断层。在SQL
Server中也会有这样的标题。增加阅读:轻易实用SQL脚本Part:查找SQL Server
自增ID值不接二连三记录

因而举办完【导入二】,就会爆发Figure5的结果,那曾经高达了我们的指标了,不过你有没发现自增的ID值跳过了1部分值,那是因为大家事先试行【导入一】战败产生的,即使咱们的事务回滚了,可是自增ID会产出断层。在SQL
Server中也会有如此的主题素材。扩张阅读:简单实用SQL脚本Part:查找SQL Server
自增ID值不一连记录

4)在修改表变量的查询中,不采用并行实施布署。

四)在修改表变量的查询中,不利用并行施行安顿。

997755.com澳门葡京 9

997755.com澳门葡京 10

 

 

(Figure5:IGNORE效果)

(Figure5:IGNORE效果)

 

 

(二) 导入并覆盖再度数据,REPLACE INTO
的施用

(二) 导入并覆盖再一次数据,REPLACE INTO
的选取

一.
把testtable1和testtable贰分别回滚到Figure2和Figure三的图景(使用TRUNCATE
TABLE命名再进行Insert语句),今年再实施下边的SQL,看有何效果:

1.
把testtable一和testtable二分别回滚到Figure二和Figure3的情状(使用TRUNCATE
TABLE命名再实行Insert语句),这年再举办上面包车型客车SQL,看有何意义:

复制代码 代码如下:

复制代码 代码如下:

— 导入3
REPLACE INTO testtable1(UserId,UserName)
SELECT UserId,UserName FROM testtable2;

— 导入3
REPLACE INTO testtable1(UserId,UserName)
SELECT UserId,UserName FROM testtable2;

997755.com澳门葡京 11

997755.com澳门葡京 12

(Figure6:REPLACE效果)

(Figure6:REPLACE效果)

从上海体育地方Figure6中,大家能够看来:UserId为101的笔录暴发了改换,不单UserName修改了,而且UserType也改成NULL了。

从上海教室Figure陆中,大家能够看来:UserId为拾1的笔录发生了退换,不单UserName修改了,而且UserType也改成NULL了。

据此,如果导入中窥见了重复的,先删除再插入,如若记录有四个字段,在插入的时候假使局地字段未有赋值,那么新插入的记录这几个字段为空(新插入记录的UserType都为NULL)。

于是,如果导入中窥见了重新的,先删除再插入,纵然记录有多少个字段,在插入的时候假如有的字段没有赋值,那么新插入的笔录那个字段为空(新插入记录的UserType都为NULL)。

亟需小心的是,当你replace的时候,假若被插入的表假若未有钦点列,会用NULL表示,而不是以此表原来的始末。假若插入的内容列和被插入的表列同样,则不会现出NULL。

亟需注意的是,当您replace的时候,假使被插入的表借使未有钦点列,会用NULL表示,而不是以此表原来的内容。假诺插入的内容列和被插入的表列一样,则不会油但是生NULL。

二.
只要我们表结构UserType字段不容许为空,而且从不暗中同意值的景观,试行【导入3】会时有爆发哪些职业吗?

二.
如若大家表结构UserType字段不容许为空,而且尚未暗中同意值的情景,实行【导入三】会时有产生什么样工作呢?

997755.com澳门葡京 13

997755.com澳门葡京 14

(Figure七:重回警告音讯)

(Figure七:重回警告消息)

997755.com澳门葡京 15

997755.com澳门葡京 16

(Figure八:UserType棉被服装置为0)

(Figure捌:UserType棉被服装置为0)

经过Figure七和Figure8,我们领略数码记录依旧插入了,只是重回Field’UserType’ doesn’t have a default
value的警戒,插入记录的UserType字段都被安装为0(’UserType’
为int数据类型)。

通过Figure七和Figure8,大家掌握多少记录依旧插入了,只是重返Field’UserType’ doesn’t have a default
value的警告,插入记录的UserType字段都被设置为0(’UserType’
为int数据类型)。

三.
一旦我们意在导入的时候一同更新UserType字段的值,那当然很简单了,使用下边的SQL脚本就足以消除:

三.
万1大家意在导入的时候共同更新UserType字段的值,那当然很轻巧了,使用上面包车型客车SQL脚本就足以消除:

复制代码 代码如下:

复制代码 代码如下:

— 导入4
REPLACE INTO testtable1(UserId,UserName,UserType)
SELECT UserId,UserName,UserType FROM testtable2;

— 导入4
REPLACE INTO testtable1(UserId,UserName,UserType)
SELECT UserId,UserName,UserType FROM testtable2;

997755.com澳门葡京 17

997755.com澳门葡京 18

(Figure玖:一同更新UserType)

(Figure玖:一齐更新UserType)

(叁)
导入保留重复数据未钦点字段,INSERT
INTO ON DUPLICATE KEY UPDATE
的选用

(3)
导入保留重复数据未钦命字段,INSERT
INTO ON DUPLICATE KEY UPDATE
的采用

把testtable壹和testtable二分别回滚到Figure二和Figure三的动静(使用TRUNCATE
TABLE命名再实施Insert语句),今年再施行上面包车型客车SQL,看有啥效益:

把testtable一和testtable二分别回滚到Figure贰和Figure叁的意况(使用TRUNCATE
TABLE命名再推行Insert语句),今年再推行上边包车型大巴SQL,看有何效益:

复制代码 代码如下:

复制代码 代码如下:

— 导入5
INSERT INTO testtable1(UserId,UserName)
SELECT UserId,UserName FROM testtable2
ON DUPLICATE KEY UPDATE
testtable1.UserName = testtable2.UserName;

— 导入5
INSERT INTO testtable1(UserId,UserName)
SELECT UserId,UserName FROM testtable2
ON DUPLICATE KEY UPDATE
testtable1.UserName = testtable2.UserName;

997755.com澳门葡京 19

997755.com澳门葡京 20

(Figure10:保留UserType值)

(Figure10:保留UserType值)

比较Figure二、Figure3与Figure十,UserId为十一的笔录:更新了UserName的值,保留了UserType的值;然而由于【导入5】中从不钦点UserType,所以新插入记录的UserType是为NULL的。

相比较Figure二、Figure三与Figure10,UserId为十一的笔录:更新了UserName的值,保留了UserType的值;可是由于【导入五】中从未钦定UserType,所以新插入记录的UserType是为NULL的。

复制代码 代码如下:

复制代码 代码如下:

— 导入6
INSERT INTO testtable1(UserId,UserName,UserType)
SELECT UserId,UserName,UserType FROM testtable2
ON DUPLICATE KEY UPDATE
testtable1.UserName = testtable2.UserName;

— 导入6
INSERT INTO testtable1(UserId,UserName,UserType)
SELECT UserId,UserName,UserType FROM testtable2
ON DUPLICATE KEY UPDATE
testtable1.UserName = testtable2.UserName;

997755.com澳门葡京 21

997755.com澳门葡京 22

(Figure11:保留UserType值)

(Figure11:保留UserType值)

比较Figure2、Figure3与Figure1一,只插入testtable2表的UserId,UserName字段,然而保留testtable一表的UserType字段。假诺发现有重复的记录,做立异操作;在原始记录基础上,更新钦定字段内容,此外字段内容保留。

相比Figure2、Figure三与Figure11,只插入testtable贰表的UserId,UserName字段,不过保留testtable一表的UserType字段。若是发现成重新的笔录,做创新操作;在原有记录基础上,更新钦命字段内容,其余字段内容保留。

(四) 总结

(四) 总结

当在贰个UNIQUE键上插入包罗重复值的笔录时,私下认可的insert会报拾6二荒谬,MYSQL能够透过以上二种不一致的办法和您的事情逻辑进行拍卖。

当在一个UNIQUE键上插入包括重复值的笔录时,暗中同意的insert会报拾6二错误,MYSQL能够透过以上三种分化的形式和您的事体逻辑实行拍卖。

三、参考文献

3、参考文献

MYSQL插入处理重复键值的两种方法

MYSQL插入处理重复键值的两种办法

您可能感兴趣的小说:

  • mysql 增加索引 mysql
    如何创建索引
  • MySQL索引类型总计和选拔手艺以及注意事项
  • MySQL
    主键与索引的联系与不一样分析
  • 依照mysql全文索引的深远精晓
  • Mysql索引会失效的两种意况分析
  • Mysql中的Btree与Hash索引相比较
  • MYSQL索引无效和目录有效的详细介绍
  • MySQL 索引分析和优化
  • Mysql建表与索引使用正规详解
  • mysql索引使用技艺及注意事项

背景 在此在此以前使用SQL
Server实行表分区的时候就遇到诸多有关唯一索引的标题:Step8:SQL Server
当表分区遇上唯一约束,没悟出在MySQL的分区…

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

*
*
Website