存储引擎

什么是存储引擎

MySQL中的数据用各种不同的技术存储在文件(或者内存)中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术，你能够获得额外的速度或者功能，从而改善你的应用的整体功能。 存储引擎说白了就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。
例如，如果你在研究大量的临时数据，你也许需要使用内存存储引擎。内存存储引擎能够在内存中存储所有的表格数据。又或者，你也许需要一个支持事务处理的数据库(以确保事务处理不成功时数据的回退能力)。

存储引擎有哪些

MyISAM

5.1之前版本MySQL默认的存储引擎，存取数据的速度快 但是功能很少，安全性较低

InnoDB

5.1之后版本MySQL默认的存储引擎，有诸多功能 安全性较高 存取速度没有MyISAM快，支持事务、行锁、外键等

BlackHole

黑洞存储引擎，所有插入的数据并不会保存，BlackHole 引擎表永远保持为空，写入的任何数据都会消失

Memory

以内存作为数据存取，速度快但是断电立刻丢失

查看存储引擎

show engines;

自定义选择存储引擎

可以在my.cnf 配置文件中设置你需要的存储引擎，这个参数放在 [mysqld] 这个字段下面的 default_storage_engine 参数值，例如下面配置的片段

[mysqld]
default_storage_engine=CSV

也可以在创建表的时候，对表设置存储引擎，例如：

CREATE TABLE user (id int)ENGINE=InnoDB;

在创建用户表 user 的时候，SQL语句最后 ENGINE=InnoDB 就是设置这张表存储引擎为 InnoDB

创建表的完整语法

create table 表名(
    字段名1 字段类型(数字) 约束条件,
    字段名2 字段类型(数字) 约束条件,
  字段名3 字段类型(数字) 约束条件
);

• 字段名和字段类型是必须存在的，数字和约束条件是可选的
• 约束条件可以写多个空格隔开eg：字段名 字段类型(数字) 约束条件1 约束条件2 约束条件3
• 最后一行字段结尾不能加逗号

字段类型之整型

上述整型的区别在于从上往下能够存储的数字范围越来越大，而且默认是有符号的，若只需存储无符号值，可增加 unsigned 属性。

int(M)中的 M 代表最大显示宽度，并不是说 int(1) 就不能存储数值10了，不管设定了显示宽度是多少个字符，int 都是占用4个字节，即int(5)和int(10)可存储的范围一样。

存储字节越小，占用空间越小。所以本着最小化存储的原则，尽量选择合适的整型，例如：存储一些状态值或人的年龄可以用 tinyint ；主键列，无负数，建议使用 int unsigned 或者 bigint unsigned，预估字段数字取值会超过 42 亿，使用 bigint 类型

create table t1(id tinyint);
insert into t1 values(-129),(256);
# 如果是在5.6版本不会报错 会自动处理成最大范围(没有意义)
      set global sql_mode = 'STRICT_TRANS_TABLES';
    # 退出客户端 重新登录即可
# 如果是在5.7及以上版本 则会直接报错(更加合理)
# 验证(结论)发现所有的整型都默认带有正负号 如何修改不带正负号(约束条件)
create table t2(id tinyint unsigned);

字段类型之浮点型

定点型字段类型有 DECIMAL 一个，主要用于存储有精度要求的小数。

对于声明语法 DECIMAL(M,D) ，自变量的值范围如下：

• M是最大位数(精度)，范围是1到65。可不指定，默认值是10。

• D是小数点右边的位数(小数位)。范围是0到30，并且不能大于M，可不指定，默认值是0。

上述浮点型从上往下精确度越来越高

虽然三者精确度有差距 但是具体用哪个应该结合实际情况，比如正常业务 使用float足够，如果是高精尖 可以使用decimal

create table t3(id decimal(6,3));
-- 表示能够存储有6位数字和3位小数的任何值，因此可以存储在id列中的值的范围从-999.999到999.999

字段类型之字符类型

其中 char 和 varchar 是最常用到的。char 类型是定长的，MySQL 总是根据定义的字符串长度分配足够的空间。当保存 char 值时，在它们的右边填充空格以达到指定的长度，当检索到 char 值时，尾部的空格被删除掉。varchar 类型用于存储可变长字符串，存储时，如果字符没有达到定义的位数，也不会在后面补空格。

