MySQL 之存储引擎与数据类型

一．存储引擎场景

1.InnoDB

用于事务处理应用程序，支持外键和行级锁。如果应用对事物的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包括很多更新和删除操作，那么InnoDB存储引擎是比较合适的。InnoDB除了有效的降低由删除和更新导致的锁定，还可以确保事务的完整提交和回滚，对于类似计费系统或者财务系统等对数据准确要求性比较高的系统都是合适的选择。

 

2.MyISAM

如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不高，那么可以选择这个存储引擎。

 

3.Memory

将所有的数据保存在内存中，在需要快速定位记录和其他类似数据的环境下，可以提供极快的访问。Memory的缺陷是对表的大小有限制，虽然数据库因为异常终止的话数据可以正常恢复，但是一旦数据库关闭，存储在内存中的数据都会丢失。

#查看当前的默认存储引擎:

show variables like "default_storage_engine";

#更改表的存储引擎

alter table t1 engine = innodb;

方法2:

#my.ini文件

[mysqld]

default_storage_engine=INNODB

4.BLACKHOLE

# BLACKHOLE使用场景:

如图:用户访问网站时,先通过负载均衡系统

来进行用户的分发,底层会用一系列类似于取模的算法,给用户进行分配,目的让服务器所抗住的并发均衡;

有的用户需要查询,有的用户需要修改或者添加,但是整体查询次数会远远大于增删改的次数,

那么这时搭建主从数据库.主数据库负责写入,从数据库负责读取,

为了让主从数据库,数据同步,中间在加上一台服务器,用于同步,那么这台服务器所用的存储引擎就是blackhole

专门帮助主数据库进行binlog日志的生成和分发.从数据库拿到binlog日志后,开始同步自己的数据

这样的好处,主数据库只专注于写入,不用考虑分发其他数据等操作;减少服务器的压力;

中间blackhole的mysql服务器不需要生产或者消费数据,只是单纯的过滤生产binlog日志,所以选择blackhole存储引擎.

5.了解不同引擎创建数据库产生的文件结构

create table innodb1(id int , name char(4)) engine=innodb;

show create table innodb1;

innodb1.frm 表结构

innodb1.ibd 表数据

 

create table myisam1(id int ,name char(4)) engine=myisam;

myisam1.frm 表结构

myisam1.MYD 表数据

myisam1.MYI 表索引 从上到下,从左到右依次查询,如果设置该字段是索引,会单独拿出来一个文件进行存储查询,速度更快.

 

create table memory1(id int ,name char(4)) engine=memory;

memory1.frm 表结构

 

create table blackhole1(id int ,name char(4)) engine=blackhole;

blackhole1.frm 表结构

insert into blackhole1 values(1,"s");

 

在配置文件中指定默认的存储引擎：

/etc/my.cnf

[mysqld]

default-storage-engine=INNODB

innodb_file_per_table=1

 

二.数据类型

1.number类型(数字类型)

(1)整型

tinyint 1个字节 有符号(-128 ~ 127) 无符号(0~255) 小整数值

int     4个字节 有符号(约-21亿 ~ 21亿 最大长度10位) 无符号(0~42亿) 大整数值,精确度更高

create table t1(id int unsigned,sex tinyint);

insert into t1 values(4200000000,128); #22003): Out of range value for column 'sex' at row 1

insert into t1 values(4200000000,127);

(2)浮点型

float(255,30)  单精度

double(255,30) 双精度

decimal(65,30) 一般用于保存金钱,是使用字符串的形式来保存小数,

 

create table t2(f1 float(5,2) , f2 double(5,2),f3 decimal(5,2));

insert into t2 values(1.234,1.234,1.234);

 

# decimal 默认保留整数 double默认保留的小数位数更多,更精确.

create table t3(f1 float, f2 double,f3 decimal);

insert into t3 values(1.2355555555,1.2355555555,1.2355555555);

number类型详细见下列表格：

2.str类型(字符串类型)

char(11)     定长:固定开启字符长度是11的空间 (手机号,身份证号),速度快

varchar(11)  变长:最大开辟字符长度是11的空间 (文章评论 5~255个字数之间.),速度慢

text         这种类型应用在文章,小说中.

create table t4(c char(11),v varchar(11),t text);

insert into t4 values('1122',"dfdffd","34234234243");

select concat(c,v) from t4;

select concat(c,",",v) from t4;

详细见表格：

3.时间类型

date YYYY-MM-DD 年月日 (出生日期)

time HH:MM:SS   时分秒 (竞赛时间)

year YYYY       年分值 (红酒82年拉菲 82年矿泉水)

datetime YYYY-MM-DD HH:MM:SS 年月日时分秒 (登录时间,下单时间)

 

create table t5(d date ,t time ,y year ,dt datetime);

insert into t5 values(now(),now(),now(),now());

insert into t5 values("2020-1-1","23:23:23","2038","2099-1-1 23:23:23");

 

timestamp YYYYMMDDHHMMSS 自动更新时间戳,不需要你手动写入(修改表的时候,自动更新,到时候可以看到最后一次表更新时间;)

 

create table t6(dt datetime,ts timestamp);

insert into t6 values(null,null);

insert into t6 values(20190103121212,20190103121212);

insert into t6 values(20190103121212,20380118121212);

如果timestamp这个效果没有实现,需要将explicit_defaults_for_timestamp=true这句配置删除,重启服务,然后重新建一个数据库和数据表才能体现效果.

详细见表格：

4.枚举&集合

enum 和 set 的数据必须从其中挑选,没有的话报错;

enum         枚举 从一组数据中选一个 (一般性别上)

set          集合 从一组数据中选多个 ,自动去重

详细见表格：

例：

create table stu1(

id int primary key auto_increment,

name char(5),

sex enum('male','female'), #enum 代表枚举类型

hobbies set('eat','play','study','coding') #set 代表集合类型

);

insert into stu1(name,sex,hobbies) values('haiyan','none','dsfdg');

select * from stu1;  #如果设置了sex是枚举类型，就的从设定的里面选其中的一个存

insert into stu1(name,sex,hobbies) values('haiyan','female','play,study');

select * from stu1;  #如果设置了hobbies是集合类型，就得从设定的里面选其中一个或者多个值来存