选择优化的数据类型
- 更小的通常更好。更小的数据类型通常更快、占用更少的磁盘、内存和CPU缓存
- 简单就好。简单的数据类型的操作通常需要更少的CPU周期，例如整形比字符串操作代价更低
- 尽量避免
NULL。通常最好指定列为NOT NULL，因为NULL是列的默认属性。如果查询中包含可为NULL的列，对MySql来说更难优化
整数类型
| 名称 | 占用内存(字节) |
|---|---|
TINYINT |
1 |
SMALLINT |
2 |
MEDIUMINT |
3 |
INT |
4 |
BIGINT |
8 |
- 整数类型具有可选的
UNSIGNED属性，表示不允许负数。示例:INT UNSIGNED - MySql可以为整数类型指定宽度，例如
INT(11)，对大多数应用这是没有意义的:这并不会限制值的合法范围，只是规定了MySql的一些交互工具用来显示字符的个数，对于存储于计算来说，INT(1)和INT(20)是一样的
实数类型
| 名称 | 占用内存(字节) |
|---|---|
DECIMAL |
[1, 65] |
FLOAT |
4 |
DOUBLE |
8 |
-
DECIMAL用于存储精确的小数，运算速度没有浮点运算快 -
DECIMAL具有UNSIGNED属性 -
DECIMAL(P,D)
P是表示有效数字数的精度。 P范围为[1, 65]，默认为10
D是表示小数点后的位数。 D的范围是[0, 30]。MySQL要求D小于等于P。
例:DECIMAL(6,2)最多可以存储6位数字，小数位数为2位; 因此范围是从-9999.99到9999.99。 -
DOUBLE、FLOAT也可以指定精度，但是不建议
字符串类型
| 名称 | 备注 |
|---|---|
VARCHAR |
vachar(N) 用来存储非二进制字符串，插入时，对于少于N个字符的不填补空格，查询时，尾部的空格不会被丢弃掉 |
CHAR |
char(N)用来存储非二进制字符串，插入时，对于少于N个字符的会自动在尾部加空格，查询时，尾部的空格就会被丢弃掉 |
BINARY |
binary(N)存储二进制字符串，插入进，少于N个字节的会自动在尾部加0x00，取出时，所有的字节都保留，返回定义长度的字节长度 |
VARBINARY |
varbinary在插入不会去填补0x00字节，查询的时候也不会丢弃任何字节 |
BLOB |
用于存储大型二进制数据。TINYBLOB(255)，BLOB(65535)，MEDIUMBLOB(16MB-1)，LONGBLOB(4GB-1) |
TEXT |
用于存储大型字符串。TINYTEXT(255)，TEXT(65535)，MEDIUMTEXT(16MB-1)，LONGTEXT(4GB-1) |
- MySQL单行最大限制为65535，不包括
TEXT、BLOB。此条规定间接限制了VARCHAR的最大长度 - 最好避免使用
BLOB与TEXT
使用枚举代替字符串类型
- 枚举列可以把一些不重复的字符串存储成一个预定义的集合，在内部会将每个值保存为整数，且排序方式也是按照整数的规则进行的
- 枚举最不好的地方是字符串列表是固定的，添加或删除字符串必须使用
ALTER TABLE - 由于MySql把每个枚举值保存为整数，并且必须进行查找才能转换为字符串，所以枚举列有一些开销
create table sznTable(v enum('banana', 'apple'));
insert into sznTable(v) values('apple'),('banana');
select * from sznTable |
select v + 1 from sznTable |
select * from sznTable order by v |
|---|---|---|
| apple | 2 | banana |
| banana | 3 | apple |
日期与时间类型
| 名称 | 备注 |
|---|---|
DATATIME |
此类型能保存大范围的值，从1001年到9999年，精度为秒 把时间封装到YYYYMMDDHHMMSS的整数中 与时区无关 使用8字节的存储空间 默认情况下，MySql以一种可排序、无歧义的格式显示此类型的值，例如:"2018-09-10 20:29:00" |
TIMESTAMP |
保存了从 1970年1月1日 0点0分0秒 以来的秒计数，和UNIX时间戳相同 使用4字节的存储空间 提供 FROM_UNIXTIME()、UNIX_TIMESTAMP，进行UNXI时间戳与日期的转换与时区相关 |
TIMESTAMP时间戳在创建的时候可以有多重不同的特性:
- 在创建新记录和修改现有记录的时候都对这个数据列刷新：
TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP- 在创建新记录的时候把这个字段设置为当前时间，但以后修改时，不再刷新
TIMESTAMP DEFAULT CURRENT_TIMESTAMP- 在创建新记录的时候把这个字段设置为0，以后修改时刷新它：
TIMESTAMP ON UPDATE CURRENT_TIMESTAMP- 在创建新记录的时候把这个字段设置为给定值，以后修改时刷新它：
TIMESTAMP DEFAULT ‘yyyy-mm-dd hh:mm:ss' ON UPDATE CURRENT_TIMESTAMP- 默认情况下，如果插入时没有指定第一个
TIMESTAMP列的值，MySql会设置这个列的值为当前时间，更新时也是如此
create table sznTest
(
t0 timestamp,
t1 timestamp default current_timestamp on update current_timestamp,
t2 timestamp default current_timestamp,
t3 timestamp on update current_timestamp,
t4 timestamp default '2018-09-10 20:54:00' on update current_timestamp
);
位数据类型
| 名称 | 备注 |
|---|---|
BIT |
可以使用BIT列存储一个或多个true/false值BIT(1)定义一个包含单个位的字段，以此类推，最大长度是64MySql将 BIT当做字符串类型而非数据类型，然而在数字上下文中会将其转为数字 |
SET |
最多保存64个元素的集合 在MySql中是以一系列打包的位的集合来表示的，一般无法在 SET列中通过索引查找主要缺点:改变列的定义时需要用 ALTER TABLE，代价可能会很高1～8成员的集合，占1个字节 9～16成员的集合，占2个字节 17～24成员的集合，占3个字节 25～32成员的集合，占4个字节 33～64成员的集合，占8个字节 |
create table t (v bit(8));
insert into t (v) values(b'00111001');
select * from t |
select v + 0 as value from t |
|---|---|
| 9 | 57 |
- 不同于枚举，枚举列的值只能是预设值中的一个，而
SET列的值可以是预设值中的多个的组合
create table t (v set('szn', 'slz', 'szy'));
insert into t(v) value('szn');
insert into t(v) values('szn,slz');
insert into t(v) values('szn,szy');
select * from t |
select v + 0 as value from t |
|---|---|
| szn | 1 |
| szn,slz | 3 |
| szn,szy | 5 |
