03>>>存储引擎与MySQL基础数据类型
数据库存储引擎
在MySQL中，外来的数据可以以不同的方式存储。可以存储到内存里，也可以存储到硬盘里；可以单独存为一个表，也可以拆分成多个表分别存储；或者还能用其他方式进行存储。
打个比方：数据库里的数据就像是领到手的工资，工资到手之后可以选择转到支付宝里，也可以选择转到微信里，或者还可以选择汇进银行卡里。无论选择哪种转存方式都不会让工资缩水，而支付宝、微信、银行卡，就可以看成数据库的存储引擎。
如何查看存储引擎？
show engines;
可以看到在engine一列列出了多种引擎名称，接下来先挑几个主要的存储引擎来讲解。
事务、行锁、外键
在了解数据引擎之前还需要解释一下：什么是事务？什么是行锁？外键又是什么？
事务：为了保证多个数据的操作能够同时完成（要么全部完成，要么全部失败）。
比如说跨行转账，张三想用上海银行的账户通过浦发银行的ATM机给李四的工商银行账户转账。
底层逻辑很简单，就是张三账户上的钱减少，李四账户上的钱增加。然而在张三的钱已经从上海银行转入浦发银行的时候全市发生了大停电，转账流程被外力干扰中断了，那么这笔交易算是完成了吗？如果产生了损失应该怎么办？
事务便是对这样的活动上了一层保险。如果出现了程序运行到一半时被意外打断，那么就将所有的操作重置到开始之前。结合上述例子，就是将张三的钱回退到张三的上海银行账户里，只要再次转账即可。
行锁：对一行行数据加锁，同一时间只能有一个人操作，表锁同理。
行锁是抢票软件的底层机制之一。假设春运前当打开抢票软件要抢某车次的春运票时，可以看到会有一行提示显示“当前余票：XX张”之类的信息。但是当你点击抢票时，余票数可能会瞬间几十张乃至上百张。
这一现象很好解释：当你在打开抢票软件或网站时，客户端或浏览器便会访问数据库获取车票信息，并且在你的手机或电脑上显示出此时的余票数量。如果此时有人已经抢到了票，数据库内的车票信息便会立刻更新，但如果你不主动刷新，那么在你的客户端或浏览器上显示的永远只是打开软件/网站时数据库内记录的车票数量。只有等到你主动刷新或者点击抢票时，客户端/浏览器才会再次去数据库再次获取当前车票信息。
这就意味着，同一份数据可以有多个客户端/浏览器获取。行锁机制就保证了同一条数据在同时只能有一个客户端/浏览器读取。只有当上一个准许进入的用户操作完了，才会允许下一个用户进来读取数据。
换言之，行锁就像是大家在商场购物之后再收银台排队结账。只有收银员结算完了上一位的客人的金额之后才能给下一位客人结算。
与行锁相对应的，还有表锁的概念。只要理解了行锁，表锁的概念自然也就迎刃而解了。
外键：建立表与表之间的关系。外键的知识之后还会详细学习，这里就先写个理论解释，暂不赘述。
主要存储引擎
InnoDB是MySQL5.5之后的版本默认的存储引擎。支持事务、行锁、外键，数据更安全。
MyISAM系MySQL5.5之前的版本默认的存储引擎。不支持事务、外键等功能；安全性较InnoDB略低，但是存取数据的速度比InnoDB快。
memory引擎的数据直接存储在内存，读写速度快，但是断电立刻丢失。另外说一句题外话，memory在IT行业指代内存。
写入balckhole中的数据都会丢失，这个数据引擎相当于垃圾站。主要用于制作集群的部署。
存储引擎表文件
在创建表时，我们就可以指定表的存储引擎。
create table t1(id int)engine=InnoDB; # 创建表t1；定义字段id，整型数据；存储引擎为InnoDB
create table t2(id int)engine=MyISAM; # 创建表t2；定义字段id，整型数据；存储引擎为MyISAM
create table t3(id int)engine=memory; # 创建表t3；定义字段id，整型数据；存储引擎为memory
create table t4(id int)engine=blackhole; # 创建表t4；定义字段id，整型数据；存储引擎为blackhole
创建完毕后我们打开文件夹看看有没有创建成功：
可以看到，在文件夹中出现了许多同名不同后缀的文件。打头的db.opt可以忽略，这是库自带的系统文件。
这里补充一个豆知识：MySQL对于字母大小写不敏感，所以在其中书写命令时可以不必考虑大小写之分。
1.InnoDB有两个文件
t1.frm 表结构
t1.idb 表数据、表索引（相当于书的目录）
2.MyISAM有三个文件
t2.frm 表结构
t2.MYD
表数据
t2.MYI 表索引
MyISAM引擎将数据和索引分开，所以查找数据的速度更快。
3.memory有一个文件
t3.frm 表结构
没有表数据，因为直接存在内存里。
4.blackhole有一个文件
t4.frm 表结构
写入blackhole引擎的文件也不需要存入硬盘，所以也只有表结构文件。
验证存储数据特性
首先往这四张表里插入一点数据。
insert into t1 values(1); # 在表t1中插入值‘1’
insert into t2 values(1); # 在表t2中插入值‘1’
insert into t3 values(1); # 在表t3中插入值‘1’
insert into t4 values(1); # 在表t4中插入值‘1’
接下来一次查看他们。
select * from t1; # 查看t1表中所有的数据
select * from t2; # 查看t2表中所有的数据
select * from t3; # 查看t3表中所有的数据
select * from t4; # 查看t4表中所有的数据
InnoDB和MyISAM就不必多说了。
blackohole引擎不存储数据，故显示empty。
而想要验证memory的存储数据，就得重启一下MySQL。
重启MySQL相当于释放了内存空间，保存在内存上的数据自然也随之被抹去。
创建表的完整语法
创建表的完整语法是这样的：
create table 表名(
字段名1 字段类型(宽度) 约束条件,
字段名2 字段类型(宽度) 约束条件,
字段名3 字段类型(宽度) 约束条件
);
在cmd中书写命令只能一行一行地写不能换行，所以在执行大量指令时，可以在文档中分多行书写语句，然后一起复制到cmd里。
语法需要注意的特点是：
1.字段名和字段类型是必须的，宽度和约束条件是可选的。
2.约束条件可选，并且一个字段可支持多个约束条件。
3.最后结尾的字段语句不能有逗号。
字段类型
1.整型
tinyint：1字节大小，8个二进制位。在有正负符号时存储范围为(-128~127)，无正负符号时为(0~255)。
smallint：2字节，16个二进制位。
mediumint:3字节。
int或integer：4字节。
bigint：8字节。
不同整型类型能够存储的数字范围不同。
验证整型是否自带正负号
以tinyint为例，在表中插入-129和256两个数，一个超了有正负号的范围，一个超了无符号的范围：
create table t5(id tinyint); # 创建表t5，定义字段id，整型tinyint类型
insert into t5 values(-129),(256); # 在表t5中插入值-129和256
select * from t5; # 查看t5表中所有的数据
从结果来看，tinyint自带正负号。不光如此，其他int类型也是默认自带正负号的。在此情形下，int类型能存储的数据都会少一位数，正负号就占一位。
如何取消正负号
既然默认正负号占位，那么自然也就有办法取消正负号。
create table t6(id tinyint unsigned); # 创建表t6，定义字段id，整型tinyint类型
insert into t6 values(-129),(256); # 在表t6中插入值-129和256
select * from t6; # 查看t6表中所有的数据
在第一行命令中的'unsigned'是约束条件，即无符号。
不同int类型可以记录不同位数的数字，所占用的资源也各不相同。在记录诸如班级人数、月份等小范围数据时，可以使用tinyint类型；而如果需要记录电话号码时，可以选用bigint类型。
2.浮点型
float
double
decimal
和int一样，不同的浮点型存储小数的范围和精确度各不同。
float(总位数,小数位数)
double(总位数,小数位数)
decimal(总位数,小数位数)
现在来验证一下：
create table t7(id float(255,30)); # 创建表t7，定义字段id，浮点型float类型，总共255位，小数位占30位
create table t8(id double(255,30)); # 创建表t8，定义字段id，浮点型double类型，总共255位，小数位占30位
create table t9(id decimal(65,30)); # 创建表t9，定义字段id，浮点型decimal类型，总共65位，小数位占30位
insert into t7 values(1.111111111111111111111111111111); # 在表t7中插入值1.111111111111111111111111111111
insert into t8 values(1.111111111111111111111111111111); # 在表t8中插入值1.111111111111111111111111111111
insert into t9 values(1.111111111111111111111111111111); # 在表t9中插入值1.111111111111111111111111111111
select * from t7; # 查看t7表中所有的数据
select * from t8; # 查看t8表中所有的数据
select * from t9; # 查看t9表中所有的数据
float在保持了7位小数之后数字便乱了，double保持了15位小数之后也乱了，只有decimal一直保持数字的精确性。
由此可见，从精确度上讲：float < double < decimal。一般情况下，使用float足矣，如果要求精确到更多位数时，再考虑使用double或decimal。
精确度问题在很多场景下都会发生，有时候我们会采取不同的类型来存储。例如手机号全部是数字，存储时理应使用整型，但有时我们使用字符类型来存储，因为字符类型不存在精确度问题。
3.字符类型
char varchar
拼写起来差不多，但这两种数据类型在存储数据上有本质区别。
如下文所示：
char(4)
最多可以存储4个字符，超过了报错，没超过也按照4个字符存储（默认空格填充）。
varchar(4)
最多可以存储4个字符，超过了报错，没超过则按照实际的字符数存储。
原理听起来好像也差不多，结合实例就比较明晰了：
create table t10(id int,name char(4)); # 创建表t10，定义字段id，整型类型；定义字段name，字符char类型，存储4个字符
create table t11(id int,name varchar(4)); # 创建表t11，定义字段id，整型类型；定义字段name，字符varchar类型，存储4个字符
insert into t10 values(1,'jason'); # 在表t10中插入值1,'jason'
insert into t11 values(1,'jason'); # 在表t11中插入值1,'jason'
select * from t10; # 查看t10表中所有的数据
select * from t11; # 查看t11表中所有的数据
可以看见，两个表中都没有完整收入jason，而是去掉了尾巴。不过奇怪的是，这样写却没有报错。
5.6版本字符超出范围不报错是因为没有开启严格模式，实际上这对实际工作不利。5.7之后的版本默认都开启了。
关于如何严格模式的操作记载在本文后面的部分。
统计数据长度
在分析char和varchar的存储特点之前，先要额外介绍一个命令。
char_length(); # 统计字符长度
以表t10为例：
select char_length(name) from t10; # 查看表t10中name一列的字符长度
随便插入一点数据，比如说就输入个a，然后再查看字符长度。
不是说char类型在填入数据不满的情况下会自动加空格填充吗，为什么这里的字符长度还是只有1？
这个涉及到MySQL的底层优化机制。在默认情况下，用户输入的char类型数据没有填满时，计算机会自动填充空格再存储；但是当查询该数据时，计算机又会自动把空格去掉，显示出来的还是当初用户输入的数据。在这样的优化机制下，用统计字符长度的方式无法看出填充的空格来。
若想要显示自动填充的空格，就必须要使用以下命令：
set global sql_mode='strict_trans_tables,PAD_CHAR_TO_FULL_LENGTH'
char和varchar的区别
现在我们来具体分析char和varchar的优缺点。
char
好处：整存整取，速度快
坏处：浪费存储空间
varchar
好处：节省存储空间
坏处：存取速度较char慢
两种字符类型的区别，还是要从硬盘存储数据的原理说起。
假如现在文件中保存了四个人名：joe、victor、simon、frank，宽度为6。
在char类型的保存方式很好理解：'joe ','victor','simon ','frank '。
然而在varchar类型里，就不一样了：'joevicortsimonfrank'。
char写入和读取都很规整，六个字符六个字符的取值就没问题了。而varchar则不做区分，一股脑写在一串，在存储时确实很方便，可现在要读取文件，那就很头疼了。
应该如何取值？
为了避免这种窘境，varchar的解决方法是这样的："1byte+joe1byte+vict1byte+simo1byte+fran"，在每一段数据面前加上1个字节，用来计算这段数据的长度。
于是在读取varchar时，就是先读取1个字节并计算该段数据的长度，然后再读取这段存储数据。
因为存储和读取都需要计算，消耗更多时间，所以效率上varchar比char慢。
了解了两者的区别，那么我们应该是用哪种字符类型呢？
在明确知道需存储数据大致长度的情况下（例如记录电话号码），当然可以直接用char类型。但如果不清楚存储数据的长度（例如记录少数民族或者外国人姓名），那么用varchar更好。
结论：两者都有应用场景，需要结合实际情况分析决定。
4.时间类型
date 年月日
time 时分秒
Datetime 年月日时分秒
Year 年
时间类型大部分都可以见名知意。针对时间数据很多时候都可以自动获取。
5.枚举与集合类型
enum 枚举（多选一）
set 集合（多选多，包含多选一）
枚举类型
枚举类型在设计阶段就需要提前定义好有哪些选项可供用户选择。
例如现在要制作一张学生登记表。
create table t12( # 创建表t12
id int, # 定义字段id，整型类型
name varchar(16), # 定义字段name，字符varchar类型，存储16个字符
gender enum('male','female','others') # 定义字段gender，枚举类型，内含male、female、others三项
);
然后插入学生信息。
insert into t12 values(1,'joe','男');
insert into t12 values(2,'victor','male');
select * from t12;
标新立异的joe报错，最终没有输入表里去；中规中矩的victor顺利被输入了表中。再度印证了最开始的定义：枚举类型插入数据的时候只能够插入提前规定好的数据。
集合类型
现在要建立一张兴趣统计表。
create table t13( # 创建表t12
id int, # 定义字段id，整型类型
name char(16), # 定义字段name，字符char类型，存储16个字符
hooby set('basketball','football','tennis') # 定义字段hobby，集合类型，内含basketball、football、tennis三项
);
接下来输入数据。
insert into t13 values(1,'joe','read');
insert into t13 values(2,'victor','basketball');
insert into t13 values(3,'simon','football,tennis');
select * from t13;
不喜欢运动的joe还是没有被输入进表里，而单选和多选的另两位则顺利输入进了表中。
6.文本类型
text
可以插入大段文本，现在目前学习阶段接触到的还是以小容量文本为主，所以这个仅做了解就行。
严格模式
查看严格模式？
首先，如何查看严格模式？
show variables like '%mode%'; # 模糊查询各种模式
现在没有引擎开启了严格模式。所以要想办法开启它。
开启严格模式
set global sql_mode = 'strict_trans_tables';
不过即便这样还是没有开启严格模式。因为这需要写入配置文件之中。不过步骤也简单，退出客户端之后重新进入即可。
当sql_mode一项显示为STRICT_TRANS_TABLES时，就表示开启严格模式了。
那么这时候再插入数据会怎么样呢？
insert into t10 values(2,'kevin'); # 在表t10中插入值2,'kevin'
insert into t11 values(2,'kevin'); # 在表t11中插入值2,'kevin'
错误提示信息：name一列的数据过长。
宽度
提问：int(4)和char(4)后面的括号有什么区别？
针对数字类型，宽度并不是用来限制存储长度，而是用来表示展示长度。
来一张表格演示一下：
create table t14(id tinyint(1),name char(16)); # 创建表t14，定义字段id，整型tinyint类型，展示宽度1；定义字段name，字符char类型，存储16个字符
insert into t14 values(123,'joe'); # 在表t14中插入值123，joe
select * from t14; # 显示表t14中的所有数据
可以发现，在id一列还是展示了完整的序数。
实际上，整型类型定义多少位宽度都没有影响，只是在数字够不到最大位数时自动填充0。例如在宽度为3的一列中插入数字1，最终会显示为“001”。
以后再定义数字的时候无需手动添加宽度。
约束条件
其实在之前讲述整型类型是已经讲到过一个约束条件了，这里再来补充几个。
1.unsigned 无符号
具体描述可看本文《字段类型1.整型》一段。
2.zerofill 0填充
create table t15(id int(8) zerofill,name char(16)); # 创建表t15，定义字段id，整型int类型，显示宽度8位，0填充；定义字段name，字符char类型，存储16个字符
insert into t15 values(1,'joe'); # 在表t15中插入值1，joe
select * from t15; # 显示表t15中的所有数据
序号这里变成了7个0+1个1。
3.not null # 不能为空（使用频率很高）
了解not null之前可以学习一下插入数据的另外一种方式：
insert into 表名(字段1，字段2) values(值1，值2); 按照指定的字段顺序传入值
insert into t15(id) values(2); # 在表t15中id一列插入值2
select * from t15; # 显示表t15中的所有数据
序号顺利传入进去了，但是name列显示NULL，即空的意思。
在许多调查问卷或者注册流程中都会有一些选项显示为必填，怎样才能形成这样的强制要求呢？
create table t16(id int,name varchar(16) not null); # 创建表t16；定义字段id，整型int类型，定义字段name，字符varchar类型，存储16个字符,不能为空
desc t16; # 显示表t18的结构
insert into t16(id) values(1); # 在表t16中id一列插入值1
Null列为NO的意思是不能为空。下方的错误提示意为name列的数据不能为空。
即便如此，还是有空子可以钻，在不能为空的字段里依然可以输入空字符串使其填入列表中，空字符串不被认为是null。
4.default 默认值
通常在社交网站注册一个新账号，个人信息里会有一些系统帮使用者填写好的默认信息。在技术上实现这一功能并不困难。
create table t17(id int,name varchar(16) default '匿名用户'); # 创建表t17；定义字段id，整型int类型；定义字段name，字符varchar类型，存储16个字符，默认填写匿名用户
desc t17; # 显示表t17的结构
insert into t17(id) values(1); # 在表t17中插入值，id为1；name未输入
insert into t17(id,name) values(2,'joe'); # 在表t17中插入值，id为2，name为joe
select * from t17; # 显示表t17中的所有数据
5.unique # 唯一
接着来创建一张用户表，要求名字不能重复。应该怎么做？
create table t18(id int,name varchar(16) unique); # 创建表t18，；定义字段id，整型int类型；定义字段name，字符varchar类型，存储16个字符，不能重复
insert into t18 values(1,'joe'); # 在表t18中插入值，id为1；name为joe
insert into t18 values(1,'joe'); # 在表t18中插入值，id为1；name为joe
insert into t18 values(2,'victor'); # 在表t18中插入值，id为2；name为victor
insert into t18 values(2,'simon'); # 在表t18中插入值，id为2；name为simon
select * from t18; # 显示表t18中的所有数据
1,joe自然没问题，加了unique之后joe就不能重复添加了。
2,victor也没问题。
2,simon却也可以，因为unique只限定了名字不能重复，却没有限定序号不能重复。
现在示范的形式叫做单列唯一，只要规定的一列不重复，其他列重复是没问题的；我们也可以马上联想到如何做到多列唯一。
接下来做一张记录电脑的ip和端口的表格。
create table t19(id int,host int,port int,unique(host,port)); # 创建表t19，；定义字段id，整型int类型；定义字段host，整型int类型；定义字段port，整型int类型；host与port的组合不能重复
desc t19; # 显示表t19的结构
insert into t19 values(1,127,3306); # 在表t19中插入值：1,127,3306
insert into t19 values(2,127,3307); # 在表t19中插入值：1,127,3307
insert into t19 values(3,128,3306); # 在表t19中插入值：1,128,3306
select * from t19; # 显示表t19中的所有数据
insert into t19 values(3,128,3306); # 在表t19中插入值：1,128,3306
查看表的结构时，出现在Key一列的MUL是多重组合的意思。
接下来往表内插入数据，可以看到只要host和port的组合不重复，就可以存入表内。而如果host和port重复了，就说明已经存储过了，不必重复劳动。
附录 当default不能用时
在撰写本文档时遇到了一个问题，那就是在使用default语句是报错，语句的代号是ERROR 1067。这里写出在网上看到的一种解决方法，根据其步骤也确实解决了笔者的情况，故此写下以备不时之需。不过并不排除本文记载的该方案无法解决各位看官遇到的问题的可能。
1.先看看数据库的字符编码。看看是不是utf8码。
show create database 库;
2.如果不是，改成utf8试试。
alter database 库 character set utf8;
3.接下来应该可行了。
此方法只能解决编码问题造成的报错，若非ERROR 1067而是其他原因产生的报错，还需参考其他方法解决。
浙公网安备 33010602011771号