MySQL之存储引擎、基本数据类型及约束条件「建议收藏」

一、存储引擎

数据库存储引擎是数据库底层软件组织，数据库管理系统（DBMS）使用数据引擎进行创建、查询、更新和删除数据。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以获得特定的功能。现在许多不同的数据库管理系统都支持多种不同的数据引擎。

因为在关系数据库中数据的存储是以表的形式存储的，所以存储引擎也可以称为表类型(Table Type，即存储和操作此表的类型)。

1.1 MySQL存储引擎

MySQL给开发者提供了查询存储引擎的功能，执行以下sql即可查询到mysql中的存储引擎：

show engines;

我的MySQL版本是5.6.50，下面是执行结果：

• InnoDB存储引擎

  InnoDB是事务型数据库的首选引擎，通过上图也看到了，InnoDB是目前MYSQL的默认事务型引擎，是目前最重要、使用最广泛的存储引擎。支持事务安全表（ACID），支持行锁定和外键。InnoDB主要特性有：

  1、InnoDB给MySQL提供了具有提交、回滚和崩溃恢复能力的事物安全（ACID兼容）存储引擎。InnoDB锁定在行级并且也在SELECT语句中提供一个类似Oracle的非锁定读。这些功能增加了多用户部署和性能。在SQL查询中，可以自由地将InnoDB类型的表和其他MySQL的表类型混合起来，甚至在同一个查询中也可以混合。

  2、InnoDB是为处理巨大数据量的最大性能设计。它的CPU效率可能是任何其他基于磁盘的关系型数据库引擎锁不能匹敌的。

  3、InnoDB存储引擎完全与MySQL服务器整合，InnoDB存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池。InnoDB将它的表和索引在一个逻辑表空间中，表空间可以包含数个文件（或原始磁盘文件）。这与MyISAM表不同，比如在MyISAM表中每个表被存放在分离的文件中。InnoDB表可以是任何尺寸，即使在文件尺寸被限制为2GB的操作系统上

  4、InnoDB支持外键完整性约束，存储表中的数据时，每张表的存储都按主键顺序存放，如果没有在表定义时指定主键，InnoDB会为每一行生成一个6字节的ROWID，并以此作为主键。

  5、InnoDB被用在众多需要高性能的大型数据库站点上。

  InnoDB不创建目录，使用InnoDB时，MySQL将在MySQL数据目录下创建一个名为ibdata1的10MB大小的自动扩展数据文件，以及两个名为ib_logfile0和ib_logfile1的5MB大小的日志文件。

  场景：由于其支持事务处理，支持外键，支持崩溃修复能力和并发控制。如果需要对事务的完整性要求比较高（比如银行），要求实现并发控制（比如售票），那选择InnoDB有很大的优势。如果需要频繁的更新、删除操作的数据库，也可以选择InnoDB，因为支持事务的提交（commit）和回滚（rollback）。

