sqlserver 组合索引只用一部分 sqlserver 联合索引

转载

laojean 2024-02-28 10:59:22

文章标签 sqlserver 组合索引只用一部分 MySQL SQLServer 索引唯一 文章分类 数据仓库大数据

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);

这两条数据我经过了两次的插入，结果如图：

sqlserver 组合索引只用一部分 sqlserver 联合索引_SQLServer

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

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

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

结果如图所示：

sqlserver 组合索引只用一部分 sqlserver 联合索引_索引_02

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

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

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

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

sqlserver 组合索引只用一部分 sqlserver 联合索引_索引_03

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

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

首先我们还是插入数据：

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

结果如图：

sqlserver 组合索引只用一部分 sqlserver 联合索引_SQLServer_04

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

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

INSERT INTO t_login1 VALUES ('章汉夫', '1606390236', '123456', '0',null);
 INSERT INTO t_login1 VALUES ('章汉夫', '1606390236', '123456', '0',null);

sqlserver 组合索引只用一部分 sqlserver 联合索引_SQLServer_05

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

sqlserver 组合索引只用一部分 sqlserver 联合索引_sqlserver 组合索引只用一部分_06

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

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

INSERT INTO t_login1 VALUES ('彭万里', '1606390234', '123456', '0','111');
 INSERT INTO t_login1 VALUES ('彭万里', '1606390234', '123456', '0','111');

sqlserver 组合索引只用一部分 sqlserver 联合索引_MySQL_07

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

那么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;

sqlserver 组合索引只用一部分 sqlserver 联合索引_MySQL_08

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

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

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

sqlserver 组合索引只用一部分 sqlserver 联合索引_索引_09