澳门新葡萄京娱乐场 1

忽略重复键

建立唯一索引时,有一项“忽略重复键”,这是何意呢?

在表设计的时候,可以打开“索引/键”对话框,如下:

mysql在存在主键冲突或者唯一键冲突的情况下,根据插入策略不同,一般有以下三种避免方法。
澳门新葡萄京娱乐场,1、insert ignore
2、replace into
3、insert on duplicate key update

这个意思相当于“跳过重复键”,就是遇到索引相同的情况下,新插入或更新记录的行为会被拒绝。

澳门新葡萄京娱乐场 1

注意,除非表有一个PRIMARY
KEY或UNIQUE索引,否则,使用以上三个语句没有意义,与使用单纯的INSERT
INTO相同。

如果我们批量导入(比如
DTS)有重复索引的数据到另一个“忽略重复键”索引的表,那么重复索引的记录只有一条会被导入。

我认为这个对话框有点误导人,上面有“选定的索引”、“索引名称”,但实际上我们建立的并不是索引,可能只是一个键。只有按上述红色标注建立的才是键,其它情况是索引。

一、insert ignore

insert
ignore会忽略数据库中已经存在的数据(根据主键或者唯一索引判断),如果数据库没有数据,就插入新的数据,如果有数据的话就跳过这条数据.

遇到了重复,也不是停止执行,而是跳过,虽然执行结果最终可能会报错说:已忽略重复的键。

我们可以这么验证:建立一个键,然后在表上点右键->“所有任务”->“生成
SQL
脚本”->“选项”,选中“编写索引脚本”,可以看到脚本中并没有与索引相关的脚本。

Case:

表结构如下:

root:test> show create table t3G
*************************** 1. row ***************************
       Table: t3