• MyISAM存储引擎

  MyISAM基于ISAM存储引擎，并对其进行扩展。它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。MyISAM拥有较高的插入、查询速度，但不支持事物和外键。

  MyISAM主要特性有：

  1、大文件（达到63位文件长度）在支持大文件的文件系统和操作系统上被支持。

  2、当把删除和更新及插入操作混合使用的时候，动态尺寸的行产生更少碎片。这要通过合并相邻被删除的块，以及若下一个块被删除，就扩展到下一块自动完成。

  3、每个MyISAM表最大索引数是64，这可以通过重新编译来改变。每个索引最大的列数是16。

  4、最大的键长度是1000字节，这也可以通过编译来改变，对于键长度超过250字节的情况，一个超过1024字节的键将被用上。

  5、BLOB和TEXT列可以被索引，支持FULLTEXT类型的索引，而InnoDB不支持这种类型的索引。

  6、NULL被允许在索引的列中，这个值占每个键的0~1个字节。

  7、所有数字键值以高字节优先被存储以允许一个更高的索引压缩。

  8、每个MyISAM类型的表都有一个AUTO_INCREMENT的内部列，当INSERT和UPDATE操作的时候该列被更新，同时AUTO_INCREMENT列将被刷新。所以说，MyISAM类型表的AUTO_INCREMENT列更新比InnoDB类型的AUTO_INCREMENT更快。

  9、可以把数据文件和索引文件放在不同目录。

  10、每个字符列可以有不同的字符集。

  11、有VARCHAR的表可以固定或动态记录长度。

  12、VARCHAR和CHAR列可以多达64KB。

  存储格式：

  1、静态表（默认）：字段都是非变长的（每个记录都是固定长度的）。存储非常迅速、容易缓存，出现故障容易恢复；占用空间通常比动态表多。

  2、动态表：占用的空间相对较少，但是频繁的更新删除记录会产生碎片，需要定期执行optimize table或myisamchk -r命令来改善性能，而且出现故障的时候恢复比较困难。

  3、压缩表：使用myisampack工具创建，占用非常小的磁盘空间。因为每个记录是被单独压缩的，所以只有非常小的访问开支。

  静态表的数据在存储的时候会按照列的宽度定义补足空格，在返回数据给应用之前去掉这些空格。如果需要保存的内容后面本来就有空格，在返回结果的时候也会被去掉。（其实是数据类型char的行为，动态表中若有这个数据类型也同样会有这个问题）

  使用MyISAM引擎创建数据库，将产生3个文件。文件的名字以表名字开始，扩展名之处文件类型：frm文件存储表定义、数据文件的扩展名为.MYD（MYData）、索引文件的扩展名时.MYI（MYIndex）。

  场景：如果表主要是用于插入新记录和读出记录，那么选择MyISAM能实现处理高效率。

• MEMORY存储引擎

  MEMORY存储引擎将表中的数据存储到内存中，为查询和引用其他表数据提供快速访问。MEMORY主要特性有：

  1、MEMORY表的每个表可以有多达32个索引，每个索引16列，以及500字节的最大键长度。

  2、MEMORY存储引擎执行HASH和BTREE缩影。

  3、可以在一个MEMORY表中有非唯一键值。

  4、MEMORY表使用一个固定的记录长度格式。

  5、MEMORY不支持BLOB或TEXT列。

  6、MEMORY支持AUTO_INCREMENT列和对可包含NULL值的列的索引。

  7、MEMORY表在所由客户端之间共享（就像其他任何非TEMPORARY表）。

  8、MEMORY表内存被存储在内存中，内存是MEMORY表和服务器在查询处理时的空闲中，创建的内部表共享。

  9、当不再需要MEMORY表的内容时，要释放被MEMORY表使用的内存，应该执行DELETE FROM或TRUNCATE TABLE，或者删除整个表（使用DROP TABLE）。

  MEMORY存储引擎默认使用哈希（HASH）索引，其速度比使用B-+Tree型要快，但也可以使用B树型索引。由于这种存储引擎所存储的数据保存在内存中，所以其保存的数据具有不稳定性，比如如果mysqld进程发生异常、重启或计算机关机等等都会造成这些数据的消失，所以这种存储引擎中的表的生命周期很短，一般只使用一次。现在mongodb、redis等NOSQL数据库愈发流行，MEMORY存储引擎的使用场景越来越少。

  场景：如果需要该数据库中一个用于查询的临时表。

• BLACKHOLE存储引擎（黑洞引擎）

  该存储引擎支持事务，而且支持mvcc的行级锁，写入这种引擎表中的任何数据都会消失，主要用于做日志记录或同步归档的中继存储，这个存储引擎除非有特别目的，否则不适合使用。

  场景：如果配置一主多从的话，多个从服务器会在主服务器上分别开启自己相对应的线程，执行 binlogdump 命令而且多个此类进程并不是共享的。为了避免因多个从服务器同时请求同样的事件而导致主机资源耗尽，可以单独建立一个伪的从服务器或者叫分发服务器。

