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 |
+----+------+
外键约束
作用
'''
foreign key 就是表与表之间的某种约定的关系，由于这种关系的存在，我们能够让表与表之间的数据，更加的完整， 关联性更强。
关于完整性， 关联性我们举个例子
例：
有二张表，一张是用户表，一张是订单表
1. 如果我删除了用户表里的用户，那么订单表里面与这个用户有关的数据，就成了无头数据了，不完整了。
2. 如果我在订单表里面，随便插入了一条数据，这个订单在用户表里面，没有与之对应的用户。这样数据也不完整了。
3. 如果有外键的话，就方便多了，可以不让用户删除数据，或者删除用户的话，通过外键同样删除订单表里面的数据，这样也能让数据完整。
'''
参数解释
- foreign key 当前表的字段
- references 外部表名
- on update cascade 是级联更新的意思
- on update cascade 是级联更新的意思
约束条件
- 确保参照的表和字段存在
- 组成外键的字段被索引。
- 必须使用 ENGINE 指定存储引擎为： innodb。
- 外键字段和关联字段，数据类型必须一致。
外键创建
mysql> create table orderinfo(o_id int(11) auto_increment, u_id int(11) default '0', 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=innodb;
mysql> create table userinfo(id int(11) not null auto_increment, name varchar(50) not null default '', sex int(1) not null default '0', primary key(id))ENGINE=innodb;
mysql> insert into userinfo(name,sex)values('HA',1),('LB',2),('HPC',1);
Query OK, 3 rows affected (0.00 sec)
mysql> insert into orderinfo (u_id,username,money)values(1,'HA',234),(2,'LB',146),(3,'HPC',256);
Query OK, 3 rows affected (0.00 sec)
数据测试
mysql> select * from orderinfo;
+------+------+----------+-------+
| o_id | u_id | username | money |
+------+------+----------+-------+
| 1 | 1 | HA | 234 |
| 2 | 2 | LB | 146 |
| 3 | 3 | HPC | 256 |
+------+------+----------+-------+
mysql> select id,name,sex,money,o_id from userinfo,orderinfo where id=u_id;
+----+------+-----+-------+------+
| id | name | sex | money | o_id |
+----+------+-----+-------+------+
| 1 | HA | 1 | 234 | 1 |
| 2 | LB | 2 | 146 | 2 |
| 3 | HPC | 1 | 256 | 3 |
+----+------+-----+-------+------+
# 级联删除
mysql> delete from userinfo where id=1; #删除数据
Query OK, 1 row affected (0.00 sec)
mysql> select * from orderinfo； # 查看级联表
+------+------+----------+-------+
| o_id | u_id | username | money |
+------+------+----------+-------+
| 2 | 2 | LB | 146 |
| 3 | 3 | HPC | 256 |
+------+------+----------+-------+
# 级联更新
mysql> update userinfo set id=6 where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from userinfo;
+----+------+-----+
| id | name | sex |
+----+------+-----+
| 3 | HPC | 1 |
| 6 | LB | 2 |
+----+------+-----+
# 测试数据完整性
insert into orderinfo (u_id,username,money)values(5,'Find',346); # 报错此时无u_id=5的用户
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test1`.`orderinfo`, CONSTRAINT `orderinfo_ibfk_1` FOREIGN KEY (`u_id`) REFERENCES `userinfo` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)
mysql> insert into userinfo values(5,'Find',1);
Query OK, 1 row affected (0.04 sec)
mysql> insert into orderinfo (u_id,username,money)values(5,'Find',346);
Query OK, 1 row affected (0.00 sec)
mysql> select * from orderinfo;
+------+------+----------+-------+
| o_id | u_id | username | money |
+------+------+----------+-------+
| 2 | 6 | LB | 146 |
| 3 | 3 | HPC | 256 |
| 7 | 5 | Find | 346 |
+------+------+----------+-------+
索引
作用
- 索引是一种特殊的文件（ 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 | | # 添加成功
+-------+--------------+------+-----+---------+-------+
唯一索引
作用
- 其与普通索引类似
- 但是在索引列所在的字段中插入的数据值必须唯一
- 唯一性索引允许有空值允许为null
唯一索引创建
mysql> create table test6(id int auto_increment primary key,name varchar(254), unique index(name));
mysql> desc test6;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(254) | YES | UNI | NULL | |
+-------+--------------+------+-----+---------+----------------+
mysql> insert into test6 values(1,'SR');
mysql> insert into test6 values(2,null);
mysql> insert into test6 values(3,'SR'); # 报错含有重复值
ERROR 1062 (23000): Duplicate entry 'SR' for key 'name'
唯一索引添加与删除
mysql> alter table test6 drop index name;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc test6;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(254) | YES | | NULL | |
+-------+--------------+------+-----+---------+----------------+
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;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(254) | YES | | NULL | |
+-------+--------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> insert into test7 values('','SR'); # 数据不能为空
ERROR 1366 (HY000): Incorrect integer value: '' for column 'id' at row 1
mysql>
mysql> insert into test7 values(1,'SR');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test7 values(1,'SR');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
主键索引删除与添加
# 不建议当有生产数据的时候在来创建主键索引 因为此时有数据 无法保证数据唯一 如果不唯一则无法创建主键索引
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('deny','allow') not null, primary key (host,port));
mysql> desc test8;
+--------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------------------+------+-----+---------+-------+
| host | varchar(15) | NO | PRI | NULL | |
| port | smallint(4) | NO | PRI | NULL | |
| access | enum('deny','allow') | NO | | NULL | |
+--------+----------------------+------+-----+---------+-------+
mysql> insert into test8 values('10.96.52.46',22,'deny');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test8 values('10.96.52.46',21,'allow');
Query OK, 1 row affected (0.05 sec)
mysql> insert into test8 values('10.96.52.46',21,'allow'); # 数据重复
ERROR 1062 (23000): Duplicate entry '10.96.52.46-21' for key 'PRIMARY'
全文索引
作用
- 全文索引（ 也称全文检索） 是目前搜索引擎使用的一种关键技术。它能够利用「分词技术「等多种算法智能分析出文本文字中关键字词的频率及重要性，然后按照一定的算法规则智能地筛选出我们索结果。
- MySQL 在数据量较大的情况下，高并发连接的情况下。select 语句 where bName like '%网%'使用% _ 通配符，不通过索引，直接全表扫描。ABSUWU LIKE ‘%U_U’数据库压力大。
- MySQL 的解决方案：全文索引： 3.2 开始支持全文索引。 无法正确支持中文。从 MySQL 5.7.6 开始 MySQL 内置了 ngram 全文检索插件，用来支持中文分词
创建
# 全文索引字段必须是varchar text
# 存储引擎必须是myisam
# mysql自带的全文搜索引擎只能作用于myisam存储引擎
mysql> create table test10 (id int,article text, fulltext key(article)) engine=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)
