目录
数据类型
日常开发中，最常使用到的 MySQL 中常用的数据类型有 : 整型, 浮点型, 定点数, 字符型, 时间日期类型. 下面对其分别进行总结。
整型
格式为 : INT[(M)] [UNSIGNED] [ZEROFILL]
- 取值范围：如果是
unsigned无符号位 , 则最大值翻倍, 如 tinyint unsigned 的取值范围为 (0~256). - 注意：INT(1) 和 INT(10) 没有区别, 占用的内存都一样, 并不是代表其最大位数，只是加上 (M) 值后, 在 CMD 中显示的结果的宽度不同而已. 只有在加上 zerofill 零填充后 M 才表现出有点点效果, 比如 int(3) zerofill, 插入到数据库里的是 10, 则实际插入为 010, 也就是在前面补充加了一个0.
- Java 中的
Long类型, 和 MySQL 中的bigint相对应. - Java 中的
Integer类型, 和 MySQL 中的int相对应. - Java 中的
Boolean类型, 和 MySQL 中的tinyint相对应.false 代表 0, true 代表 1.
浮点型
- 设一个字段定义为 float(5,3), 5 表示在 CMD 中显示的总位数是 5 位, 3 表示小数位是 3 位.
- 小数位超过指定长度, 对后面的数字按照四舍五入截取. 所以如果插入一个数 123.45678, 实际数据库里存的是123.457, 进行了四舍五入截取.
- 小数位如果都是0, 则不保存小数位, 直接保存整数.
定点数
浮点型在数据库中存放的是近似值, 而定点类型在数据库中存放的是精确值. 所以对于金额等精度要求高的数据, 使用定点数 decimal 来保存.
decimal(m,d) 参数 m 是总个数, d 是小数位. ( m<65, d<30, d<m )
字符型
| 字符型 | 含义 | 占用字节数 |
|---|---|---|
| char(n) | 固定长度, 最多 255 个字符 | 4 byte |
| varchar(n) | 可变长度, 最多 65535 个字符 | 实际存入字节数+1 |
char 类型的字符串检索速度要比 varchar 类型的快. 所以能 char 的优先 选择 char.- char(n) 若存入字符数小于 n, 则以空格补于其后, 查询之时再将空格去掉. 所以
char 类型存储的字符串末尾不能有空格, varchar 不限于此. - char(n) 固定长度, char(4) 不管是存入几个字符, 都将占用 4 个字节. varchar 可变长度, 是存入的实际字符数+1个字节（n<=255）或 2 个字节(n>255), 所以 varchar(4), 存入3 个字符将占用 4 个字节.
- varchar(10) 的 10 代表了申请的空间长度, 即
存储的数据的最大长度, 而 int(10) 的 10 只是代表了展示的长度.
日期时间型
| 日期时间类型 | 占用空间(字节数) | 日期格式 |
|---|---|---|
| DATETIME | 8 byte | YYYY-MM-DD HH:MM:SS |
| TIMESTAMP | 4 byte | YYYY-MM-DD HH:MM:SS |
| DATE | 4 byte | YYYY-MM-DD |
| TIME | 3 byte | HH:MM:SS |
| YEAR | 1 byte | YYYY |
- 根据实际需要选择能够满足的最小存储的类型即可. 比如 :
DATE 用于表示 年月日, 生日字段使用 DATE 类型即可. 这样不但能节省内存, 还能提高表的操作效率. - 如果要记录年月日时分秒, 而且记录的年代比较久远, 最好使用 DATETIME. 因为
TIMESTAMP 可以表示的年代范围比 DATETIME 少的多. - 如果记录的日期需要用到时区, 需要选择 TIMESTAMP.
- 一个表中至多
只能有一个字段设置 CURRENT_TIMESTAMP. 并且 CURRENT_TIMESTAMP 只适合 TIMESTAMP 类型的字段.
CREATE TABLE USER( ... create_time timestamp NULL default CURRENT_TIMESTAMP, -- 默认为当前时间 ... )
字段类型的选取原则
- 字段类型优先级 :
整型 > date,time > char,varchar > blob
原因：整型运算快, 且节省空间. char, varchar 要考虑字符集的转换与排序时的校对集, 所以速度慢.
- 优先选择
占空间小的类型.
原因 : 小空间的类型占用的磁盘, 内存, CPU 缓存, CPU 处理时间都更少.
对于字符型和数字型, 优先选择数字型.
比如 : 应该使用整型来存储 IP 地址, 而不是字符型. 应该使用日期时间类型来存储日期时间, 而不是字符型.
- 对于 char 和 varchar
如果列中的数据长度差不多一致, 比如 : 身份证号, 学号等, 则应该考虑使用 char 类型.
对于那些长度未知的字符串, 使用 varchar.
如果列中的最大数据长度小于 50 Byte (15个字符左右), 则一般考虑使用 char.
- 对于 decimal 和 float:
精确数据只能使用 decimal 类型.
非精确数据优先使用 float 类型, ( float 比 decimal 的存储空间小 )
- 对于那些可能值很有限的列,
使用 tinyint 代替 varchar / int
比如记录移动设备平台, 只有两个值：android, ios, 那么就可以使用 0 表示 android, 1 表示 ios, 并写好注释.
不要滥用 bigint, 比如文章表的主键 id 字段, 用 int 就行了, 21 亿篇文章的上限足够使用了.
以上原则主要是从以下角度考虑 :
- 查询速度：
定长数据比变长数据查询快, 因为定长数据与数据之间的偏移是固定的, 很容易计算下一个数据的偏移.
而变长数据则还需要多一步去查询下一个数据的偏移量. 不过定长数据可能会浪费更多的存储空间.
- 排序速度：在对数据进行
比较 ( 查询条件, JOIN 条件以及排序 ) 操作时,同样的数据, 数字的处理比字符串快.
字符串的比较与当前的排序规则有关, 它需要去查询当前排序规则的字典顺序, 而数字不需要.
