MySQL的数据模型

时间：2021-10-01 11:10:39

MySQL的数据类型主要分为三大类：

  1. 数值型（Numeric Type）
  2. 日期与时间型（Date and Time Type）
  3. 字符串类型（String Type）

1. 数值

MySQL的数值类型按照精确程度，大致可以分为两类：

  • 精确数值（Exact Value），包括INTEGER、DECIMAL、NUMERIC等；
  • 近似数值（Approximate Value），包括FLOAT、REAL、DOUBLE PRECISION等。

若按类型簇划分，则可分为三簇：

  • Integer类型，包括INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT；
  • Fixed-Point类型，包括DECIMAL, NUMERIC；
  • Floating-Point类型，包括FLOAT, DOUBLE。

一般地，关键字INT代指INTEGER，关键字DEC（desc只降序）代指DECIMAL， DOUBLE代指DOUBLE PRECISION（a nonstandard extension）。BIT类型保存位字段值，并且支持MyISAM、MEMORY、InnoDB和BDB表。数值类型的存储、范围及用途如下表：

类型 大小（字节） 范围（有符号） 范围（无符号） 用途
TINYINT 1 (-128，127) (0，255) 小整数值
SMALLINT 2 (-32 768，32 767) (0，65 535) 大整数值
MEDIUMINT 3 (-8 388 608，8 388 607) (0，16 777 215) 大整数值
INT 4 (-2 147 483 648，2 147 483 647) (0，4 294 967 295) 大整数值
BIGINT 8 (-9 233 372 036 854 775 808，9 223 372 036 854 775 807) (0，18 446 744 073 709 551 615) 极大整数值
FLOAT 4 (-3.402 823 466 E+38，1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) 0，(1.175 494 351 E-38，3.402 823 466 E+38) 单精度浮点数值
DOUBLE 8 (1.797 693 134 862 315 7 E+308，2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 双精度浮点数值
DECIMAL 对于DECIMAL(M,D)，为M+2 依赖于M和D的值 依赖于M和D的值 小数值

DECIMAL类型常被声明为DECIMAL(M,D)，其中M为precision，指允许的最多数字位数（the maximum number of digits）；D为scale，指小数点后的小数位数（the number of digits to the right of the decimal point）；一般D不大于M。对于Floating-Point类型，MySQL允许类似的声明语法：FLOAT(M,D)、REAL(M,D)、DOUBLE PRECISION(M,D)。

常见的类型转换:

-- string to decimal

cast(cover_ratio as decimal(4,2))

2. 日期与时间

日期和时间类型（Date and Time Type）包括了如下5种类型：

  • DATE，指日期，遵从'YYYY-MM-DD'（或'YY-MM-DD'）格式，支持区间段['1000-01-01', '9999-12-31']；
  • TIME，指时间，遵从'HH:MM:SS'（或'HHH:MM:SS'）格式，支持区间段['-838:59:59', '838:59:59']；
  • DATETIME，包含了日期与时间，一般精确到秒（精确度最高到微妙），遵从 'YYYY-MM-DD HH:MM:SS'（或'YY-MM-DD HH:MM:SS'）格式，支持区间段['1000-01-01 00:00:00', '9999-12-31 23:59:59']；
  • TIMESTAMP，包含了日期与时间，一般精确到秒（精确度最高到微妙），无格式遵从，支持区间段['1970-01-01 00:00:01' UTC, '2038-01-19 03:14:07' UTC]；
  • YEAR，指年份，常被声明为YEAR或YEAR(4) （1-byte type）；

至于应选择什么数据类型表示时间戳，这篇文章给出了答案。MySQL同时支持ODBC语法来表示日期和时间类型：

{ d '2016-10-21' }  -- date

{ t '2016-10-21' }  -- time

{ dt '2016-10-21' }  -- datetime

{ ts '2016-10-21' }  -- timestamp

官方提供了Date and Time函数，下面给出一些常见的类型转换：

-- string to date

STR_TO_DATE('01,5,2016','%d,%m,%Y')

-- date to string

DATE_FORMAT(NOW(), '%d %m %Y')

-- timestamp to date_string

DATE_FORMAT(registration_time, '%Y-%m-%d')

-- int to date

FROM_UNIXTIME(measured_on) > '2016-01-01'

3. 字符串

MySQL中有两个重要的概念：character set、collation，分别表示字符编码集、字符校对规则，显示所有可用的命令：

show character set;

show collation;

String类型大致可以分为如下三类：

  • char类型，包括CHAR、VARCHAR、TEXT，分别表示定长字符串、变长字符串、长文本；
  • binary类型，包括BINARY、VARBINARY、BLOB，为定长、变长的二进制数据及较长的二进制数据；
  • enum类型与set类型。

其中，根据字符串（文本）长度，TEXT可细分为TINYTEXT、TEXT、MEDIUMTEXT；根据二进制数据长度，BLOB可细分为TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB。binary类型最大的特点是没有编码集和校对规则，存储形式为byte。

MySQL内置的string函数，下面给出一些我遇到过的用法：

-- replace occurrences of a specified string

replace('苹果 iPad Air', '苹果 ', '')

-- concat strings

concat_ws(',', 'hello', 'world')

-- string to int

CAST('10' AS UNSIGNED)

CAST('-10' AS SIGNED)

相关文章