• MERGE存储引擎

  MERGE存储引擎是一组MyISAM表的组合，这些MyISAM表结构必须完全相同，尽管其使用不如其它引擎突出，但是在某些情况下非常有用。说白了，Merge表就是几个相同MyISAM表的聚合器；Merge表中并没有数据，对Merge类型的表可以进行查询、更新、删除操作，这些操作实际上是对内部的MyISAM表进行操作。

  场景：对于服务器日志这种信息，一般常用的存储策略是将数据分成很多表，每个名称与特定的时间端相关。例如：可以用12个相同的表来存储服务器日志数据，每个表用对应各个月份的名字来命名。当有必要基于所有12个日志表的数据来生成报表，这意味着需要编写并更新多表查询，以反映这些表中的信息。与其编写这些可能出现错误的查询，不如将这些表合并起来使用一条查询，之后再删除Merge表，而不影响原来的数据，删除Merge表只是删除Merge表的定义，对内部的表没有任何影响。

• ARCHIVE存储引擎

  Archive是归档的意思，在归档之后很多的高级功能就不再支持了，仅仅支持最基本的插入和查询两种功能。在MySQL 5.5版以前，Archive是不支持索引，但是在MySQL 5.5以后的版本中就开始支持索引了。Archive拥有很好的压缩机制，它使用zlib压缩库，在记录被请求时会实时压缩，所以它经常被用来当做仓库使用。

  场景：由于高压缩和快速插入的特点Archive非常适合作为日志表的存储引擎，但是前提是不经常对该表进行查询操作。

• CSV存储引擎

  使用该引擎的MySQL数据库表会在MySQL安装目录data文件夹中的和该表所在数据库名相同的目录中生成一个.CSV文件（所以，它可以将CSV类型的文件当做表进行处理），这种文件是一种普通文本文件，每个数据行占用一个文本行。该种类型的存储引擎不支持索引，即使用该种类型的表没有主键列；另外也不允许表中的字段为null。csv的编码转换需要格外注意。

  场景：这种引擎支持从数据库中拷入/拷出CSV文件。如果从电子表格软件输出一个CSV文件，将其存放在MySQL服务器的数据目录中，服务器就能够马上读取相关的CSV文件。同样，如果写数据库到一个CSV表，外部程序也可以立刻读取它。在实现某种类型的日志记录时，CSV表作为一种数据交换格式，特别有用。

• PERFORMANCE_SCHEMA存储引擎

  该引擎主要用于收集数据库服务器性能参数。这种引擎提供以下功能：提供进程等待的详细信息，包括锁、互斥变量、文件信息；保存历史的事件汇总信息，为提供MySQL服务器性能做出详细的判断；对于新增和删除监控事件点都非常容易，并可以随意改变mysql服务器的监控周期，例如（CYCLE、MICROSECOND）。 MySQL用户是不能创建存储引擎为PERFORMANCE_SCHEMA的表。

  场景： DBA能够较明细得了解性能降低可能是由于哪些瓶颈。

• Federated存储引擎

  该存储引擎可以不同的Mysql服务器联合起来，逻辑上组成一个完整的数据库。这种存储引擎非常适合数据库分布式应用。

  Federated存储引擎可以使你在本地数据库中访问远程数据库中的数据，针对federated存储引擎表的查询会被发送到远程数据库的表上执行，本地是不存储任何数据的。

  缺点：

  1. 对本地虚拟表的结构修改，并不会修改远程表的结构

  2. truncate 命令，会清除远程表数据

  3. drop命令只会删除虚拟表，并不会删除远程表

  4. 不支持 alter table 命令

  5. select count(*), select * from limit M, N 等语句执行效率非常低，数据量较大时存在很严重的问题，但是按主键或索引列查询，则很快，如以下查询就非常慢（假设 id 为主索引）

     select id from db.tablea where id >100 limit 10 ;
     

     而以下查询就很快：

     select id from db.tablea where id >100 and id<150;
     

  6. 如果虚拟虚拟表中字段未建立索引，而实体表中为此字段建立了索引，此种情况下，性能也相当差。但是当给虚拟表建立索引后，性能恢复正常。

  7. 类似 where name like “str%” limit 1 的查询，即使在 name 列上创建了索引，也会导致查询过慢，是因为federated引擎会将所有满足条件的记录读取到本地，再进行 limit 处理。

  场景： dblink。