char(M) 与 varchar(M) 中的的 M 表示保存的最大字符数，单个字母、数字、中文等都是占用一个字符。char 适合存储很短的字符串，或者所有值都接近同一个长度。例如，char 非常适合存储密码的 MD5 值，因为这是一个定长的值。对于字符串很长或者所要存储的字符串长短不一的情况，varchar 更加合适。

我们在定义字段最大长度时应该按需分配，提前做好预估，能使用 varchar 类型就尽量不使用 text 类型。除非有存储长文本数据需求时，再考虑使用 text 类型。

BLOB 类型主要用于存储二进制大对象，例如可以存储图片，音视频等文件。日常很少用到，有存储二进制字符串时可以考虑使用。

create table t4(name char(4));
create table t5(name varchar(4));
insert into t4 values('kevin');  # 报错，数据太长
insert into t5 values('kevin');  # 报错，数据太长
# 如果是5.6版本并且没有修改严格模式 则会自动截取四个字符(不合理)
# 临时修改 
  set global sql_mode = 'STRICT_TRANS_TABLES';
  # 退出客户端 重新登录即可
# 永久修改
  # 修改my.ini配置文件
  sql_mode = 'STRICT_TRANS_TABLES,ONLY_FULL_GROUP_BY'
  # 重启服务端之后永久生效

数字的含义

字段类型括号内的数字大部分情况下是用来限制存储的长度，但是在整型中并不是用来限制长度 而是用来控制展示长度

create table t6(id int(3));
insert into t6 values(1234);  -- 不会报错
create table t7(id int(3) zerofill);  -- zerofill 不够用0填充
insert into t7 values(1);  -- 001
insert into t7 values(1234); -- 有几位就展示几位

补充：以后涉及到整型字段 都无需自己定义长度 直接使用自带的即可，而针对其他类型的字段 则需要自己添加数字

字段类型之日期类型

涉及到日期和时间字段类型选择时，根据存储需求选择合适的类型即可

create table t8(
  id int, 
  name varchar(32),
  reg_time datetime,
  birth date,study_time 
  time,join_time year
);
insert into t8 values(1,'kevin','2000-11-11','2001-11-11','11:11:11','2022');

mysql> select * from t8;
+------+-------+---------------------+------------+------------+-----------+
| id   | name  | reg_time            | birth      | study_time | join_time |
+------+-------+---------------------+------------+------------+-----------+
|    1 | kevin | 2000-11-11 00:00:00 | 2001-11-11 | 11:11:11   |      2022 |
+------+-------+---------------------+------------+------------+-----------+
1 row in set (0.00 sec)

字段类型之枚举与集合

枚举（多选一）

关键字enum

在插入数据的时候，只能填写提前定义好的数值

create table t9(
  id int,
  name varchar(32),
  gender enum('male','feamale','others')
);
insert into t9 values(1,'kevin','男');             -- 报错
insert into t9 values(2,'tony','male');           -- 成功（多选一）
insert into t9 values(3,'jason','male,feamale');  -- 报错

集合（多选多）

关键字set

在插入数据的时候，也可以多选一

create table t10(
  id int,
  name varchar(32),
  hobby set('篮球','足球','排球','保龄球','台球')
);
insert into t10 values(1,'kevin','橄榄球');      -- 报错
insert into t10 values(2,'tony','足球');         -- 成功（多选一）
insert into t10 values(3,'jason','台球,保龄球');  -- 成功（多选多）

约束条件

约束条件是指在字段类型之上，添加额外的限制，例如：在字段类型之整型中 `id int insigned`就是规定了字段类型`int`的`id`字段只能存整数， 约束条件`unsigned`指的是在整数基础之上还必须是正数

无需正负号

关键字unsigned

create table t11(id int unsigned);

零填充

关键字zerofill

create table t12(id int(12) zerofill);

非空

关键字not null

create table t13(id int not null);

默认值

关键字default

create table t14(id int default 886);

唯一值

关键字unique

create table t15(id int unique);  -- 单列唯一值，对应数据不能重复
create table t16(host varchar(32),port int,unique(host,port)); -- 多列唯一值，对应数据组合不能重复

