MySQL不为人知的主键与唯一索引约束
source link: http://database.51cto.com/art/201809/583387.htm?amp%3Butm_medium=referral
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
今天和大家简单聊聊MySQL的约束主键与唯一索引约束：
PRIMARY KEY and UNIQUE Index Constraints
文章不长，保证有收获。
触发约束检测的时机：
-
insert
-
update
当检测到违反约束时，不同存储引擎的处理动作是不一样的。
如果存储引擎支持事务，SQL会自动回滚。
例子：
create table t1 ( id int(10) primary key )engine=innodb; insert into t1 values(1); insert into t1 values(1);
其中第二条insert会因为违反约束，而导致回滚。
通常可以使用：
show warnings;
来查看违反约束后的错误提示。
如果存储引擎不支持事务，SQL的执行会中断，此时可能会导致后续有符合条件的行不被操作，出现不符合预期的结果。
例子：
create table t2 ( id int(10) unique )engine=MyISAM; insert into t2 values(1); insert into t2 values(5); insert into t2 values(6); insert into t2 values(10); update t2 set id=id+1;
update执行后，猜猜会得到什么结果集？
猜想一：2, 6, 7, 11
猜想二：1, 5, 6, 10
.
.
.
都不对，正确答案是：2, 5, 6, 10
第一行id=1，加1后，没有违反unique约束，执行成功；
第二行id=5，加1后，由于id=6的记录存在，违反uinique约束，SQL终止，修改失败；
第三行id=6，第四行id=10便不再执行；
画外音：这太操蛋了，一个update语句，部分执行成功，部分执行失败。
为了避免这种情况出现，请使用InnoDB存储引擎，InnoDB在遇到违反约束时，会自动回滚update语句，一行都不会修改成功。
画外音：大家把存储引擎换成InnoDB，把上面的例子再跑一遍，印象更加深刻。
另外，对于insert的约束冲突，可以使用：
insert … on duplicate key
指出在违反主键或唯一索引约束时，需要进行的额外操作。
例子：
create table t3 ( id int(10) unique, flag char(10) default 'true' )engine=MyISAM; insert into t3(id) values(1); insert into t3(id) values(5); insert into t3(id) values(6); insert into t3(id) values(10); insert into t3(id) values(10) on duplicate key update flag='false';
insert执行后，猜猜会发生什么？
插入id=10的记录，会违反unique约束，此时执行update flag=’false’，于是有一行记录被update了。
这相当于执行：
update t3 set flag='false' where id=10;
仔细看，insert的结果返回，提示：
Query OK, 2 rows affected
有意思么？
画外音：本文所有实验，基于MySQL5.6。
总结，对于主键与唯一索引约束：
- 执行insert和update时，会触发约束检查
- InnoDB违反约束时，会回滚对应SQL
- MyISAM违反约束时，会中断对应的SQL，可能造成不符合预期的结果集
- 可以使用 insert … on duplicate key 来指定触发约束时的动作
- 通常使用 show warnings; 来查看与调试违反约束的ERROR
互联网大数据量高并发量业务，为了大家的身心健康，请使用InnoDB。
Recommend
-
21
MySQL - @wangbenjun5 - 最近接触了一个项目，里面的数据库设计让我“大开眼界”，很多表没有主键 id，取而代之的是复合主键，也就是几个字段同时作为主键，比如说：A 表：```nameageaddr```
-
23
UUID作为主键UUID自动生成主键，但是尽量还是不要使用UUID作为主键，在数据量大的时候，UUID做主键稍显慢一点。好处就是本地生成，不要基于数据库来了；不好之处就是，UUID 太长了、占用空间大，作为主键性能太差了；更重要的...
-
6
每张表都一定存在主键吗？ 关于这个问题，各位小伙伴们不妨先自己想一想，再往下寻找答案。 首先公布结论：对于 InnoDB 存储引擎来说，每张表都一定有个主键（Primary Key）！ 让人非常遗憾的是，网...
-
9
单个表上亿行数据的主键、索引设计，及分页查询 - ChenJacklondon的个人空间 - OSCHINA - 中文开源技术交流社区 一，概述 一般而言，我们对关系型数据库系统，进行表结构设计时，会按数据的种类，进行分类，一般有如下种类:
-
12
V2EX › MySQL MySQL 数据库主键用了字符串的 UUID 怎么办？ Geekerstar · 4 小时 43...
-
1
V2EX › MySQL 关于 MySQL 建表主键用自增还是 uuid 的问题 daoqiongsi1101 · 1 天前 ·...
-
9
这是图解MySQL的第3篇文章，这篇文章会让大家清楚地明白： 什么是InnoDB行格式？InnoDB页是什么？ InnoDB页和InnoDB行格式都有哪些字段信息？ 为什么推荐使用自增ID作为主键，而不推荐使用UUID？ InnoDB设计者如何设计...
-
2
[TOC] 之前松哥在前面的文章中介绍 MySQL 的索引时，有小伙伴表示被概念搞晕了，主键索引、非主键索引、聚簇索引、非聚簇索引、二级索引、辅助索引等等，今天咱们就来捋一捋这些概念。 1. 按照功能划分按照功能来划分，索引主要有四种...
-
4
1. 讲故事 最近在看 SQL SERVER 2008 查询性能优化，书中说当一个表创建了聚集索引，那么表中的行会按照主键索引的顺序物理排列，这里有一个关键词叫：物理排列，如果不了解底层原理，真的会被忽悠过去，其实仔细...
-
7
ClickHouse主键索引最佳实践 在本文中，我们将...
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK