MySQL的数据模型

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 setcollation,分别表示字符编码集、字符校对规则,显示所有可用的命令:

show character set;
show collation;

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

  • char类型,包括CHARVARCHARTEXT,分别表示定长字符串、变长字符串、长文本;
  • binary类型,包括BINARYVARBINARYBLOB,为定长、变长的二进制数据及较长的二进制数据;
  • enum类型与set类型。

其中,根据字符串(文本)长度,TEXT可细分为TINYTEXTTEXTMEDIUMTEXT;根据二进制数据长度,BLOB可细分为TINYBLOBBLOBMEDIUMBLOBLONGBLOB。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)



posted @ 2016-11-16 17:16 Treant 阅读(...) 评论(...) 编辑 收藏