Mysql 数据类型和存储引擎-CSDN博客

本文链接：https://blog.csdn.net/qq_33393542/article/details/79660626

数据类型

对于整形数据，MySql还支持在类型名称后面的小括号设置位数，默认设置为int(11),配合zerofill,
当数字位数不够的时候，用字符‘0’填充

alter table t1 modify id1 int zerofill

对于小数，MySql 分为两种，浮点数和定点数。浮点数包括float和double，而定点数只有decimal，定点数在Mysql内部是以字符串形式存放，比浮点数更精确，适合用于货币等精度高的数据

浮点数和定点数可以用类型名称加（M，D) M是几位，D是位于小数点后面几位。

日期类型

DATE来表示年月日
DATETIME来表示年月日时分秒
TIME来表示时分秒

当前系统时间，通常用TIMESTAMP来表示

TIMESTAMP
创建一个字段为TIMESTAMP类型，系统自动创建了默认值为CURRENT_TIMESTAMP（系统日期）。同时MySql规定TIMESTAMP类型字段一列只能有一个默认值current_timestamp。如果修改会报错.

TIMESTAMP 另一个重要特点是与时区有关。当插入时间时，先转换为本地时区后存放，而从数据库取出时，同样会将日期转换为本地时区后显示，这样两个时区的用户看到同一个时区可能就不一样

查看当前时区
show variables like 'time_zone';
修改时区
set time_zone='+9.00';

DATETIME插入的格式

YYYY-MM-DD HH:MM:SS 或YY-MM-DD HH:MM:SS 的字符串允许任何标点符号用来做时间部分的间隔符
如92@12@31 11^30^45

YYYYMMDDHHMMSS 或YYMMDDHHMMSS的格式没有间隔符的字符串

字符串类型

CHAR和VARCHAR类型
两者的主要区别是存储方式不同：CHAR列的长度固定为创建表时声明的长度，长度可以为0-255；而VARCHAR列中的值是可变长度。同时在检索的时候，CHAR列删除尾部的空格，而VARCHAR保留空格，由于CHAR是固定长度，所以它的处理速度别VARCHAR快很多，但是其缺点是浪费内存，在使用中VARCHAR被更多的使用

create table vc (v varchar(4),c char(4))
insert into vc values('ab  ','ab  ');
selelct length(v),length(c) from vc
//4,2

枚举

create table t(gender enum('m','f'));
insert into t values('m'),('2'),('f'),('null')
//m,f,f,null

set类型
set类型可以一次选取多个成员

create table t2 (col set('a','b','c','d'));
INSERT into t2 VALUE ('a,b'),('a,d,a'),('a,b'),('a,c'),('a');

对于（a,d，a)这个包含重复成员的集合只取一次 结果为’a,d'

运算符

DIV==/==除法获取商
MOD==%==除法获取余数

=和<=>区别

不能用于null比较，后者可以

between 使用格式 a between min and max 等价于 a>=min and a<=max

in的使用格式 a in(value1,value2...);

like 使用格式如 a like %123%,当字符串含有123则返回1 否则返回0

REGEXP 使用格式 str REGEXP str_pat 当str字符串中含有str_pat 相匹配的字符串，则返回1

MySql引擎
MySql支持的存储引擎包括MyISAM、InnoDB、BDB、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED等，其中InnoDB和BDB提供事务安全表，用户可以选择不同的数据存储引擎来提高应用的效率

创建表如果不指定存储引擎，系统默认使用默认存储引擎，MySql5.5之前的默认引擎是MyISAM，5.5之后改为InnoDB。如果要修改默认的存储引擎，可以在参数文件中设置default-table-type.

show ENGINES //查看的确支持的存储引擎

//通过增加engine关键字设置新建表的储存引擎z
create table ai(i bigint(20) not null auto_increment,primary key(i))engine=innodb default charset=gbk;

通过alter　来修改一个表的存储引擎
ALTER TABLE ai ENGINE =MyISAM;

MyISAM
MyISAM 不支持事务、也不不支持外键，其优点是速度快，对事务完整性没有要求。以SELECT和INSERT为主的应用基本上都就可以使用这个表

InnoDB
InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比MyISAM的存储引擎，InnoDB写的处理效率差一些，并且会占用更多的磁盘空间以保留数据和索引。

MEMORY
memory 存储引擎使用存在于内存中的内容来创建表，每个MEMORY表实际对应一个磁盘文件，格式是.fm,MEMORY表的访问非常快，因为它的数据是放在内存中，并且默认使用HASH索引，但是一旦服务关闭，表中的数据就会丢失

alter table t2 engine=memory;
show TABLE status like 't2'
给memory表创建索引。可以指定hash索引还是btree索引
create index mem_hash using hash on tab_memory(city_id);