1.2 存储引擎的选择

二、MySQL基本数据类型

2.1 整形

不同的int类型能够存储的数字范围是不一样的。

1. 要注意是否存负数(正负号需要占一个比特位)

2. 针对手机号码只能用bigint

"""研究默认是否需要正负号"""
	create table t5(id tinyint);
	insert into t5 values(-999),(999);
"""结论:所有的int类型默认都需要正负号"""
	create table t6(id tinyint unsigned);  # 移除正负号
	insert into t6 values(-999),(999);

2.2 浮点型

float(255,30)  """总共255位 小数位占30位"""
double(255,30)  """总共255位 小数位占30位"""
decimal(65,30)  """总共65位 小数位占30位"""

"""研究三者的不同"""
create table tb7(id float(255,30));
create table tb8(id double(255,30));
create table tb9(id decimal(65,30));
insert into tb7 values(1.11111111111111111111111);
insert into tb8 values(1.11111111111111111111111);
insert into tb9 values(1.11111111111111111111111);

"""结论：三者的精确度不一样"""
float < double < decimal

一般情况下小数点后面只保留两位 所以float足矣；如果是从事高精密业务，则需要考虑更高的精确度。
ps:有时候很多看似需要用数字存储的数据，可能都是存的字符串。

2.3 字符类型

char(4):
	定长类型  最多只能存四个字符 多了报错少了自动空格填充至四个
varchar(4):
	变长类型  最多只能存四个字符 多了报错少了有几个则存几个

"""研究定长与变长特性"""
	create table t10(id int,name char(4));
	create table t11(id int,name varchar(4));
	insert into t10 values(1,"jason");
	insert into t11 values(1,"jason");

"""5.6版本以后超出范围不会报错，而是自动帮你截取并保存(此行为不合理)"""
方式1:命令修改(暂时)
	show variables like "%mode%";    """查看当前sql_mode"""
	set session    """当前窗口有效"""
	set global     """当前服务端有效"""
	set global sql_mode = "strict_trans_tables";
	"""修改完毕后退出客户端重新进入即可"""
	"""再次执行上述插入命令 会直接报错"""
方式2:修改配置文件(永久)
	"""添加如下语句"""
	[mysqld]  
	sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

"""统计某个字段数据的长度  char_length()"""
	insert into t10 values(2,"j");
	insert into t11 values(2,"t");
"""底层确实会填充  但是取出来的时候又会自动去除"""
	set global sql_mode = "strict_trans_tables,pad_char_to_full_length";
	select char_length(name) from tb10;
	select char_length(name) from tb11;

2.4 char与varchar的对比

• char

  优势：整存整取，速度快

  劣势：浪费存储空间

• varchar

  优势：节省存储空间

  劣势：存取数据的速度较于char稍慢

补充：在创建字段的时候可以加上相应的注释

create table tb12(
	id int comment "序号",
	name char(4) comment "姓名"
);

2.5 整形中括号内数字的作用

create table t13(id int(3));
insert into t13 values(4444444);  """正常显示"""

在整型中括号内的数字并不是用来限制存储的长度，而是用来控制展示的长度，需搭配约束条件（如：zerofill）一起使用。
我们以后在定义整型字段的时候，基本不需要自己添加数字，使用默认的即可。

