mysql 索引与约束_MySQL之索引与约束条件

MySQL之索引与约束条件

字段约束

作用

顾名思义就是给字段加以限制

其保证数据库的完整性与一致性

通过约束条件防止数据库产生一些不必要的数据 保证数据库的正确性 相容性 安全性

null和not null

mysql> create database test1;

mysql> use test1;

mysql> create table test1(id int not null,name varchar(254) not null); # 设置约束条件不能为空

mysql> insert into test1 values (1,'SR');

mysql> insert into test1 values (1,''); # 如果以空字符可以正常插入数据

mysql> insert into test1 values (1,null); # 为null则报错

ERROR 1048 (23000): Column 'name' cannot be null

mysql> select * from test1 where name is not null; # 当查询条件为not null的时候即使数据为空也可以被查询出来

+----+------+

| id | name |

+----+------+

| 1 | SR |

| 1 | |

+----+------+

mysql> select * from test1 where name ！= ''; # 当查询条件不为空的时候 数据为空不能被查询出来

+----+------+

| id | name |

+----+------+

| 1 | SR |

+----+------+

PS:当约束条件为not null的时候可以设置为"" 但是不能插入null

上述现象扩展

""和null的区别

null在数据库中是占用空间的 为空值不占用空间

对于MyISAM表中null需要一个额外的为 四舍五入到最接近的字节

null和not null效率

not null效率高于null

对于null字段来说其本身占用空间在数据查询的时候会参与字段比较

null字段不会被索引 如果参与索引的时候效率会下降很多

内核优化

MySQL 难以优化引用可空列查询，它会使索引、索引统计和值更加复杂。

可空列需要更多的存储空间，还需要一个额外字节作为判断是否为 NULL 的标志位 “需要 MySQL内部进行特殊处理”

可空列被索引后，每条记录都需要一个额外的字节，还能导致 MyISAM 中固定大小的索引变成可变大小的索引

default

作用

为指定字段设置默认值

如果在插入数据的时候给默认值赋值则使用赋值的数据

mysql> create table test2(id int not null,name varchar(255) not null default 'SR');

mysql> insert into test2(id)values(1); # 不给name字段插入数据

mysql> insert into test2(id,name)values(2,'MZ'); # 手动给name字段添加数据

mysql> select * from test2;

+----+------+

| id | name |

+----+------+

| 1 | SR | # 使用默认值

| 2 | MZ | # 使用指定的值

+----+------+

PS:

1:如果时间字段，默认为当前时间 ，插入 0 时，默认为当前时间。

2:如果是 enum 类型，默认为第一个元素

auto_increment

作用

自动增长

其作用数据类型只能为整形

每次插入一条数据的时候都会在该字段的最大值+1

mysql> create table test3 (id int not null auto_increment primary key ,test int);

mysql> desc test3;

+-------+---------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+-------+---------+------+-----+---------+----------------+

| id | int(11) | NO | PRI | NULL | auto_increment |

| test | int(11) | YES | | NULL | |

+-------+---------+------+-----+---------+----------------+

mysql> insert into test3(test) values(1);

mysql> insert into test3(test) values(2);

# id字段自动从1开始插入数据并且自动递增

mysql> select * from test3;

+----+------+

| id | test |

+----+------+

| 1 | 1 |

| 2 | 2 |

+----+------+

mysql> insert into test3(id,test) values(4,9);

mysql> insert into test3(id,test) values(9,9);# 跳级插入

mysql> insert into test3(test) values(9);# 从最大值+1

mysql> select * from test3;

+----+------+

| id | test |

+----+------+

| 1 | 1 |

| 2 | 2 |

| 3 | 9 |

| 4 | 9 |

| 9 | 9 |

| 10 | 9 |

+----+------+

truncat

作用

清除表中的数据

将auto_increment字段中的数据清零 再次插入数据从1开始

mysql> delete from test3; # 清空上述test3表

mysql> select * from test3;

Empty set (0.00 sec)

mysql> insert into test3(test)values(12); # 插入数据

# 使用delete发现此时id字段接着保留上次最大值 在自增1

mysql> select * from test3;

+----+------+

| id | test |

+----+------+

| 11 | 12 |

+----+------+

mysql> truncate test3; # 清空表

mysql> select * from test3;

Empty set (0.01 sec)

mysql> insert into test3(test)values(12);

mysql> select * from test3; # 此时id字段初始值为1

+----+------+

| id | test |

+----+------+

| 1 | 12 |

+----+------+

索引

作用

索引是一种特殊的文件( InnoDB 数据表上的索引是表空间的一个组成部分)

它们包含着对数据表里所有记录的引用指针

[索引详解]https://www.cnblogs.com/SR-Program/p/12008958.html

优缺点

优点

加快查询速度 增加查询效率

缺点

索引需要单独的文件来存放索引 如果索引量比较大占用存储空间

索引与数据库中的数据相互对应 如果数据量较大 当有数据增加的时候 索引需要同步更新 降低效率

普通索引

作用

最基础的索引 只是用来加快查询速度

其不具备唯一性

普通索引创建

# 使用index或者key指定索引字段

# 索引名称可以添加也可以省略 如果省略默认以字段名字作为索引名称

mysql> create table test4(id int,name varchar(254),index(id));

mysql> desc test4;

+-------+--------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+--------------+------+-----+---------+-------+

| id | int(11) | YES | MUL | NULL | |

| name | varchar(254) | YES | | NULL | |

+-------+--------------+------+-----+---------+-------+

mysql> show create table test4;

+-------+--------------------------------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table |

+-------+--------------------------------------------------------------------------------------------------------------------------------------------------+

| test4 | CREATE TABLE `test4` (

`id` int(11) DEFAULT NULL,

`name` varchar(254) DEFAULT NULL,

KEY `id` (`id`) # 索引名称

) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

+-------+--------------------------------------------------------------------------------------------------------------------------------------------------+

mysql> create table test4(id int,name varchar(254),index name_index(name)); # 指定索引名称index_name

mysql> show create table test5;

+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table |

+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------+

| test5 | CREATE TABLE `test5` (

`id` int(11) DEFAULT NULL,

`name` varchar(254) DEFAULT NULL,

KEY `name_index` (`name`) # 指定索引名称

) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------+

key为MUL表示普通索引 该列值可以重复

该列是一个非唯一索引的前导列(第一列)或者是一个唯一性索引的组成部分但是可以含有空值 NULL

索引删除与添加

mysql> alter table test5 drop key name_index;

mysql> desc test5;

+-------+--------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+--------------+------+-----+---------+-------+

| id | int(11) | YES | | NULL | |

| name | varchar(254) | YES | | NULL | | # 无索引字段

+-------+--------------+------+-----+---------+-------+

mysql> alter table test5 add key name_index(name);

mysql> desc test5;

+-------+--------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+--------------+------+-----+---------+-------+

| id | int(11) | YES | | NULL | |

| name | varchar(254) | YES | MUL | NULL | | # 添加成功

+-------+--------------+------+-----+---------+-------+