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,&＃39;SR&＃39;);

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

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

ERROR 1048 (23000): Column &＃39;name&＃39; cannot be null

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

&＃43;----&＃43;------&＃43;

| id | name |

&＃43;----&＃43;------&＃43;

| 1 | SR |

| 1 | |

&＃43;----&＃43;------&＃43;

mysql> select * from test1 where name &＃xff01;&＃61; &＃39;&＃39;; # 当查询条件不为空的时候 数据为空不能被查询出来

&＃43;----&＃43;------&＃43;

| id | name |

&＃43;----&＃43;------&＃43;

| 1 | SR |

&＃43;----&＃43;------&＃43;

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

上述现象扩展

""和null的区别

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

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

null和not null效率

not null效率高于null

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

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

内核优化

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

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

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

default

作用

为指定字段设置默认值

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

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

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

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

mysql> select * from test2;

&＃43;----&＃43;------&＃43;

| id | name |

&＃43;----&＃43;------&＃43;

| 1 | SR | # 使用默认值

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

&＃43;----&＃43;------&＃43;

PS:

1:如果时间字段&＃xff0c;默认为当前时间 &＃xff0c;插入 0 时&＃xff0c;默认为当前时间。

2:如果是 enum 类型&＃xff0c;默认为第一个元素

auto_increment

作用

自动增长

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

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

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

mysql> desc test3;

&＃43;-------&＃43;---------&＃43;------&＃43;-----&＃43;---------&＃43;----------------&＃43;

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

&＃43;-------&＃43;---------&＃43;------&＃43;-----&＃43;---------&＃43;----------------&＃43;

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

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

&＃43;-------&＃43;---------&＃43;------&＃43;-----&＃43;---------&＃43;----------------&＃43;

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

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

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

mysql> select * from test3;

&＃43;----&＃43;------&＃43;

| id | test |

&＃43;----&＃43;------&＃43;

| 1 | 1 |

| 2 | 2 |

&＃43;----&＃43;------&＃43;

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);# 从最大值&＃43;1

mysql> select * from test3;

&＃43;----&＃43;------&＃43;

| id | test |

&＃43;----&＃43;------&＃43;

| 1 | 1 |

| 2 | 2 |

| 3 | 9 |

| 4 | 9 |

| 9 | 9 |

| 10 | 9 |

&＃43;----&＃43;------&＃43;

外键约束

作用

&＃39;&＃39;&＃39;

foreign key 就是表与表之间的某种约定的关系&＃xff0c;由于这种关系的存在&＃xff0c;我们能够让表与表之间的数据&＃xff0c;更加的完整&＃xff0c; 关联性更强。

关于完整性&＃xff0c; 关联性我们举个例子

例&＃xff1a;

有二张表&＃xff0c;一张是用户表&＃xff0c;一张是订单表

1. 如果我删除了用户表里的用户&＃xff0c;那么订单表里面与这个用户有关的数据&＃xff0c;就成了无头数据了&＃xff0c;不完整了。

2. 如果我在订单表里面&＃xff0c;随便插入了一条数据&＃xff0c;这个订单在用户表里面&＃xff0c;没有与之对应的用户。这样数据也不完整了。

3. 如果有外键的话&＃xff0c;就方便多了&＃xff0c;可以不让用户删除数据&＃xff0c;或者删除用户的话&＃xff0c;通过外键同样删除订单表里面的数据&＃xff0c;这样也能让数据完整。

&＃39;&＃39;&＃39;

参数解释

foreign key 当前表的字段

references 外部表名

on update cascade 是级联更新的意思

on update cascade 是级联更新的意思

约束条件

确保参照的表和字段存在

组成外键的字段被索引。

必须使用 ENGINE 指定存储引擎为&＃xff1a; innodb。

外键字段和关联字段&＃xff0c;数据类型必须一致。

外键创建

mysql> create table orderinfo(o_id int(11) auto_increment, u_id int(11) default &＃39;0&＃39;, username varchar(50), money int(11), primary key(o_id), index(u_id), foreign key order_f_key(u_id) references userinfo(id) on delete cascade on update cascade) ENGINE&＃61;innodb;

mysql> create table userinfo(id int(11) not null auto_increment, name varchar(50) not null default &＃39;&＃39;, sex int(1) not null default &＃39;0&＃39;, primary key(id))ENGINE&＃61;innodb;