create table t14(id int(3) zerofill);
insert into t13 values(4);  """显示004"""
"""结论:整型比较特殊 是唯一个括号内的数字不是用来限制存储长度的类型"""

2.6 枚举与集合类型

• 枚举：enum()，多选一

  create table user1(
  	id int,
  	name varchar(32),
  	gender enum("male","female","others")
  );
  
  insert into user1 values(1,"jason","男");    """报错"""
  insert into user1 values(1,"jason","male");   """正常"""
  

• 集合：set()，多选多（包含了多选一）

  create table user2(
  	id int,
  	name char(16),
  	hobby set("basketball","football","doublecolorball")
  );
      
  insert into user2 values(1,"jason","basketball"); 
  insert into user2 values(2,"kevin","football,doublecolorball");
  

2.7 日期类型

date		年月日
datetime	年月日时分秒
time		时分秒
year		年份

create table client(
	id int,
    name varchar(32),
    reg_time date,
    birth datetime,
    study_time time,
    join_time year
);
insert into client values(1,"jason","2000-11-11","2000-1-21 11:11:11","11:11:11",1995);

三、创建表

3.1 创建表的完整语法

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

• 字段名和字段类型是必须的
• 数字和约束条件是可选的，并且约束条件可以有多个，使用空格隔开即可
• 最后一个语句的结尾不要加逗号，

3.2 约束条件

约束条件相当于是在字段类型的基础之上添加的额外约束

• unsigned：让数字类型变为只允许正数

  create table tb1(id int unsigned);
  

• zerofill：数据所占位数不够时，多余的位数用数字0填充

  create table tb2(id int(4) zerofill);
  

• not null：非空；

  create table tb3(
  	id int,
  	name varchar(32) not null
  );
  

  补充：新增表数据的方式

  • 方式1：按照字段顺序一一传值

    insert into tb3 values(1,"jason");
    

  • 方式2：自定义传值顺序，也可以不传

    insert into tb3(name,id) values("jason",1);
    insert into tb3(id) values(1);
    

  默认情况下，在MySQL中不传数据，会使用关键字NULL填充，意思就是空 类似于Python中的None。

• default：默认值

  所有的字段都可以设置默认值，如果用户没有给该字段传值则使用默认值，否则使用所传的值。

  create table tb4(
  	id int default 911,
  	name varchar(16) default "jason"
  );
  

• unique：唯一值

  • 单列唯一

    create table tb5(
    	id int,
    	name varchar(32) unique
    );
    

  • 联合唯一

    create table tb5(
    	id int,
    	host varchar(32),
    	port int,
    	unique(host,port)
    );
    

• primary key：主键

  单从约束层面上来说，相当于是 not null + unique(非空且唯一)，并且在此基础上还可以加快数据的查询。

  InnoDB存储引擎规定了一张表必须有且只有一个主键，因为InnoDB是通过主键的方式来构造表的。

  如果没有设置主键，会分为两种情况，如下：

  • 情况1：没有主键和其他约束条件

    InnoDB会为每一行生成一个6字节的ROWID，并以此作为主键。

  • 情况2：没有主键但是有非空且唯一的字段

    自动将该字段升级为主键：

    create table tb6(
    	id int,
    	age int not null unique,  """从上到下选取第一个非空唯一字段作为主键"""
    	pwd int not null unique
    );
    

  结论：

  以后我们在创建表的时候一定要设置主键，并且主键字段一般都设置为表的id字段（uid、sid、pid、cid …）

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

• auto_increment：自增

  由于主键类似于数据的唯一标识，并且主键一般都是数字类型。

  我们在添加数据的时候不可能记住接下来的序号的是多少，这样很麻烦，所以使用auto_increment 可以帮助我们实现主键字段的自增。

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

  需要注意的一点是自增的特性：

  自增不会因为删除数据等操作而回退（delete from无法影响自增）

  如果想要重置需要使用truncate关键字：

  truncate 表名  """清空表数据并且重置主键值"""