MySQL的数据模型
MySQL的数据类型主要分为三大类：
- 数值型（Numeric Type）
- 日期与时间型（Date and Time Type）
- 字符串类型（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)