在启动MySql服务的时候使用–init-file选项，把INSERT INTO … SELECT或LOAD DATA INFILE这样的语句放入这个文件中，就可以在服务启动时从持久稳固的数据源装载表
服务器需要足够的内存来维持同一时间使用的MEMORY表，当不需要MEMORY表的内容，要释放MEMORY表的内存，执行DELETE FROM或 TRUNCATE TABLE 或者是DROP TABLE
每个MEMORY表中可以放置的数据量的大小，受max_heap_table_size系统变量的约束，初始值是16mb，可以根据需要加大、
MEMORY类型的存储引擎主要用在那些内容变化不平凡的表，或作为统计操作的中间结果表，便于高效的对中间结果进行分析并得到最终的统计结果。

TokuDB
TokuDB是第三方的存储引擎，是一个高性能、支持事务处理的MySql和MariaDB的存储引擎，具有高扩展性、高压缩、高效率的写入性能，支持大多数在线的DDL操作
TokuDB 特别适用的场景

日志数据，因为日志数据通常插入频繁且储存量大
历史数据，通常不会有在写的操作，可以利用TokuDB的高压缩特性进行存储
在线DDL频繁的场景

几种常用存储引擎的适用环境

MyISAM:如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性，并发性要求不高，那么选择这个引擎非常合适
Innodb：用于事务的处理，支持外键。如果应用对事务的完整性较高的要求，在并发条件下要求数据的一致性，数据除了插入和查询外，还包括很多的更新和删除操作，那么Innodb存储引擎比较适合
MEMORY：将所有数据都存在RAM中，如果需要快速定位记录和其他类似数据的环境下，可以提供极快的访问，缺陷在于对表大小的限制，太大的表无法缓存在内存中，其次是要确保表的数据是可恢复的.
MERGE:用于将一系列等同MyISAM表以逻辑方式组合在一起，并作为一个对象引用它们。MERGE表的优点在于可以突破单个MyISAM表大小的限制，并且通过将不同的表分布在多个磁盘上，可以有效的改善MERGE表的访问效率

Text与BLOB
如果保存少量字符串会选择CHAR和VARCHAR 但是保存较大文本时，选择text或blob，两者主要差别是blob能用来保存二进制数据如图片；而text只能保存字符数据

BLOB与TEXT引起的性能问题，特别是在执行大量的删除数据时，删除操作会留下很大的空洞，以后填入这些空洞的记录在插入的性能上会有影响，建立定期使用OPTIMIZE TABLE对这类表进行碎屏整理

使用合成的索引来提供大文本字段的查询性能

合成索引就是根据大文本字段的内容建立一个散列值，并把值存储在单独的数据列中，接下来就是通过检索散列值找到数据行，但是只能做到精确匹配不能使用范围搜索。可以使用MD5，SHA1，CRC32 等生成散列值，使用精确匹配，在一定程度上减少了I/O，提高了查询效率。如果散列算法生成的字符串带有尾部空格，就不要存储在CHAR或VARCHAR列中，它会受尾部空格的影响

如果需要对BLOB或CLOB字段进行模糊查询，MySQL提高前缀索引，也就是只为字段的前n列创建索引
desc select * from t where context like 'beijing%' \G;

注意事项

在不必要的时候避免检索大型的BLOB或TEXT：如SELECT * 查询，尽量从符合条件的数据行中检索BLOB或TEXT指
把BLOB或TEXT列分离到单独表中：在某些环境下，如果把这些数据列移动到第二张数据表中，可以把原数据表中的数据列转换为固定长度的数据行格式，减少主表的碎片，可以得到固定长度数据行的性能优势。还可以在运行SELECT * 查询的时候不会通过网络传输大量的BLOB或TEXT指

设计索引的原则

搜索的索引列，不一定是所要选择的列。最适合索引的列是出现在where字句中的列，或连接字句中指定的列，而不是出现在select关键字后的列表中的列
使用唯一索引.考虑到某列中的值分布，索引的列基础越大，索引的效果越好。入存放出生日期的列具有各部相同的值，很容易区分，但是记录性别的列，只含有男和女对此类进行索引没有多大好处
使用短检索。如果对字符串进行检索，应该指定一个前缀长度。例如：一个CHAR(200)列，如果前10个或20个字符内，多数值是唯一的，那么就不要对整个列进行检索。对前10个或20个字符进行检索能够节省大量索引空间，是查询更快。
利用最左前缀。在创建一个n列索引时，实际是创建了MySQL可利用的n个索引。多列索引可起几个索引的作用，因为可利用索引最左边的列级来匹配。
不要过度索引。每个索引都是占用额外的磁盘空间，并降低写操作的性能。在修改表内容的时候，索引必须进行相应的更新，有时候需要重构。如果有一个索引很少被用到，那么会不必要的减缓表的修改速度。此外，mysql在生成一个执行计划时，要考虑各个索引，这也要花费时间。创建多余的索引给查询优化带来了更多的工作
对于Innodb，记录默认会按照一定的顺序排序，如果有明确的定义主键，则按照主键排序顺序保存。 http://blog.csdn.net/qq_22329521/article/details/53876662