MySQL存储引擎详解-优快云博客

什么是存储引擎

MySQL中的数据用各种不同的技术存储在文件（或者内存）中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术，你能够获得额外的速度或者功能，从而改善你的应用的整体功能。
总结
存储数据的的方式

# 存储引擎 -- 存储数据的方式
# 一张表
    # 数据
    # 表的结构
    # 索引(查询的时候使用的一个目录结构)
# 面试题
# 你了解mysql的存储引擎么?
# 你的项目用了什么存储引擎,为什么?
    # innodb
    # 多个用户操作的过程中对同一张表的数据同时做修改
    # innodb支持行级锁,所以我们使用了这个存储引擎
    # 为了适应程序未来的扩展性,扩展新功能的时候可能会用到...,涉及到要维护数据的完整性
    # 项目中有一两张xx xx表,之间的外键关系是什么,一张表的修改或者删除比较频繁,怕出错所以做了外键约束

mysql支持哪些存储引擎？

mysql5.6支持的存储引擎包括InnoDB、MyISAM、MEMORY、CSV、BLACKHOLE、FEDERATED、MRG_MYISAM、ARCHIVE、PERFORMANCE_SCHEMA。其中NDB和InnoDB提供事务安全表，其他存储引擎都是非事务安全表。
最常见
Innodb  Myisam  Memory

Innodb存储引擎

创建表的时候
# Innodb存储引擎（印欧db）    mysql5.6之后的默认的存储引擎
# 数据和索引存储在一起 2个文件
    # 数据索引\表结构
# 数据持久化
# 支持事务   : 为了保证数据的完整性,将多个操作变成原子性操作   : 保持数据安全
# 支持行级锁 : 修改的行少的时候使用                          : 修改数据频繁的操作
# 支持表级锁 : 批量修改多行的时候使用                        : 对于大量数据的同时修改
# 支持外键   : 约束两张表中的关联字段不能随意的添加\删除      : 能够降低数据增删改的出错率

Myisam存储引擎

# Myisam存储引擎    mysql5.5之前的默认的存储引擎
# 数据和索引不存储在一起  3个文件
    # 数据\索引\表结构
# 数据持久化
# 只支持表锁

Memory存储引擎

# Memory存储引擎
# 数据存储在内存中, 1个文件
    # 表结构
# 数据断电消失

指定存储引擎建表(方式)

mysql 5.6 默认innodb
# create table myisam_t (id int,name char(18)) engine=myisam;#修改了存储方式
# create table memory_t (id int,name char(18)) engine=memory;

创建一个完整的表

#创建语法：
create table 表名(
字段名1 类型[(宽度) 约束条件],
字段名2 类型[(宽度) 约束条件],
字段名3 类型[(宽度) 约束条件]
);

#注意：
1. 在同一张表中，字段名是不能相同
2. 宽度和约束条件可选
3. 字段名和类型是必须的
举例
create table staff_info (
id int,name varchar(50),
age int(3),
sex enum('male','female'),
phone bigint(11),
job varchar(11));

插入数据
第一种 推荐
语法
insert into 表名 (字段1,字段2,字段3,字段4,字段5,字段6) values 
(内容1,内容2,内容3,内容4,内容5,内容6);
insert into staff_info (id,name,age,sex,phone,job) values (1,'Alex',83,'female',13651054608,'IT');
第二种
insert into staff_info values 
(2,'Egon',26,'male',13304320533,'Teacher');

虽然desc命令可以查看表的定义，但是其输出的信息还不够全面，为了得到更全面的表定义信息，有时候就需要查看创建表的SQL语句，使用show create table语法。除了可以看到表定义之外，还可以看到engine（存储引擎）和charset（字符集）等信息。
查看表结构 
desc staff_info

数值类型

MySQL支持所有标准SQL数值数据类型。
这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC)，以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION)。
关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。
MySQL支持的整数类型有TINYINT、MEDIUMINT和BIGINT。下面的表显示了需要的每个整数类型的存储和范围。
对于小数的表示，MYSQL分为两种方式：浮点数和定点数。浮点数包括float(单精度)和double(双精度),而定点数只有decimal一种，在mysql中以字符串的形式存放，比浮点数更精确，适合用来表示货币等精度高的数据。
BIT数据类型保存位字段值，并且支持MyISAM、MEMORY、InnoDB和BDB表。

