MySQL和SQLServer和Oracle的索引初次探索2

你的名字 2021-12-10 13:45 146阅读 0赞

MySQL和SQLServer的索引初次探索2：

在这里我们先不谈其他的索引，我们就先来看一下MySQL和SQLServer的唯一索引的诡异之处：

首先两个数据库的创建唯一联合索引的语句是一样的

MySQL:

\-- 建造组合索引

CREATE UNIQUE INDEX  stu\_login  ON  t\_login1(stu\_name,stu\_pwd,login\_type,test);

SQLServer:

\-- 创建索引

CREATE UNIQUE INDEX stu\_login ON dbo.t\_login1 (stu\_name,stu\_pwd,login\_type,test);

Oracle:

\-- 创建唯一联合索引

CREATE UNIQUE INDEX stu\_login ON t\_login1 (stu\_name,stu\_pwd,login\_type,test);

查看索引结构

\-- 查看表中索引结构

select \* from user\_indexes where table\_name=upper('T\_LOGIN1');

然后套路就出现了，此时我们查看两个表的索引命令就变了

MYSQL:

\-- 展示表中的索引

show index from t\_login1;

SQLServer:

\-- 查询表的索引

exec sp\_helpindex  t\_login1;

Oracle查看创表语句，表名必须大写：

\-- 获取创表语句

select dbms\_metadata.get\_ddl('TABLE','T\_LOGIN1') from dual;

如果你觉得就这点不同，那么套路就比较深了，看查看表结构的语句

MYSQL:

\-- 查看表结构

DESC t\_login1;

SQLServer:

\--  查看表结构

sp\_help t\_login1;

sp\_columns t\_login1;

那么唯一联合索引的套路呢？

那就更深了，我们先来看看MYSQL的唯一联合索引

首先我们先进行插入数据

\-- 插入数据

INSERT INTO \`t\_login1\` VALUES ('彭万里', '1606390234', '123456', '0',null);  
INSERT INTO \`t\_login1\` VALUES ('马宏宇', '1606390235', '123456', '0',null);  
这两条数据我经过了两次的插入，结果如图：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hfal9jXzEyMw_size_16_color_FFFFFF_t_70][]

其中我们不然发现test这一列为空时MYSQL允许同样的数据进行插入（套路一）

然后我们再进行插入两条数据

INSERT INTO \`t\_login1\` VALUES ('彭万里', '1606390234', '123456', '0','2');  
INSERT INTO \`t\_login1\` VALUES ('彭万里', '1606390234', '123456', '0','3');

结果如图所示：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hfal9jXzEyMw_size_16_color_FFFFFF_t_70 1][]

这两个数据的test字段不同所以允许插入

那么我们再来测试两天完整的数据：

INSERT INTO \`t\_login1\` VALUES ('彭万里', '1606390234', '123456', '0','4');  
INSERT INTO \`t\_login1\` VALUES ('彭万里', '1606390234', '123456', '0','4');

这个时候套路来了，请看图：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hfal9jXzEyMw_size_16_color_FFFFFF_t_70 2][]

这个时候我们不难看出唯一联合索引发挥了作用两个一样的数据成功了一个

那么我们再来看看SQLServer，可以说这个套路就深了:

首先我们还是插入数据：

INSERT INTO t\_login1 VALUES ('彭万里', '1606390234', '123456', '0',null);  
INSERT INTO t\_login1 VALUES ('马宏宇', '1606390235', '123456', '0',null);

结果如图：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hfal9jXzEyMw_size_16_color_FFFFFF_t_70 3][]

那么我们再来看看更深的套路：

在插入两个一样的数据，并且有一列为空

INSERT INTO t\_login1 VALUES ('章汉夫', '1606390236', '123456', '0',null);  
INSERT INTO t\_login1 VALUES ('章汉夫', '1606390236', '123456', '0',null);

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hfal9jXzEyMw_size_16_color_FFFFFF_t_70 4][]

可以看出第一条数据成功的插入，那么来试试第二条：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hfal9jXzEyMw_size_16_color_FFFFFF_t_70 5][]

不难看出唯一联合索引发挥了作用，报错了，跟MYSQL完全不一样

那么我们再来试试一样的完整的数据：

INSERT INTO t\_login1 VALUES ('彭万里', '1606390234', '123456', '0','111');  
INSERT INTO t\_login1 VALUES ('彭万里', '1606390234', '123456', '0','111');

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hfal9jXzEyMw_size_16_color_FFFFFF_t_70 6][]

同样的是成功的报错了，所以这完全是个大坑。

那么oracle呢？

插入数据：

\-- 插入数据

INSERT INTO t\_login1 VALUES ('彭万里', '1606390234', '123456', '0',null);  
INSERT INTO t\_login1 VALUES ('马宏宇', '1606390235', '123456', '0',null);  
commit;

然后我们将第一条数据再进行插入一遍

\-- 插入两条一样的数据

INSERT INTO t\_login1 VALUES ('彭万里', '1606390234', '123456', '0',null);  
INSERT INTO t\_login1 VALUES ('彭万里', '1606390234', '123456', '0',null);  
commit;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hfal9jXzEyMw_size_16_color_FFFFFF_t_70 7][]

明显报错，违反唯一性，所以跟SQLServer是一样的，

那么我们再来两条一样的数据：

INSERT INTO t\_login1 VALUES ('彭万里', '1606390234', '123456', '0','111');  
INSERT INTO t\_login1 VALUES ('彭万里', '1606390234', '123456', '0','111');  
commit;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hfal9jXzEyMw_size_16_color_FFFFFF_t_70 8][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hfal9jXzEyMw_size_16_color_FFFFFF_t_70 9][]

违反唯一性条例，所以跟SQLServer一致，但是跟MYSQL不同。

总结就到这，如果写的对的话请留言点赞加关注哦，后续还会继续发布新的文章，不对的话请留言指正哦，谢谢。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hfal9jXzEyMw_size_16_color_FFFFFF_t_70]: /images/20211210/618fbfeda1784071ae09aa40d3bce0fb.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hfal9jXzEyMw_size_16_color_FFFFFF_t_70 1]: /images/20211210/ae0b946a0a9740d0919da64752e42152.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hfal9jXzEyMw_size_16_color_FFFFFF_t_70 2]: /images/20211210/aa0ab185355e49b18bd93bf6f0759ca3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hfal9jXzEyMw_size_16_color_FFFFFF_t_70 3]: /images/20211210/07315d4a13ad400bb966882328c36de2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hfal9jXzEyMw_size_16_color_FFFFFF_t_70 4]: /images/20211210/9af6f3f0a80f48fda08f85c47fed355e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hfal9jXzEyMw_size_16_color_FFFFFF_t_70 5]: /images/20211210/25bda086f2c8411e8159882f83a6d20c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hfal9jXzEyMw_size_16_color_FFFFFF_t_70 6]: /images/20211210/add042f1e3454661a6acb6d2f5381450.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hfal9jXzEyMw_size_16_color_FFFFFF_t_70 7]: /images/20211210/3cf1d05f89524f4c943316e990cb8759.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hfal9jXzEyMw_size_16_color_FFFFFF_t_70 8]: /images/20211210/3226fca3ff8f46cd8a2b762ca6b1af60.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hfal9jXzEyMw_size_16_color_FFFFFF_t_70 9]: /images/20211210/a727ef8f1f8845c3b813a0b873659fbb.png