MySQL学习笔记（4）：数据类型
本文更新于2022-04-17，使用MySQL 5.7，操作系统为Deepin 15.4。
数值类型
整数类型
type[(m)] [UNSIGNED] [ZEROFILL] [AUTO_INCREMENT]
| 类型 | 字节 | 最小值 | 最大值 |
|---|---|---|---|
| TINYINT | 1 | 有符号-128，无符号0 | 有符号127，无符号255 |
| SMALLINT | 2 | 有符号-32768，无符号0 | 有符号32767，无符号65535 |
| MEDIUMINT | 3 | 有符号-8388608，无符号0 | 有符号8388607，无符号16772150 |
| INT/INTERGER | 4 | 有符号-2147483648，无符号0 | 有符号2147483647，无符号4294967295 |
| BIGINT | 8 | 有符号-9223372036854775808，无符号0 | 有符号9223372036854775807，无符号18446744073709551615 |
m用于指定显示宽度。上面列出的属性为整数类型特有。ZEROFILL表示位数不够显示宽度时在前补0，且自动添加UNSIGNED属性。AUTO_INCREMENT从1开始，一个表只能有一个AUTO_INCREMENT的列，且必须为NOT NULL，并且必须为PRIMARY KEY或UNIQUE。AUTO_INCREMENT的列如插入0或NULL，则插入自动增长后的值。
定点数类型
定点数实际是以字符串形式存放。
DECIMAL[(m, d)]
| 类型 | 字节 | 最小精确度 | 最大精确度 |
|---|---|---|---|
| DECIMAL | m+2 | 由m、d决定，不能超过±2.2250738585072014E-308 | 由m、d决定，不能超过±1.7976931348623157E+308 |
m表示精度（有效位数），d表示标度（小数位数），默认为(10, 0)。
浮点数类型
type[(m, d)]
| 类型 | 字节 | 最小精确度 | 最大精确度 |
|---|---|---|---|
| FLOAT | 4 | ±1.175494351E-38 | ±3.402823466E+38 |
| DOUBLE | 8 | ±2.2250738585072014E-308 | ±1.7976931348623157E+308 |
其使用类似DECIMAL，但(m, d)是非标准用法，不建议使用。
位类型
BIT(m)
| 类型 | 字节 | 最少位数 | 最多位数 |
|---|---|---|---|
| BIT | 1~8 | 1 | 64 |
m取值从1至64，默认为1。插入时，会将数值转换成二进制，查询时直接SELECT会看不到结果，需使用BIN(value)或HEX(value)函数。
日期时间类型
| 类型 | 字节 | 最小值 | 最大值 |
|---|---|---|---|
| YEAR | 1 | 1901 | 2155 |
| TIME | 3 | -838:59:59 | 838:59:59 |
| DATE | 4 | 1000-01-01 | 9999-12-31 |
| TIMESTAMP | 4 | 1970-01-01 00:00:00 | 2038-01-19 03:14:07 |
| DATETIME | 8 | 1000-01-01 00:00:00 | 9999-12-31 23:59:59 |
如需查看TIMESTAMP时间戳数值，则需对其进行"+0"数值运算。表中只能有一列TIMESTAMP默认值为CURRENT_TIMESTAMP，该列在插入或更新时都会自动设置为当前时间。TIMESTAMP与时区有关，DATETIME只与插入时的时区有关。
插入日期时间的格式有多种，下面以DATETIME为例：
- 'YYYY-MM-DD HH:MM:SS'：允许不严格的间隔，即任何标点符都可当作日期间隔（'-'）或时间间隔（':'）。若包含日期间隔，月和日可取一位；若包含时间间隔，时分秒均可取一位。
- 'YYYYMMDDHHMMSS'
- YYYYMMDDHHMMSS
对于DATETIME，时分秒可从后往前省略，省略值设置为0。
字符串类型
| 类型 | 字节 | 值最小长度 | 值最大长度 |
|---|---|---|---|
| CHAR(m) | m个字符字节数 | 0 | m字符（不能超过255字节） |
| VARCHAR(m) | 值字节数+1 | 0 | m字符（不能超过65535字节） |
| BINARY(m) | m | 0 | m字节（不能超过255） |
| VARBINARY(m) | 值字节数+1 | 0 | m字节（不能超过65535) |
| TIINYBLOB | 值字节数+1 | 0 | 255字节 |
| BLOB | 值字节数+2 | 0 | 65535字节 |
| MEDIUMBLOB | 值字节数+3 | 0 | 167772150字节 |
| LONGBLOB | 值字节数+4 | 0 | 4294967295字节 |
| TINYTEXT | 值字节数+1 | 0 | 255字节 |
| TEXT | 值字节数+2 | 0 | 65535字节 |
| MEDIUMTEXT | 值字节数+3 | 0 | 167772150字节 |
| LONGTEXT | 值字节数+4 | 0 | 4294967295字节 |
CHAR使用空格填充，BINARY使用\0填充。比较时CHAR会忽略尾部的空格，BINARY不会忽略尾部的\0。
CHAR为定长字符串，VARCHAR为变长字符串。InnoDB存储引擎建议使用VARCHAR，因内部的行存储格式使用指向字符串值的头指针。MyISAM存储引擎建议使用CHAR。MEMORY存储引擎无论使用CHAR还是VARCHAR，都是作为定长字符串存储。
存储少量字符串时使用CHAR或VARCHAR，在存储较大文本时，使用[*]TEXT或[*]BLOB。[*]BLOB还可以用来存储二进制数据。使用[*]TEXT或[*]BLOB需注意如下问题：
- 使用
[*]TEXT或[*]BLOB会导致一些性能问题，特别是执行删除操作后数据表会留下“空洞”，建议定期使用OPTIMIZE TABLE tablename进行碎片整理。 - 使用前缀索引或根据内容的散列值创建的合成索引，提高查询性能。
- 在不必要的时候，避免
SELECT出[*]TEXT或[*]BLOB，否则会导致在网络上传输大量的数据。 - 把
[*]TEXT和[*]BLOB分离到单独的表中。
枚举类型
ENUM('value'[, ...])
| 类型 | 字节 | 最少成员 | 最多成员 |
|---|---|---|---|
| ENUM | 1~2 | 1 | 65535 |
ENUM类型忽略大小写。若插入不在ENUM指定范围内的值时，会转为插入第一个值。插入语句如下：
INSERT INTO TABLE tablename(colname) VALUES('value')
集合类型
SET('value'[, ...])
| 类型 | 字节 | 最少成员 | 最多成员 |
|---|---|---|---|
| SET | 1~8 | 1 | 64 |
SET类型不能插入不在指定范围内的值，且插入时会对值集合进行排重。插入语句如下：
INSERT INTO TABLE tablename(colname) VALUES('value[,...]')
