1、为表中的字段选择合适的数据类型
当一个列可以选择多种数据类型时,应该优先考虑数字类型,其次是日期或二进制类型,最后是字符类型。
对于相同级别的数据类型,应该优先选择占用空间小的数据类型。
2、如何选择正确的整数类型
类型 | 大小 | 范围(SIGNED) | 范围(UNSIGNED) | 用途 |
---|---|---|---|---|
TINYINT | 1 字节 | (-128,127) | (0,255) | 小整数值,如年龄 |
SMALLINT | 2 字节 | (-32768,32 767) | (0,65535) | 大整数值 |
MEDIUMINT | 3 字节 | (-8388608,8388607) | (0,16777 215) | 大整数值 |
INT | 4 字节 | (-2147483648,2147483647) | (0,4294967295) | 大整数值 |
BIGINT | 8 字节 | (-9233372036854775808,9223372036854775807) | (0,18446744073709551615) | 极大整数值 |
3、如何选择正确的实数类型
列类型 | 存储空间 | 是否精确 |
---|---|---|
FLOAT | 4个字节 | 否 |
DOUBLE | 8个字节 | 否 |
DECIMAL(与金额有关的) | 每4个字节存9个数字,小数点占一个字节 | 否 |
- DECIMAL(18,9):表示该字段可存储数字最大的长度是18位,其中小数位最大的长度是9位。由于DECIMAL每4个字节存9个数字,小数点占一个字节,所以该字段占9个字节。
4、如何选择VARCHAR和CHAR类型
VARCHAR和CHAR类型长度是以字符为定位的而不是字节,如UTF-8编码,每个字符占3个字节,那么CHAR(10)就需要占30个字节
-
VARCHAR类型的存储特点
VARCHAR用于存储变长字符串,只占用必要的存储空间。
当列的最大长度小于255时,只占用一个额外字节用于记录字符串长度。
当列的最大长度大于255,则需要占用两个额外字节用于记录字符串长度。
-
VARCHAR长度选择问题
使用最小的符合需求的长度。
系统上线后尽量不要修改VARCHAR的长度,因为在mysql 5.7之前,只要一修改就会发生锁表。
-
VARCHAR(5)和VARCHAR(200)存储'AAAAA'这一个字符串时都是使用一个额外的字节来记录字符串的长度,那么他们的性能有什么不同呢?
VARCHAR(5)优于VARCHAR(200)
因为MySQL为了能够更有效的优化查询,对于VARCHAR字段使用的是其最大的宽度来分配内存,所以如果我们把宽度定的太长就会消耗更多的内存。
-
VARCHAR类型的适用场景
字符串列的最大长度比平均长度大很多
字符串列很少被更新
使用了多字节字符集存储字符串(如utf8中的中文和英文)
-
CHAR类型的存储特点
CHAR类型是定长的
字符串存储在CHAR类型的列中会删除末尾的空格(VARCHAR则不会)
CHAR类型的最大宽度是255个字符,所以如果当我们长度超过255时,则需要使用VARCHAR类型进行存储
-
CHAR类型的适用场景
存储长度近似的值(md5值,如密码)
存储短字符串
存储经常被更新的字符串列
5、如何选择日期类型
-
DATATIME类型
-
以yyyy-MM-dd HH:mm:ss[.fraction]格式存储日期时间
- DATATIME = yyyy-MM-dd HH:mm:ss
- DATATIME(6) = yyyy-MM-dd HH:mm:
与时区无关,占8个字节的存储空间
时间范围:1000-1-1 00:00:00至9999-12-31 23:59:59
-
-
TIMESTAMP类型
存储了由格林尼治时间1970-1-1 00:00:00到当前时间的秒数,以yyyy-MM-dd HH:mm:ss[.fraction]的格式显示,占用4个字节
时间范围:1970-1-1 到 2038-01-19
显示依赖于所指定的时区
在行的数据修改时可以自动修改TIMESTAMP列的值(设置为NOT NULL即可),默认是第一个为TIMESTAMP类型的列才会自动更新
-
DATE类型(如生日)
占用3个字节
使用DATE类型可以利用日期函数进行日期之间的计算
范围:1000-01-01到9999-12-31
-
TIME类型
- HH:MM:SS
-
注意事项:
-
不要使用字符串类型存储日期时间数据
日期时间类型通常比字符串占用空间小
日期类型在进行查找过滤时可以利用日期来进行对比
可以利用日期函数进行日期之间的计算
-
使用Int存储日期时间不如使用TIMESTAMP类型
- 因为TIMESTAMP类型底层其实也是使用INT来存储的,只不过显示的时候格式化了而已,但更方便查看
-