mysql> insert into userinfo(name,sex)values(&＃39;HA&＃39;,1),(&＃39;LB&＃39;,2),(&＃39;HPC&＃39;,1);

Query OK, 3 rows affected (0.00 sec)

mysql> insert into orderinfo (u_id,username,money)values(1,&＃39;HA&＃39;,234),(2,&＃39;LB&＃39;,146),(3,&＃39;HPC&＃39;,256);

Query OK, 3 rows affected (0.00 sec)

数据测试

mysql> select * from orderinfo;

&＃43;------&＃43;------&＃43;----------&＃43;-------&＃43;

| o_id | u_id | username | money |

&＃43;------&＃43;------&＃43;----------&＃43;-------&＃43;

| 1 | 1 | HA | 234 |

| 2 | 2 | LB | 146 |

| 3 | 3 | HPC | 256 |

&＃43;------&＃43;------&＃43;----------&＃43;-------&＃43;

mysql> select id,name,sex,money,o_id from userinfo,orderinfo where id&＃61;u_id;

&＃43;----&＃43;------&＃43;-----&＃43;-------&＃43;------&＃43;

| id | name | sex | money | o_id |

&＃43;----&＃43;------&＃43;-----&＃43;-------&＃43;------&＃43;

| 1 | HA | 1 | 234 | 1 |

| 2 | LB | 2 | 146 | 2 |

| 3 | HPC | 1 | 256 | 3 |

&＃43;----&＃43;------&＃43;-----&＃43;-------&＃43;------&＃43;

# 级联删除

mysql> delete from userinfo where id&＃61;1; #删除数据

Query OK, 1 row affected (0.00 sec)

mysql> select * from orderinfo&＃xff1b; # 查看级联表

&＃43;------&＃43;------&＃43;----------&＃43;-------&＃43;

| o_id | u_id | username | money |

&＃43;------&＃43;------&＃43;----------&＃43;-------&＃43;

| 2 | 2 | LB | 146 |

| 3 | 3 | HPC | 256 |

&＃43;------&＃43;------&＃43;----------&＃43;-------&＃43;

# 级联更新

mysql> update userinfo set id&＃61;6 where id&＃61;2;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from userinfo;

&＃43;----&＃43;------&＃43;-----&＃43;

| id | name | sex |

&＃43;----&＃43;------&＃43;-----&＃43;

| 3 | HPC | 1 |

| 6 | LB | 2 |

&＃43;----&＃43;------&＃43;-----&＃43;

# 测试数据完整性

insert into orderinfo (u_id,username,money)values(5,&＃39;Find&＃39;,346); # 报错此时无u_id&＃61;5的用户

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (&＃96;test1&＃96;.&＃96;orderinfo&＃96;, CONSTRAINT &＃96;orderinfo_ibfk_1&＃96; FOREIGN KEY (&＃96;u_id&＃96;) REFERENCES &＃96;userinfo&＃96; (&＃96;id&＃96;) ON DELETE CASCADE ON UPDATE CASCADE)

mysql> insert into userinfo values(5,&＃39;Find&＃39;,1);

Query OK, 1 row affected (0.04 sec)

mysql> insert into orderinfo (u_id,username,money)values(5,&＃39;Find&＃39;,346);

Query OK, 1 row affected (0.00 sec)

mysql> select * from orderinfo;

&＃43;------&＃43;------&＃43;----------&＃43;-------&＃43;

| o_id | u_id | username | money |

&＃43;------&＃43;------&＃43;----------&＃43;-------&＃43;

| 2 | 6 | LB | 146 |

| 3 | 3 | HPC | 256 |

| 7 | 5 | Find | 346 |

&＃43;------&＃43;------&＃43;----------&＃43;-------&＃43;

索引

作用

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

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

优缺点

优点

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

缺点

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

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

普通索引

作用

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

其不具备唯一性

普通索引创建

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

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

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

mysql> desc test4;

&＃43;-------&＃43;--------------&＃43;------&＃43;-----&＃43;---------&＃43;-------&＃43;

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

&＃43;-------&＃43;--------------&＃43;------&＃43;-----&＃43;---------&＃43;-------&＃43;

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

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

&＃43;-------&＃43;--------------&＃43;------&＃43;-----&＃43;---------&＃43;-------&＃43;

mysql> show create table test4;

&＃43;-------&＃43;--------------------------------------------------------------------------------------------------------------------------------------------------&＃43;

| Table | Create Table |

