选择优化的数据类型
- 更小的通常更好。更小的数据类型通常更快、占用更少的磁盘、内存和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 |
网友评论