内置数据结构

MySQL 内置数据结构

数字类型

数字类型算是最简单的了,主要差异在于各个类型的取值范围大小限制,和对存储空间字节数的需求。数字类型当然是在满足情况的条件下越短越好,一方面 MySQL 每行有 65535 字节长度的限制,同时更宽的数据类型意味着对 CPU、内存、磁盘 IO 带来压力。

定点类型

类型

长度

TINYINT

1

SMALLINT

2

MEDIUMINT

3

INTEGER

4

BIGINT

5

在数据库设计的时候,常常看到这些整形有个前缀长度,其实这对其类型本身的存储长度和精度没有影响,只会关系到某些交互式工具显示出来的字符个数。

浮点类型

类型

长度

FLOAT

4

FLOAT(p) [0,24]

4

FLOAT(p) [25,53]

8

DOUBLE,REAL

8

计算机的浮点运算都是不精确的,如果要实现精确浮点运算,就需要使用 DECIMAL 类型。

时间与日期类型

常被使用的是 DATE、DATETIME 和 TIMESTAMP 类型,其表示的范围为:

  • DATE:’1000-01-01’ to ‘9999-12-31’

  • DATETIME:’1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’

  • TIMESTAMP:’1970-01-01 00:00:01’ UTC to ‘2038-01-19 03:14:07’ UTC

TIMESTAMP 存储的范围比 DATETIME 要小,但是空间利用率也最高。MySQL 支持的时间精度最高为 1s,如果更精确的存储,就必须自己定义存储格式了。

获取函数

now 与 sysdate 是常见的获取日期的函数,不过 now() 在执行开始时值就得到了,sysdate() 在函数执行时动态得到值。

--- 获得当前日期+时间(date + time)
mysql> select now();
--- sysdate() 日期时间函数,一般情况下很少用到。
mysql > select now(), sleep(3), now(); -- 2019-06-25 14:54:17 0 2019-06-25 14:54:17
--- 获得当前时间戳
mysql> select current_timestamp, current_timestamp();

转换函数

MySQL 日期、时间转换函数:date_format(date,format), time_format(time,format) 能够把一个日期/时间转换成各种各样的字符串格式。它是 str_to_date(str,format) 函数的 一个逆转换。

--- 日期/时间转换为字符串
mysql> select date_format('2018-08-08 22:23:01', '%Y%m%d%H%i%s');
--- 转化为日期对象
mysql> select str_to_date('08/09/2018', '%m/%d/%Y'); -- 2018-08-09
mysql> select str_to_date('08/09/18' , '%m/%d/%y'); -- 2018-08-09
mysql> select str_to_date('08.09.2018', '%m.%d.%Y'); -- 2018-08-09
mysql> select str_to_date('08:09:30', '%h:%i:%s'); -- 08:09:30
mysql> select str_to_date('08.09.2018 08:09:30', '%m.%d.%Y %h:%i:%s'); -- 2018-08-09 08:09:30
--- 日期、天数转换函数
mysql> select to_days('0000-00-00'); -- 0
mysql> select to_days('2018-08-08'); -- 737279
--- 时间、秒转换函数
mysql> select time_to_sec('01:00:05'); -- 3605
mysql> select sec_to_time(3605); -- '01:00:05'
--- 拼凑日期、时间函数
mysql> select makedate(2001,31); -- '2001-01-31'
mysql> select makedate(2001,32); -- '2001-02-01'
mysql> select maketime(12,15,30); -- '12:15:30'
--- Unix 时间戳、日期转换函数
mysql> select unix_timestamp(); -- 1218290027
mysql> select unix_timestamp('2008-08-08'); -- 1218124800
mysql> select unix_timestamp('2008-08-08 12:30:00'); -- 1218169800
mysql> select from_unixtime(1218290027); -- '2008-08-09 21:53:47'
mysql> select from_unixtime(1218124800); -- '2008-08-08 00:00:00'
mysql> select from_unixtime(1218169800); -- '2008-08-08 12:30:00'
mysql> select from_unixtime(1218169800, '%Y %D %M %h:%i:%s %x'); -- '2008 8th August 12:30:00 2008'

计算函数

-- 为日期增加一个时间间隔
mysql> set @dt = now();
mysql> select date_add(@dt, interval 1 day); -- add 1 day
mysql> select date_add(@dt, interval 1 hour); -- add 1 hour
mysql> select date_add(@dt, interval 1 minute); -- ...
mysql> select date_add(@dt, interval 1 second);
mysql> select date_add(@dt, interval 1 microsecond);
mysql> select date_add(@dt, interval 1 week);
mysql> select date_add(@dt, interval 1 month);
mysql> select date_add(@dt, interval 1 quarter);
mysql> select date_add(@dt, interval 1 year);
select date_add(@dt, interval -1 day); -- sub 1 day

MySQL 中时间戳同样支持计算:

select timestamp('2008-08-08'); -- 2008-08-08 00:00:00
select timestamp('2008-08-08 08:00:00', '01:01:01'); -- 2008-08-08 09:01:01
select timestamp('2008-08-08 08:00:00', '10 01:01:01'); -- 2008-08-18 09:01:01
select timestampadd(day, 1, '2008-08-08 08:00:00'); -- 2008-08-09 08:00:00
select date_add('2008-08-08 08:00:00', interval 1 day); -- 2008-08-09 08:00:00
-- MySQL timestampadd() 函数类似于 date_add()。
select timestampdiff(year,'2002-05-01','2001-01-01'); -- -1
select timestampdiff(day ,'2002-05-01','2001-01-01'); -- -485
select timestampdiff(hour,'2008-08-08 12:00:00','2008-08-08 00:00:00'); -- -12
select datediff('2008-08-08 12:00:00', '2008-08-01 00:00:00'); -- 7

时区转换

时区转换也可以通过 date_add, date_sub, timestampadd 来实现。

convert_tz(dt,from_tz,to_tz)
select convert_tz('2008-08-08 12:00:00', '+08:00', '+00:00'); -- 2008-08-08 04:00:00
select date_add('2008-08-08 12:00:00', interval -8 hour); -- 2008-08-08 04:00:00
select date_sub('2008-08-08 12:00:00', interval 8 hour); -- 2008-08-08 04:00:00
select timestampadd(hour, -8, '2008-08-08 12:00:00'); -- 2008-08-08 04:00:00

字符串类型

MySQL 中的字符串类型比较多也比较的复杂,各个字符串类型的差别不仅仅在存储时候的空间占用,对存取时候字段某位的 strip 和 padding 还有差异。对于类型 CHAR/VARCHAR/TEXT 是跟本地字符集相关的,这会影响到实际占用空间的字节数、字符比较等。

类型

长度

CHAR(M)

Mxw bytes

BINARY(M)

M bytes

VARCHAR(M), VARBINARY(M)

L+1/L+2 bytes

TINYBLOB, TINYTEXT

L+1 bytes

LOB, TEXT

L+2 bytes

MEDIUMBLOB, MEDIUMTEXT

L+3 bytes

LONGBLOB, LONGTEXT

L+4 bytes

根据官方手册,CHAR/BINARY 及其衍生的类型的数据是存储在表的行内部(inline)的,而对于 BLOB 和 TEXT 类型,每一个字段只占用该行 9-12(1~4+8)个字节(用于数据的地址和长度),实际的数据是存储在 Row Buffer 之外位置的。所以对于经常访问的字符串类型,而长度又不是特别的大,还是建议用 VARCHAR(M)的数据类型,性能会比 TEXT 快不少。

CHAR(M)/VARCHAR(M)

长度限制参数 M 表示的是本地字符集的字符个数而不是 bytes 数目,比如对于 UTF8 编码,每个本地字符其实际占用的 byte 长度可能是 3 或 4 倍的本地字符长度。比如 VARCHAR(255),如果每个本地字符占用两个字节,那么其需要的存储空间最大为 255x2+2。CHAR 的 M 被限制在最大 255,而 VARCHAR 的 M 理论上受限于 Row Size 的长度(65,535bytes),且实际存储时候会附加 1~2 字节的前缀表示数据实际长度。如果 strict SQL 模式没有被打开,那么当插入数据超过声明长度限制的时候,数据将会被截断并产生警告信息,在 strict SQL 模式下将会出错。

CHAR 类型在存储的时候,会在右端 padding SPACE 到指定的 M 长度,当取该字段的时候,所有末尾的 SPACE 都将会被 strip 掉然后返回;VARCHAR 不会对进行 SPACE 进行 padding 以及 strip 操作,存储什么样的数据就会返回什么样的数据。对于 CHAR/VARCHAR/TEXT 类型,在进行字符串比较的时候,(SQL 语句参数中的字符串)结尾的空格都是不参与比较的,但是对于 LIKE 语句,检索的时候结尾的空格是考虑在内的。

BINARY(M)/VARBINARY(M)

BINARY/VARBINARY 在操作的时候,参考的是 byte streaming 而不是 charaset streaming,所以其长度限制参数 M 表示的是 byte 数目,在比较的时候也是直接的数字大小比较(而非本地字符集方式比较)。

BINARY 在插入的时候,会使用 0x00(而非 SPACE)padding 到长度 M,取值的时候不会进行 strip 尾部空字符的操作(意味着取出来的长度一定是 M);VARBINARY 则是保证原样存取的。

BLOB/TEXT

分别有 TINY/MEDIUM/LONG 类型的衍生长度,BLOB 是 bytes streaming 类型的,而 TEXT 是基于 character streaming 本地字符集类型的,两者在存取的时候都不会进行 padding 和 strip 操作。BLOB/TEXT 的关系和之前的 VARBINARY/VARCHAR 是比较相似的,除了:BLOB/TEXT 不能够有 DEFAULT 值;BLOB/TEXT 在创建索引的时候必须要有 prefix length,而前者是可选的;给予 TEXT 索引需要有前缀长度,而且建立索引会自动 padding SPACE 到索引长度,所以如果插入的字符前面一样,只是尾部空字符长度不同,也是会产生相同的索引值。