TINYINT	1 字节	(-128，127)	(0，255)	小整数值
SMALLINT	2 字节	(-32 768，32 767)	(0，65 535)	大整数值
MEDIUMINT	3 字节	(-8 388 608，8 388 607)	(0，16 777 215)	大整数值
INT或INTEGER	4 字节	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
BIGINT	8 字节	(-9 233 372 036 854 775 808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值
FLOAT	4 字节float(255,30)逗号前面是总共多少位后面是小数点保留的位数	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度浮点数值
DOUBLE	8 字节double(255,30)	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度浮点数值
DECIMAL	对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2double(65,30)	依赖于M和D的值	依赖于M和D的值	小数值

int与tinyint 整数

unsigned 约束无符号（只可以为正数）

# create table int_t (
#       ti tinyint,     # 有符号****(-128，127)
#       i  int,        # 有符号*****(-2147483648，2147483647)
#       tiun tinyint unsigned,  #无符号(0，255)
#       iun int unsigned #无符号(0，4294967295)
# );
# 写超过范围的 最大不过范围

float浮点数

# 写超过范围的 最大不过范围
# create table fd_t(
#         f float,      # 精度问题 小数点后5位 # *****单精度
#         d double,     # 精度更高但也不准确
#         f2 float(5,2),# 在精确位四舍五入输入12345 确是999.99 126是126.00 3.125 是3.12
#         d2 double(5,2)# 在精确位四舍五入
# )
# 单精度 与双精度
# insert into a1 values(123456);
# select * from a1;

小数值

# create table dec_t(
#     dec1 decimal,#默认取整
#     dec2 decimal(30,20)#精确后20位
# )

日期时间类型

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。
每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。
TIMESTAMP类型有专有的自动更新特性，将在后面描述。

类型	大小 (字节)	范围	格式	用途
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	年月日
TIME	3	'-838:59:59'/'838:59:59'	HH:MM:SS	时分秒
YEAR	1	1901/2155	YYYY	年份值
DATETIME	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	年月日时分秒
TIMESTAMP	4	1970-01-01 00:00:00/2038结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日凌晨 03:14:07	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

年月日时分秒

内置函数  now()
datetime  打卡时间/日志/论坛博客类的评论\文章/***** 年月日时分秒
date      员工生日/入职日期/离职日期/开班时间   *****年月日
time      上课时间/下课时间/规定上班时间    竞赛数据 时间
year 年
timestamp  2038年由于表示范围的问题,导致用的少了
    在timestamp后面加上NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP这个约束表示的范围更大,还能拥有timestamp的特点（不能为空,默认值是当前时间,在修改的时候同时更新时间（uppdet时）） 


create table time_t2(
    dt datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,   # 表示的范围更大,还能拥有timestamp的特点
    d  date,#
    t  time,
    y  year,
    ts timestamp    # 不能为空,默认值是当前时间,在修改的时候同时更新时间（uppdet时）
)

添加时间数据

年月日必须写全
第一种数字添加
insert into 表名 values(20180705112222);
第二种字符串添加
insert into 表名 values('2018-07-05 11:22:22');
insert into tine_ tdt.d.t.y) ualues (now()，now()，now(),now();

字符串类型**

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。

类型	大小	用途
CHAR	0-255字节	定长字符串
VARCHAR	0-65535 字节	变长字符串
TINYBLOB	0-255字节	不超过 255 个字符的二进制字符串
TINYTEXT	0-255字节	短文本字符串
BLOB	0-65 535字节	二进制形式的长文本数据
TEXT	0-65 535字节	长文本数据
MEDIUMBLOB	0-16 777 215字节	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215字节	中等长度文本数据
LONGBLOB	0-4 294 967 295字节	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295字节	极大文本数据

char varchar

char定长速度快占用空间大
varchar变长速度相对慢占用空间小
char    0-255    定长存储 存储速度更快 占用更多的空间  *****
    char(12)
    alex --> 'alex        ' --> 12的长度
varchar 0-65535  变长存储 存储速度慢  占用的空间小  ****
    varchar(12)
    'alex' --> 'alex4' --> 5的长度

手机号码/身份证号码 : char
用户名/密码 : 有一定范围弹性 char
评论 : varchar
# 时间和空间
    # 时间换空间
    # 空间换时间

create table ch_t(
    c char,
    c2 char(5),
    vc2 varchar(5)
)

enum和set类型

单选题,只能从有限的条件中选择
create table enum_t(
    id int,
    name char(12),
    gender enum('男','女','不详')
)

gender性别(男 女 不详)

多选题,从有限的条件中选
create table set_t(
    id int,
    name char(12),
    hobby set('抽烟','喝酒','烫头','搓脚')
)

表的完整性约束

约束条件与数据类型的宽度一样，都是可选参数，主要分为以下几种：
# NOT NULL ：非空约束，指定某列不能为空； 
# UNIQUE : 唯一约束，指定某列或者几列组合不能重复
# PRIMARY KEY ：主键，指定该列的值可以唯一地标识该列记录 第一个 非空加唯一
# FOREIGN KEY ：外键，指定该行记录从属于主表中的一条记录，主要用于参照完整性
# auto_increment:自增必须设置最少  unique自带非空只能对数字有效
    删除了某个数据 其他不变 自带记录**
    delete from t1;如果有自增id,新增的数据,仍然是以删除前的最后 id 作为起始。
    truncate table tl;数据量大，删除速度比上一条快，且直接从零开始,

# create table cs(t1 int,
#                 t2 tinyint,
#                 t3 tinyint unsigned,
#                 t4 tinyint unsigned not null,
#                 t5 decimal,
#                 t6 enum('男','女','不详'),
#                 t7 set('抽烟','喝酒','烫头','搓脚'),
#                 t8 char(18) unique,
#                 t9 int default 18
# );

unique 唯一约束又你可

null 在mysql中 不一样
在 mysql中每次添加的null 不一样  对于mysql 数据与数据间相等就是重复 所以 就算设置了 unique 唯一 也是可以重复的**
# create table t3(
#     id int unique,
#     username char(18) unique,
# )

联合唯一unique

联合唯一 ip 加端口 不可以唯一
联合唯一 不是ip不可以重复 也不是port不可以重复 而是 ip+port不可以重复
create table t2(
id inta primary key,
servername char(12) not null,
ip char(15), 
port int,
unique(ip, port)
);

default默认值第佛特

# create table t3(int unsigned not null default 18);

非空约束not null 让

# 多选题,从有限的条件中选
# create table set_t(
#     id int,
#     name char(12),
#     hobby set('抽烟','喝酒','烫头','搓脚')
# )

主键primary key 拍模为

一张表只能有一个
如果不指定主键，默认是第一个非空+唯二
联合主键primary, key(字段1,字段2)
primary key 主键 设置

非空+唯一 第一个设置会被设置成主键
# 如果一张表中没有设置primary key 主键,那么第一个设置非空+唯一的字段会被设置成主键
# 一张表中只能有一个主键
# create table t4(
#     id int not null unique,
#     username  char(18) not null unique,
#     age tinyint unsigned
# );

##

联合主键

# create table t14(
        id int,
        server_name char(12),
        ip char(15),
        port char(5),
        primary key(ip,port));

自增auto_increment in 可闷特

# 自增auto_increment 
必须设置最少  unique
auto increment自带非空
只能对  数字 有效
删除了某个数据 其他不变 自带记录**
delete from t1;如果有自增id,新增的数据,仍然是以删除前的最后 id 作为起始。
truncate table tl;数据量大，删除速度比上一条快，且直接从零开始,

不需要自己写、
只能对数字有效自带非空约束
并至少是unique的约束之后才能使用auto increment
create table t20(id int primary key auto increment,
                name char (12));
只可以这么写 看起来麻烦 其实不麻烦
insert into t20(name, job, age, post) values
(' alex',' cto', 84, 'Iuffy )，

转载于:https://www.cnblogs.com/saoqiang/p/11377477.html