mysql数据库（二）存储引擎、表操作、数据类型

存储引擎、表操作、数据类型

一、存储引擎

存储引擎说白了就是存储数据、建立索引、更新索引、查询数据等技术的实现方法。因为在关系数据库中数据的存储是以表的形式存储的，所以存储引擎也可以称为表类型。举个例子来讲数据库相当于文件夹，表相当于文件，那么存储引擎就是文件的类型，它用来规范文件存储写入的格式。

mysql数据库提供了多种存储引擎，用户可以根据不同的需求为数据表选择不同的存储引擎，用户也可以根据自己的需要编写自己的存储引擎。

mysql中常见的存储引擎如下：

InnoDB存储引擎（默认）：
支持事务,其设计目标主要面向联机事务处理(OLTP)的应用。其特点是行锁设计、支持外键,并支持类似 Oracle 的非锁定读,即默认读取操作不会产生锁。 从 MySQL 5.5.8 版本开始是默认的存储引擎。InnoDB 存储引擎将数据放在一个逻辑的表空间中,这个表空间就像黑盒一样由 InnoDB 存储引擎自身来管理。从 MySQL 4.1(包括 4.1)版本开始,可以将每个 InnoDB 存储引擎的 表单独存放到一个独立的 ibd 文件中。此外,InnoDB 存储引擎支持将裸设备(row disk)用于建立其表空间。InnoDB 通过使用多版本并发控制(MVCC)来获得高并发性,并且实现了 SQL 标准 的 4 种隔离级别,默认为 REPEATABLE 级别,同时使用一种称为 netx-key locking 的策略来 避免幻读(phantom)现象的产生。除此之外,InnoDB 存储引擎还提供了插入缓冲(insert buffer)、二次写(double write)、自适应哈希索引(adaptive hash index)、预读(read ahead) 等高性能和高可用的功能。对于表中数据的存储,InnoDB 存储引擎采用了聚集(clustered)的方式,每张表都是按主键的顺序进行存储的,如果没有显式地在表定义时指定主键,InnoDB 存储引擎会为每一 行生成一个 6 字节的 ROWID,并以此作为主键。 InnoDB 存储引擎具备高可用性、高性能以及高可扩展性。

MyISAM存储引擎：
不支持事务、表锁设计、支持全文索引,主要面向一些 OLAP 数 据库应用,在 MySQL 5.5.8 版本之前是默认的存储引擎(除 Windows 版本外)。数据库系统与文件系统一个很大的不同在于对事务的支持,MyISAM 存储引擎是不支持事务的。此外,MyISAM 存储引擎的 另一个与众不同的地方是,它的缓冲池只缓存(cache)索引文件,而不缓存数据文件,这与 大多数的数据库都不相同。

NDB存储引擎：
NDB 存储引擎是一个集群存储引擎,类似于 Oracle 的 RAC 集群,不过与 Oracle RAC 的 share everything 结构不同的是,其结构是 share nothing 的集群架构,因此能提供更高级别的 高可用性。NDB 存储引擎的特点是数据全部放在内存中(从 5.1 版本开始,可以将非索引数 据放在磁盘上),因此主键查找(primary key lookups)的速度极快,并且能够在线添加 NDB 数据存储节点(data node)以便线性地提高数据库性能。由此可见,NDB 存储引擎是高可用、 高性能、高可扩展性的数据库集群系统,其面向的也是 OLTP 的数据库应用类型。

Memory存储引擎：
正如其名,Memory 存储引擎中的数据都存放在内存中,数据库重启或发生崩溃,表中的数据都将消失。它非常适合于存储OLTP数据库应用中临时数据的临时表,也可以作为 OLAP 数据库应用中数据仓库的维度表。Memory 存储引擎默认使用哈希索引,而不是通常熟悉的 B+ 树索引。

Infobright存储引擎：
第三方的存储引擎。其特点是存储是按照列而非行的,因此非常 适合 OLAP 的数据库应用。

NTSE存储引擎：
网易公司开发的面向其内部使用的存储引擎。目前的版本不支持事务, 但提供压缩、行级缓存等特性。

BLACKHOLE存储引擎：
黑洞存储引擎，可以应用于主备复制中的分发主库。

#创建指定存储引擎的表
create table innodb_t1(id int,name char)engine=innodb;
#查看表的存储引擎和数据类型
show create table innodb_t1;

在配置文件my.ini设置默认的存储引擎：

[mysqld]
default-storage-engine=INNODB
innodb_file_per_table=1

二、表的详细操作

#下方创建表的代码创建了两个字段id和name
#int为id的数据类型表示整形，varchar为name的数据类型表示字符串，18为字符串长度
#not null表示该字段内容不能为空
create table t1(id int,name varchar(18) not null);

#插入数据可以使用以下两种方式
#完整插入
insert t1 values(1,'zhang');
#只插入name
insert t1(name) values('wang');

#插入字段
#after id表示在id字段后面插入字段sex，如果想插入首列可以使用first
alter table t1 add sex varchar(8) after id;

#删除字段
alter table t1 drop sex;

#修改字段数据类型
alter table t1 modify sex int;

#修改整个字段
alter table t1 change sex seex int;