主键

关键字primary key

单从约束层面上而言主键相当于`not null` + `unique`（非空且唯一）主键的作用是加快查找数据的速度，如果创建表的时候没有设置主键也没有其他的键，那么InnoDB会采用一个隐藏的字段作为表的主键（隐藏就意味着而无法使用，即无法加快数据查询）如果没有主键但是有非空且唯一的字段，那么会自动升级成主键（从上往下的第一个）

create table t17(id int primary key);

create table t18(
  tid int,
  pid int not null unique,
  cid int not null unique
);

也可以有联合主键(多个字段组合 本质还是一个主键)

create table t19(
    id int,
  name varchar(32),
  pwd int,
  primary key(id,pwd)
);

自增

关键字auto_increment

专门配合主键一起使用，用户以后在添加数据的时候就不需要自己记忆主键值

create table t20(
    id int primary key auto_increment,
  name varchar(32)
);

自增特性

• 自增不会随着数据的删除而回退
• 用delete from 表名删除数据无法重置主键，需要 用truncate 表名 删除数据并重置主键值

约束条件之外键

关键字foreign keys

外键用来建立主表与从表的关联关系，为两个表的数据建立连接，约束两个表中数据的一致性和完整性

主表删除某条记录时，从表中与之对应的记录也必须有相应的改变。一个表可以有一个或多个外键，外键可以为空值，若不为空值，则每一个外键的值必须等于主表中主键的某个值。

一对多关系

在一对多关系中，一个表中的一个记录可以关联另一个表中的一个或多个记录

以员工和部门为例子，一个员工不可以对应多个部门，而一个部门可以对应多个员工

-- 在创建表字段的时候可以用 comment 添加注释
create table dep(
  id int primary key auto_increment comment '编号',
  dep_name varchar(32) comment '部门名称',
  dep_desc varchar(32) comment '部门描述'
);

create table emp(
    id int primary key auto_increment comment '编号',
  name varchar(32) comment '姓名',
  age int comment '年龄',
  dep_id int comment '部门编号',
  foreign key(dep_id) references dep(id)
  on update cascade  -- 级联更新
  on delete cascade  -- 级联删除
);

使用外键foreign key的注意事项

• 一对多关系，外键字段建在”多”的一方（员工表）
• 如果表中有外键字段，那么先编写普通字段，最后再考虑外键字段
• 创建表的时候应该先创建被关联表（没有外键字段的表）
• 插入数据的时候 应该先插入被关联表（没有外键字段的表）
• 外键字段填入的值只能是被关联表中已经存在的值

多对多关系

一个表中的多个记录与另一个表中的多个记录相关联时即产生多对多关系

以图书与作者为例子，一本书可以对应多名作者，而一名作者也可以对应多本书，这就形成了“多对多”关系。但是会有一个问题就是给两个表都添加了外键，导致无法创建表。这就需要第三张表用来专门存储之间关系

create table book(
    id int primary key auto_increment,
  book_name varchar(32)
);
create table author(
    id int primary key auto_increment,
  author_name varchar(32),
  gender enum('male','female')
);
create table book_author(
    id int primary key auto_increment,
  author_id int,
  book_id int,
  foreign key(author_id) references author(id)
  on update cascade -- 级联更新
  on delete cascade, -- 级联更新
  foreign key(book_id) references book(id)
  on update cascade -- 级联更新
  on delete cascade -- 级联更新
);

补充：

针对多对多表关系，两张基表内的数据没有在第三张表内绑定关系的情况下随意新增修改删除

一对一关系

在一对一关系中，表中的一个记录仅关联另一个表中的一个记录，即产生一对一关系

以用户和用户详细信息为例子，用户的唯一值是“ID”，用来区别不同用户，但对于每个用户都会有自己基本的信息，通过这个“ID”可以查看更多的详细信息

create table User(
    id int primary key auto_increment,
  gender enum('male','female'),
  user_detail_id int unique,
  foreign key(user_detail_id) references UserDetail(id)
  on update cascade  -- 级联更新
  on delete cascade  -- 级联删除
);
create table UserDetail(
    id int primary key auto_increment,
  phone bigint,
  age int,
  email varchar(32)
);