&＃43;-------&＃43;--------------------------------------------------------------------------------------------------------------------------------------------------&＃43;

| test4 | CREATE TABLE &＃96;test4&＃96; (

&＃96;id&＃96; int(11) DEFAULT NULL,

&＃96;name&＃96; varchar(254) DEFAULT NULL,

KEY &＃96;id&＃96; (&＃96;id&＃96;) # 索引名称

) ENGINE&＃61;InnoDB DEFAULT CHARSET&＃61;latin1 |

&＃43;-------&＃43;--------------------------------------------------------------------------------------------------------------------------------------------------&＃43;

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

mysql> show create table test5;

&＃43;-------&＃43;------------------------------------------------------------------------------------------------------------------------------------------------------------&＃43;

| Table | Create Table |

&＃43;-------&＃43;------------------------------------------------------------------------------------------------------------------------------------------------------------&＃43;

| test5 | CREATE TABLE &＃96;test5&＃96; (

&＃96;id&＃96; int(11) DEFAULT NULL,

&＃96;name&＃96; varchar(254) DEFAULT NULL,

KEY &＃96;name_index&＃96; (&＃96;name&＃96;) # 指定索引名称

) ENGINE&＃61;InnoDB DEFAULT CHARSET&＃61;latin1 |

&＃43;-------&＃43;------------------------------------------------------------------------------------------------------------------------------------------------------------&＃43;

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

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

索引删除与添加

mysql> alter table test5 drop key name_index;

mysql> desc test5;

&＃43;-------&＃43;--------------&＃43;------&＃43;-----&＃43;---------&＃43;-------&＃43;

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

&＃43;-------&＃43;--------------&＃43;------&＃43;-----&＃43;---------&＃43;-------&＃43;

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

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

&＃43;-------&＃43;--------------&＃43;------&＃43;-----&＃43;---------&＃43;-------&＃43;

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

mysql> desc test5;

&＃43;-------&＃43;--------------&＃43;------&＃43;-----&＃43;---------&＃43;-------&＃43;

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

&＃43;-------&＃43;--------------&＃43;------&＃43;-----&＃43;---------&＃43;-------&＃43;

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

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

&＃43;-------&＃43;--------------&＃43;------&＃43;-----&＃43;---------&＃43;-------&＃43;

唯一索引

作用

其与普通索引类似

但是在索引列所在的字段中插入的数据值必须唯一

唯一性索引允许有空值允许为null

唯一索引创建

mysql> create table test6(id int auto_increment primary key,name varchar(254), unique index(name));

mysql> desc test6;

&＃43;-------&＃43;--------------&＃43;------&＃43;-----&＃43;---------&＃43;----------------&＃43;

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

&＃43;-------&＃43;--------------&＃43;------&＃43;-----&＃43;---------&＃43;----------------&＃43;

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

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

&＃43;-------&＃43;--------------&＃43;------&＃43;-----&＃43;---------&＃43;----------------&＃43;

mysql> insert into test6 values(1,&＃39;SR&＃39;);

mysql> insert into test6 values(2,null);

mysql> insert into test6 values(3,&＃39;SR&＃39;); # 报错含有重复值

ERROR 1062 (23000): Duplicate entry &＃39;SR&＃39; for key &＃39;name&＃39;

唯一索引添加与删除

mysql> alter table test6 drop index name;