#将t1表中查询到的内容复制到t2表
create table t2 select * from t1;
#复制t1表的结构到t3表
#where后面的筛选条件永远为假表示不选中任何一条记录
create table t3 select * from t1 where 1<0;

修改表名：
alter table t1 rename t2;

查询现有的表：
show tables;

查询表的数据类型：
desc t2;
#更详细的信息
show create table day;

删除表：
drop table t2;

清空表中数据(包括自增值)：
truncate table day;

清空表中数据：
delete * from day;

三、数据类型

3.1整数类型

整数类型：tinyint smallint mediumint int bigint

以int为例，int的存储大小是4个Bytes，即32个bit(2**32)
另外如果整形后面加上数字表示指定显示宽度。如int(4)表示int查询显示的数字宽度为4，而默认int的显示宽度为11位(有符号最小的负数-2147483647需要11位)，完全可以显示所有支持的数字。因此整形一般不指定显示宽度。

3.2浮点数类型

浮点数类型：float double decimal

以flaot为例，create table t1(id int,x float(m,n));
其中m表示浮点数的最大总位数，n表示最大小数位数。
float和double的m最大为255，n最大为30，而decimal的m最大为65，d最大为30。
存储精度float<double<decimal，一般而言float的存储精度就足够用了。
float和double会随着小数的增多，精度变得不准确，而decimal其内部按照字符串存储，因此即便小数增多其精度依然是准确的。

3.3 位类型

bit(M)可以用来存放多位二进制数，M范围从1~64，如果不写默认为1位。
查询位字段必须使用函数，bin、oct、hex分别表示二进制、八进制、十六进制。
create table t1(id bit);
insert t1 values(8);
select bin(id),oct(id),hex(id) from t1;

3.4 日期类型

日期类型：date time datetime timestamp year

存储格式：

YEAR：YYYY（1901）

DATE：YYYY-MM-DD（2000-01-01）

TIME：HH:MM:SS（'08:59:59'）

DATETIME：YYYY-MM-DD HH:MM:SS（2000-01-01 00:00:00）

TIMESTAMP：YYYY-MM-DD HH:MM:SS（1970-01-01 00:00:00）

注意：

• 单独插入时间时，需要以字符串的形式，按照对应的格式插入
• 插入年份时，尽量使用4位值

datetime和timestamp的区别：

• DATETIME的日期范围是1001——9999年，TIMESTAMP的时间范围是1970——2038年。
• DATETIME存储时间与时区无关，TIMESTAMP存储时间与时区有关，显示的值也依赖于时区。在mysql服务器，操作系统以及客户端连接都有时区的设置。
• DATETIME使用8字节的存储空间，TIMESTAMP的存储空间为4字节。因此，TIMESTAMP比DATETIME的空间利用率更高。
• DATETIME的默认值为null，TIMESTAMP的默认值为当前时间（CURRENT_TIMESTAMP）。

如果datetime想设置当前时间位默认值可以这样写
#default now()表示仅将创建表的时间作为默认值
#default now() on update now()表示将创建表的时间作为默认值，并且更新时间数据时如果不传入值则会更新默认值为更新数据的时间。（timestamp默认为此种设置）
create table t1(x datetime not null default now());
create table t1(x datetime not null default now() on update now());

3.5 字符串类型

字符串类型：char varchar text

char类型：

• 定长，简单粗暴，浪费空间，存取速度快。
• 字符长度范围：0-255（一个中文是一个字符，utf8编码中为3个字节）
• 存储：存储char类型的值时，会往右填充空格来满足长度。例如指定长度为10，存>10个字符则截取前十个，存<10个字符则用空格填充直到凑够10个字符存储。
• 查询：在查询时，查出的结果会自动删除尾部的空格，除非我们打开pad_char_to_full_length SQL模式（SET sql_mode = ‘PAD_CHAR_TO_FULL_LENGTH’;）

varchar类型：(varchar大多数情况下比char省空间)

• 变长，精准，节省空间，存取速度慢。
• 字符长度范围：0-65535（如果大于21845会提示用其他类型 ）
• 存储：varchar类型存储数据的真实内容，不会用空格填充。例如’ab '尾部的空格也会被存起来。varchar类型会在真实数据前加1-2Bytes的前缀，该前缀用来表示数据的长度。存储大于指定字符(n)时会截取前n个字符。
• 查询：尾部有空格会保存下来，在查询时也会正常显示包含空格在内的内容。

create table t1(name char(3));
insert t1 values(‘to’);
select * from t1 where x=‘to’;
select * from t1 where x like ‘to’;
上述代码中第一个select可以查出结果，而第二个select不能查出结果，原因是like的模糊匹配不会忽视char末尾不足的空格。如果想用like查出结果可以改为select * from t1 where x like ‘to_’;

在like的模糊匹配中_表示匹配任意字符，%表示匹配任意数量的字符。

3.6 枚举类型与集合类型

enum表示单选，只能在给定的范围内选一个值，如enum(‘男’,‘女’)
set表示多选，在给定的范围内可以选择一个或一个以上的值，如set(‘爱好1’,‘爱好2’,‘爱好3’）