mysql数据类型汇总
l 数据类型分类
a) 数值类型 -- TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT、 DECIMAL
b) 时间类型 -- DATE、DATETIME、TIMESTAMP
c) 字符串类型 -- CHAR、VARCHAR、TEXT
d) 特有类型 -- ENUM、SET
e) 没有Boolean类型,用tinyint代替
特别说明:
- VARCHAR(N) 或 CHAR(N)中N的含义
MySQL中两类字符串VARCHAR(N) 或CHAR(N)定义时候填写的长度N,不是字节数的意思 ,而是字符数的含义。
- 浮点数
同财务有关的浮点数必须使用精确存储的DECIMAL数值类型。为减少浮点类型FLOAT和DOUBLE的精度不够而可能造成数值计算存在偏差,故非财务相关的浮点数字段也采用DECIMAL数值类型存储,例如:weight DECIMAL(10,4) NOT NULL DEFAULT 0。
l 数据类型范围
数据类型 |
类型分类 |
表达的范围 |
存储需求 |
TINYINT[(M)] [UNSIGNED] [ZEROFILL] |
数值整型 |
-128到127 或 0到255 |
1个字节 |
SMALLINT[(M)] [UNSIGNED] [ZEROFILL] |
-32768到32767 或 0到65535 |
2个字节 |
|
MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL] |
-8388608到8388607 或 0到16777215 |
3个字节 |
|
INT[(M)] [UNSIGNED] [ZEROFILL] |
-2147483648到2147483647 或 0到4294967295 |
4个字节 |
|
BIGINT[(M)] [UNSIGNED] [ZEROFILL] |
-9223372036854775808到9223372036854775807 或 0到18446744073709551615 |
8个字节 |
|
DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL] |
数值混合型 |
整数最大位数(M)为65,小数位数最大(D)为30 |
变长 |
DATE |
日期类型 |
YYYY-MM-DD 精确到天 |
3个字节 |
DATETIME |
YYYY-MM-DD HH:MM:SS(1001年到9999年的范围) |
8个字节 |
|
TIMESTAMP |
YYYY-MM-DD HH:MM:SS(1970年到2037年的范围) |
4个字节 |
|
ENUM |
枚举类型 |
1到65535个元数据 |
1个字节或2个字节 |
SET |
集合类型 |
1到64个元数据 |
1,2,3,4或8个字节 |
CHAR(M) |
字符串类型 |
0<M<=255(建议CHAR(1)外,超过此长度的统一用VARCHAR(M)替代) |
M*N个字节,其中N大小由字符集编码,以及是否为中文还是字母数字等有关系 |
VARCHAR(M) |
0<M<65532/N 个字符 |
||
TEXT |
64K个字节 |
能存储的字符个数,与字符集编码、存储的字符串值有关系 |
l 详细说明
1) 类似于状态,且有限类别的字段,推荐使用可比较明显表示出实际意义的字符串,而不应该使用INT之类的数字来代替,故推荐使用枚举类型ENUM和集合类型SET;
2) 使用UTF8时,因其是变长的字符集,故固定和动态长度的字符串都应用VARCHAR;
3) 仅仅当字符数量可能超过 20000 个的时候,可以使用 TEXT/MEDIUMTEXT 类型来存放字符类数据。推荐所有使用 TEXT/MEDIUMTEXT类型的字段和原表进行分拆,与原表主键单独组成另外一个表进行存放;
4) 需要精确时间(年月日时分秒)的字段可以使用DATETIME 或TIMESTAMP,但请注意各自能表达的范围,以及是否需要用到TIMESTAMP的特性,尽量使用TIMESTAMP类型替代DATETIME以减少数据存储空间的占用;需要毫秒、微妙时,使用TIMESTAMP (3)或TIMESTAMP (6),以及DATETIME(3)和DATETIME(6)(注:社区版5.6开始DATATIME也支持CURRENT_TIMESTAMP.,且支持任意数量与组合);
5) 所有只需要精确到天的字段全部使用 DATE 类型,而不应该使用 TIMESTAMP或者DATETIME 类型;
6) 自增序列特性的字段只能使用 INT 或者 BIGINT,强烈推荐明确标识为无符号类型 (关键词:UNSIGNED),除非确实会出现负数,仅当该字段的数值会超过42亿,才使用 BIGINT 类型,并且自增字段必须作为主键或主键的一部分;
7) INT(N)中N的含义
MySQL中各类整形也可包含长度,该长度的含义和Oracle的NUMBER类型的长度含
义不同,N值的大小完全不影响数值的存储范围,只影响有ZEROFILL时显示的长度。
8) 空字符串的含义
MySQL中字符串存在三种容易混淆的值:空字符串''、空值NULL和NULL字符串
'NULL',需要注意判断。和Oracle的最大区别为Oracle不区分空字符串''和空值NULL。
字符集
字符集UTF-8编码基本上表达我们日常所使用到的汉字、字母或字符,也能够兼容更多语种;其次,顺丰速运的研发项目所用编程语言为JAVA,而JAVA开发工具的语言也是默认为UTF-8;另外,顺丰速运多数项目的业务数据不需要区分数据的大小写,为此我们最终的建议:字符集统一为UTF-8,字符集校对规则为utf8_general_ci。
若研发项目中存在区分存储数据的字母大小的需求,则字符集统一为UTF-8,字符集校对规则为:utf8_bin。
采用UTF8编码时,对于中文汉字及符号,实际存储时占三个字节,而数值、字母和符号(注:英文输入法下),则只占一个字节。例如:
- CREATE TABEL gl_user(username VARCHAR(40));则username最多能存储40个字符;
- username存储’U-Cloud’ 则占用存储空间为:7个字节(注释:变长会额外增加1个字节或2个字节,此处实际占用存储空间为:8个字节);
特别说明:
随着移动互联网业务发展,越来越多的移动终端支持输入一些特殊符号,字符编码空间超过3个字节,则可使用4个字节编码的UTF8mb4。不区分字母大小的示例,如下:
- CREATE TABEL gl_user(username VARCHAR(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci);则username最多能存储40个字符,字符自身占用最大存储空间160个字节;