Query OK, 0 rows affected (0.05 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc test6;

&＃43;-------&＃43;--------------&＃43;------&＃43;-----&＃43;---------&＃43;----------------&＃43;

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

&＃43;-------&＃43;--------------&＃43;------&＃43;-----&＃43;---------&＃43;----------------&＃43;

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

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

&＃43;-------&＃43;--------------&＃43;------&＃43;-----&＃43;---------&＃43;----------------&＃43;

2 rows in set (0.00 sec)

mysql> alter table test6 add unique index(name);

Query OK, 0 rows affected (0.06 sec)

Records: 0 Duplicates: 0 Warnings: 0

主键索引

作用

使用主键索引查询速度最快

主键索引所在的字段数据必须唯一

主键索引不许为空

主键索引创建

mysql> create table test7(id int auto_increment not null, name varchar(254),primary key (id));

Query OK, 0 rows affected (0.06 sec)

mysql> desc test7;

&＃43;-------&＃43;--------------&＃43;------&＃43;-----&＃43;---------&＃43;----------------&＃43;

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

&＃43;-------&＃43;--------------&＃43;------&＃43;-----&＃43;---------&＃43;----------------&＃43;

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

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

&＃43;-------&＃43;--------------&＃43;------&＃43;-----&＃43;---------&＃43;----------------&＃43;

2 rows in set (0.00 sec)

mysql> insert into test7 values(&＃39;&＃39;,&＃39;SR&＃39;); # 数据不能为空

ERROR 1366 (HY000): Incorrect integer value: &＃39;&＃39; for column &＃39;id&＃39; at row 1

mysql>

mysql> insert into test7 values(1,&＃39;SR&＃39;);

Query OK, 1 row affected (0.00 sec)

mysql> insert into test7 values(1,&＃39;SR&＃39;);

ERROR 1062 (23000): Duplicate entry &＃39;1&＃39; for key &＃39;PRIMARY&＃39;

主键索引删除与添加

# 不建议当有生产数据的时候在来创建主键索引 因为此时有数据 无法保证数据唯一 如果不唯一则无法创建主键索引

alter table test7 drop primary key ; # 此时报错的原因因为自增长

ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

mysql> alter table test7 change id id int not null; # 去掉auto_increment

mysql> alter table test7 drop primary key ; # 删除主键

mysql> alter table test7 change id id int not null primary key auto_increment;

复合索引

作用

索引可以包含一个、两个或更多个列

两个或更多个列上的索引被称作复合索引

复合索引创建

mysql> create table test8( host varchar(15) not null ,port smallint(4) not null ,access enum(&＃39;deny&＃39;,&＃39;allow&＃39;) not null, primary key (host,port));

mysql> desc test8;

&＃43;--------&＃43;----------------------&＃43;------&＃43;-----&＃43;---------&＃43;-------&＃43;

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

&＃43;--------&＃43;----------------------&＃43;------&＃43;-----&＃43;---------&＃43;-------&＃43;

| host | varchar(15) | NO | PRI | NULL | |

| port | smallint(4) | NO | PRI | NULL | |

| access | enum(&＃39;deny&＃39;,&＃39;allow&＃39;) | NO | | NULL | |

&＃43;--------&＃43;----------------------&＃43;------&＃43;-----&＃43;---------&＃43;-------&＃43;

mysql> insert into test8 values(&＃39;10.96.52.46&＃39;,22,&＃39;deny&＃39;);

Query OK, 1 row affected (0.00 sec)

mysql> insert into test8 values(&＃39;10.96.52.46&＃39;,21,&＃39;allow&＃39;);

Query OK, 1 row affected (0.05 sec)

mysql> insert into test8 values(&＃39;10.96.52.46&＃39;,21,&＃39;allow&＃39;); # 数据重复

ERROR 1062 (23000): Duplicate entry &＃39;10.96.52.46-21&＃39; for key &＃39;PRIMARY&＃39;

全文索引

作用

全文索引( 也称全文检索) 是目前搜索引擎使用的一种关键技术。它能够利用「分词技术「等多种算法智能分析出文本文字中关键字词的频率及重要性&＃xff0c;然后按照一定的算法规则智能地筛选出我们索结果。

MySQL 在数据量较大的情况下&＃xff0c;高并发连接的情况下。select 语句 where bName like &＃39;%网%&＃39;使用% _ 通配符&＃xff0c;不通过索引&＃xff0c;直接全表扫描。ABSUWU LIKE ‘%U_U’数据库压力大。

MySQL 的解决方案&＃xff1a;全文索引&＃xff1a; 3.2 开始支持全文索引。 无法正确支持中文。从 MySQL 5.7.6 开始 MySQL 内置了 ngram 全文检索插件&＃xff0c;用来支持中文分词

创建

# 全文索引字段必须是varchar text

# 存储引擎必须是myisam

# mysql自带的全文搜索引擎只能作用于myisam存储引擎

mysql> create table test10 (id int,article text, fulltext key(article)) engine&＃61;myisam;

Query OK, 0 rows affected (0.01 sec)

mysql> show index from test10\G;

*************************** 1. row ***************************

Table: test10

Non_unique: 1

Key_name: article

Seq_in_index: 1

Column_name: article

Collation: NULL

Cardinality: NULL

Sub_part: NULL

Packed: NULL

Null: YES

Index_type: FULLTEXT # 全文引擎

Comment:

Index_comment:

1 row in set (0.00 sec)