Create Table: CREATE TABLE `t3` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c1` int(11) DEFAULT NULL,
  `c2` varchar(20) DEFAULT NULL,
  `c3` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uidx_c1` (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

root:test> select * from t3;
    +----+------+------+------+
    | id | c1   | c2   | c3   |
    +----+------+------+------+
    |  1 |    1 | a    |    1 |
    |  2 |    2 | a    |    1 |
    |  8 | NULL | NULL |    1 |
    | 14 |    4 | bb   | NULL |
    | 17 |    5 | cc   |    4 |
    +----+------+------+------+
    5 rows in set (0.00 sec)

测试插入唯一键冲突的数据

root:test> insert ignore into t3 (c1,c2,c3) values(5,'cc',4),(6,'dd',5);     Query OK, 1 row affected, 1 warning (0.01 sec)
Records: 2  Duplicates: 1  Warnings: 1

如下,可以看到只插入了(6,’dd’,5)这条,同时有一条warning提示有重复的值。

root:test> show warnings;
+---------+------+---------------------------------------+
| Level   | Code | Message                               |
+---------+------+---------------------------------------+
| Warning | 1062 | Duplicate entry '5' for key 'uidx_c1' |
+---------+------+---------------------------------------+
1 row in set (0.00 sec)

root:test> select * from t3;
+----+------+------+------+
| id | c1   | c2   | c3   |
+----+------+------+------+
|  1 |    1 | a    |    1 |
|  2 |    2 | a    |    1 |
|  8 | NULL | NULL |    1 |
| 14 |    4 | bb   | NULL |
| 17 |    5 | cc   |    4 |
| 18 |    6 | dd   |    5 |
+----+------+------+------+
6 rows in set (0.00 sec)

重新查询表结构,发现虽然只增加了一条记录,但是AUTO_INCREMENT还是增加了2个(18变成20)

root:test> show create table t3G
    *************************** 1. row ***************************
       Table: t3
     Create Table: CREATE TABLE `t3` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `c1` int(11) DEFAULT NULL,
      `c2` varchar(20) DEFAULT NULL,
      `c3` int(11) DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uidx_c1` (`c1`)
    ) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8
    1 row in set (0.00 sec)

所以我觉得上述名称应该改为“选定的索引/键”、“索引/键名称”更合理。

二、replace into

  • replace into 首先尝试插入数据到表中。
    如果发现表中已经有此行数据(根据主键或者唯一索引判断)则先删除此行数据,然后插入新的数据,否则,直接插入新数据。
  • 使用replace into,你必须具有delete和insert权限

键的代码示例:

Case:

root:test> show create table t3G
*************************** 1. row ***************************
       Table: t3
Create Table: CREATE TABLE `t3` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c1` int(11) DEFAULT NULL,
  `c2` varchar(20) DEFAULT NULL,
  `c3` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uidx_c1` (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

root:test> select * from t3;
+----+------+--------+------+
| id | c1   | c2     | c3   |
+----+------+--------+------+
|  1 |    1 | cc     |    4 |
|  2 |    2 | dd     |    5 |
|  3 |    3 | qwewqe |    3 |
+----+------+--------+------+
3 rows in set (0.00 sec)

插入一条与记录id=3存在唯一键(列c1)冲突的数据

root:test> replace into t3 (c1,c2,c3) values(3,'new',8);
Query OK, 2 rows affected (0.02 sec)

root:test> select * from t3;
+----+------+------+------+
| id | c1   | c2   | c3   |
+----+------+------+------+
|  1 |    1 | cc   |    4 |
|  2 |    2 | dd   |    5 |
|  4 |    3 | new  |    8 |
+----+------+------+------+
3 rows in set (0.00 sec)

可以看到原有id=3,c1=3的记录不见了,新增了一条id=4,c1=3的记录.
replace
into语句执行完会返回一个数,来指示受影响的行的数目。该数是被删除和被插入的行数的和,上面的例子中2
rows affected .

ALTER TABLE [dbo].[Table1] ADD
 CONSTRAINT [IX_Table1] UNIQUE  CLUSTERED
 (
  [g]
 )  ON [PRIMARY]
GO

三、insert on duplicate key update

  • 如果在insert into 语句末尾指定了on duplicate key
    update,并且插入行后会导致在一个UNIQUE索引或PRIMARY
    KEY中出现重复值,则在出现重复值的行执行UPDATE;如果不会导致重复的问题,则插入新行,跟普通的insert
    into一样。
  • 使用insert into,你必须具有insert和update权限
  • 如果有新记录被插入,则受影响行的值显示1;如果原有的记录被更新,则受影响行的值显示2;如果记录被更新前后值是一样的,则受影响行数的值显示0

索引的代码示例:

Case:

root:test> show create table t3G
*************************** 1. row ***************************
       Table: t3
Create Table: CREATE TABLE `t3` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c1` int(11) DEFAULT NULL,
  `c2` varchar(20) DEFAULT NULL,
  `c3` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uidx_c1` (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

root:test> select * from t3; 
+----+------+------+------+
| id | c1   | c2   | c3   |
+----+------+------+------+
|  1 |    1 | fds  |    4 |
|  2 |    2 | ytu  |    3 |
|  3 |    3 | czx  |    5 |
+----+------+------+------+
3 rows in set (0.00 sec)

插入一条与记录id=3存在唯一键(列c1)冲突的数据

root:test> insert into t3(c1,c2,c3) values (3,'new',5) on duplicate key update c1=c1+3;   
Query OK, 2 rows affected (0.01 sec)

root:test> select * from t3;
+----+------+------+------+
| id | c1   | c2   | c3   |
+----+------+------+------+
|  1 |    1 | fds  |    4 |
|  2 |    2 | ytu  |    3 |
|  3 |    6 | czx  |    5 |
+----+------+------+------+
3 rows in set (0.00 sec)

可以看到,id=3的记录发生了改变,c1=原有的c1+3,其他列没有改变。

CREATE  UNIQUE  CLUSTERED  INDEX [IX_Table1] ON
[dbo].[Table1]([g]) ON [PRIMARY]
GO

结论:

  • 这三种方法都能避免主键或者唯一索引重复导致的插入失败问题。
  • insert ignore能忽略重复数据,只插入不重复的数据。
  • replace into和insert … on duplicate key
    update,都是替换原有的重复数据,区别在于replace
    into是删除原有的行后,在插入新行,如有自增id,这个会造成自增id的改变;insert
    … on duplicate key
    update在遇到重复行时,会直接更新原有的行,具体更新哪些字段怎么更新,取决于update后的语句。

介绍创建索引时的几个选项:

忽略重复键:在导入数据时,遇到重复值,是跳过,而不是停止执行,请参见忽略重复键。

填充索引:填充因子 > 0
时有效,让填充因子不仅作用于页级还作用于中间级,请参见填充因子。SQL
语句中,填充索引用 PAD_INDEX  表示。

不自动重新计算统计:创建索引需要记录的统计数据,选中该选项就表示使用原来的统计数据,因此非常不推荐选中该选项。

发表评论

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