MySQL学习笔记（4）：数据类型

本文更新于2019-06-19，使用MySQL 5.7，操作系统为Deepin 15.4。

目录

• 数值类型
  • 整数类型
  • 定点数类型
  • 浮点数类型
  • 位类型
• 日期时间类型
• 字符串类型
  • 枚举类型
  • 集合类型

整数类型

type[(m)] [UNSIGNED] [ZEROFILL] [AUTO_INCREMENT]

m用于指定显示宽度。上面列出的属性为整数类型特有。ZEROFILL表示位数不够显示宽度时在前补0，且自动添加UNSIGNED属性。AUTO_INCREMENT从1开始，一个表只能有一个AUTO_INCREMENT的列，且必须为NOT NULL，并且必须为PRIMARY KEY或UNIQUE。AUTO_INCREMENT的列如插入0或NULL，则插入自动增长后的值。

定点数类型

定点数实际是以字符串形式存放。

DECIMAL[(m, d)]

m表示精度（有效位数），d表示标度（小数位数），默认为(10, 0)。

浮点数类型

type[(m, d)]

其使用类似DECIMAL，但(m, d)是非标准用法，不建议使用。

位类型

BIT(m)

m取值从1至64，默认为1。插入时，会将数值转换成二进制，查询时直接SELECT会看不到结果，需使用BIN(value)或HEX(value)函数。

如需查看TIMESTAMP时间戳数值，则需对其进行"+0"数值运算。表中只能有一列TIMESTAMP默认值为CURRENT_TIMESTAMP，该列在插入或更新时都会自动设置为当前时间。TIMESTAMP与时区有关，DATETIME只与插入时的时区有关。

插入日期时间的格式有多种，下面以DATETIME为例：

• "YYYY-MM-DD HH:MM:SS"：允许不严格的间隔，即任何标点符都可当作日期间隔（"-"）或时间间隔（":"）。若包含日期间隔，月和日可取一位；若包含时间间隔，时分秒均可取一位。
• "YYYYMMDDHHMMSS"
• YYYYMMDDHHMMSS

对于DATETIME，时分秒可从后往前省略，省略值设置为0。

CHAR使用空格填充，BINARY使用填充。比较时CHAR会忽略尾部的空格，BINARY不会忽略尾部的。

CHAR为定长字符串，VARCHAR为变长字符串。InnoDB存储引擎建议使用VARCHAR，因内部的行存储格式使用指向字符串值的头指针。MyISAM存储引擎建议使用CHAR。MEMORY存储引擎无论使用CHAR还是VARCHAR，都是作为定长字符串存储。

存储少量字符串时使用CHAR或VARCHAR，在存储较大文本时，使用[*]TEXT或[*]BLOB。[*]BLOB还可以用来存储二进制数据。使用[*]TEXT或[*]BLOB需注意如下问题：

1. 使用[*]TEXT或[*]BLOB会导致一些性能问题，特别是执行删除操作后数据表会留下“空洞”，建议定期使用OPTIMIZE TABLE tablename进行碎片整理。
2. 使用前缀索引或根据内容的散列值创建的合成索引，提高查询性能。
3. 在不必要的时候，避免SELECT出[*]TEXT或[*]BLOB，否则会导致在网络上传输大量的数据。
4. 把[*]TEXT和[*]BLOB分离到单独的表中。

枚举类型

ENUM("value"[, ...])

ENUM类型忽略大小写。若插入不在ENUM指定范围内的值时，会转为插入第一个值。插入语句如下：

INSERT INTO TABLE tablename(colname) VALUES("value")

集合类型

SET("value"[, ...])

SET类型不能插入不在指定范围内的值，且插入时会对值集合进行排重。插入语句如下：

INSERT INTO TABLE tablename(colname) VALUES("value[,...]")