MySQL基本数据类型分为以下几种
整型
实数
字符串
日期和时间
特殊字符NULL
无符号unsigned 使得整型上限提升一倍
优化数据类型,提高查询速度
MySQL中定义数据字段的类型对你数据库的优化是非常重要的。
MySQL支持多种类型,大致可以分为三类:数值、日期/时间和字符串(字符)类型。
MYSQL数据类型
数值类型
MySQL支持所有标准SQL数值数据类型。
这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC),以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION)。
关键字INT是INTEGER的同义词,关键字DEC是DECIMAL的同义词。
BIT数据类型保存位字段值,并且支持MyISAM、MEMORY、InnoDB和BDB表。
作为SQL标准的扩展,MySQL也支持整数类型TINYINT、MEDIUMINT和BIGINT。下面的表显示了需要的每个整数类型的存储和范围。
类型 | 大小 | 范围(有符号) | 范围(无符号) | 用途 |
---|---|---|---|---|
TINYINT | 1 字节 | (-128,127) | (0,255) | 小整数值 |
SMALLINT | 2 字节 | (-32 768,32 767) | (0,65 535) | 大整数值 |
MEDIUMINT | 3 字节 | (-8 388 608,8 388 607) | (0,16 777 215) | 大整数值 |
INT或INTEGER | 4 字节 | (-2 147 483 648,2 147 483 647) | (0,4 294 967 295) | 大整数值 |
BIGINT | 8 字节 | (-9 233 372 036 854 775 808,9 223 372 036 854 775 807) | (0,18 446 744 073 709 551 615) | 极大整数值 |
FLOAT | 4 字节 | (-3.402 823 466 E+38,-1.175 494 351 E-38),0,(1.175 494 351 E-38,3.402 823 466 351 E+38) | 0,(1.175 494 351 E-38,3.402 823 466 E+38) | 单精度 浮点数值 |
DOUBLE | 8 字节 | (-1.797 693 134 862 315 7 E+308,-2.225 073 858 507 201 4 E-308),0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308) | 0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308) | 双精度 浮点数值 |
DECIMAL | 对DECIMAL(M,D) ,如果M>D,为M+2否则为D+2 | 依赖于M和D的值 | 依赖于M和D的值 | 小数值 |
日期和时间类型
表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。
每个时间类型有一个有效值范围和一个"零"值,当指定不合法的MySQL不能表示的值时使用"零"值。
TIMESTAMP类型有专有的自动更新特性,将在后面描述。
类型 | 大小 (字节) | 范围 | 格式 | 用途 |
---|---|---|---|---|
DATE | 3 | 1000-01-01/9999-12-31 | YYYY-MM-DD | 日期值 |
TIME | 3 | '-838:59:59'/'838:59:59' | HH:MM:SS | 时间值或持续时间 |
YEAR | 1 | 1901/2155 | YYYY | 年份值 |
DATETIME | 8 | 1000-01-01 00:00:00/9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值 |
TIMESTAMP | 4 | 1970-01-01 00:00:00/2038 结束时间是第 2147483647 秒,北京时间 2038-1-19 11:14:07,格林尼治时间 2038年1月19日 凌晨 03:14:07 | YYYYMMDD HHMMSS | 混合日期和时间值,时间戳 |
字符串类型
字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。
类型 | 大小 | 用途 |
---|---|---|
CHAR | 0-255字节 | 定长字符串 |
VARCHAR | 0-65535 字节 | 变长字符串 |
TINYBLOB | 0-255字节 | 不超过 255 个字符的二进制字符串 |
TINYTEXT | 0-255字节 | 短文本字符串 |
BLOB | 0-65 535字节 | 二进制形式的长文本数据 |
TEXT | 0-65 535字节 | 长文本数据 |
MEDIUMBLOB | 0-16 777 215字节 | 二进制形式的中等长度文本数据 |
MEDIUMTEXT | 0-16 777 215字节 | 中等长度文本数据 |
LONGBLOB | 0-4 294 967 295字节 | 二进制形式的极大文本数据 |
LONGTEXT | 0-4 294 967 295字节 | 极大文本数据 |
CHAR 和 VARCHAR 类型类似,但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。
BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR,不同的是它们包含二进制字符串而不要非二进制字符串。也就是说,它们包含字节字符串而不是字符字符串。这说明它们没有字符集,并且排序和比较基于列值字节的数值值。
BLOB 是一个二进制大对象,可以容纳可变数量的数据。有 4 种 BLOB 类型:TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。
有 4 种 TEXT 类型:TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型,可存储的最大长度不同,可根据实际情况选择。
SQL数据类型优化
选择优化的数据类型
// 更小的通常越好
尽量选择使用可以正确存储数据的最小数据类型。更小的数据类型通常更快,
因为它们占用更少的磁盘、内存和 CPU 缓存,并且处理时需要的 CPU 周期也更少;
// 简单最好
整型比字符型要好,两个例子,应该使用 MySQL 内建的类型来存储日期而不是字符串;
使用整形存储 IP 地址。因为字符的校对、排序规则要复杂。
// 尽量避免 NULL
通常情况下最好指定列为 NOT NULL,除非真的需要存储 NULL 值;
如果查询中包含可为 NULL 的列,对 MySQL 来说更难优化,
因为可为 NULL 的列是的索引、索引统计和值比较,都更为复杂。
允许为 NULL 的列,存储空间上占用更多,同时 MySQL 还需要对它特殊处理。
整数类型
可以使用的几种整数类型:TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT
分别使用8,16,24,32,64位存储空间。
整数类型有可选的 UNSIGNED 属性,表示不允许负值,这大致可以使正数的上限提高一倍。
整数计算一般使用 64 位的 BIGINT 整数,即使 32 位环境也是如此(一些聚合函数是例外,他们使用DECIMAL或DOUBLE进行计算)。
MySQL 可以为整数类型指定宽度,例如 INT(11),对大多数应用这是没有意义的;
它并不会限制值的合法范围。
对于存储和计算来说,INT(1) 和 INT(20) 是相同的。
实数类型
FLOAT 和 DOUBLE 类型支持使用标准的浮点运算进行近似计算。
DECIMAL 类型用于存储精确的小数,MySQL 服务器自身实现了 DECIMAL 的高精度计算,
相对而言,CPU直接支持原生浮点计算,所以浮点计算明显更快。
浮点和 DECIMAL 类型都可以指定精度,对于 DECIMAL 可以指定小数点前后所允许的最大位数,
这会影响列的空间消耗。浮点类型在存储同样范围的值时,
通常比 DECIMAL 使用更少的空间,所以应该尽量只在对小数进行精确计算时才使用。
DECIMAL(18, 9) 小数点两点各存储 9 个数字,一共使用 9 个字节;
DECIMAL 可以存储的整数比 BIGINT 还要大;
浮点型以及 DECIMAL 在存取时,都需要额外的空间和计算的开销,
所以应该尽量只对小数进行精确计算时才使用 DECIAML;
如果数据量比较大的时候,可以考虑使用 BIGINT 代替 DECIAML。
// 这就是为什么微信的数据库(猜的因为接口都是分为单位)使用 BIGINT 来做处理,
目的就是为了节省 MySQL 计算带来的额外损耗;
字符串类型
VARCHAR 存储可变长字符串。 当列的更新很少,使用了像 UTF-8 这样复杂的字符集时,使用 VARCHAR 存储。
CHAR 类型是定长的,当存储 MD5 值,定长的值,或经常变更的数据时,用 CHAR 存储,因为这样不容易产生碎片。
VARCHAR 类型存储可变长字符串,他比定长类型更节省空间,VARCHAR 节省了存储空间,所以对性能也有帮助,但由于是变长的,
在 UPDATE 时可能使行变得比原来长,这就导致需要做额外的工作。
存储引擎存储 CHAR 或者 VARCHAR 值的方式在内存中和硬盘上可能不一样。
如果一个行占用的空间增长,并在业内没有更多足够的空间可以存储时,
MyISAM 存储引擎会将拆分成不同的片段存储;而 InnerDB 则需要分裂页放进页内才行;
BLOB 和 TEXT 类型
MySQL 把每个 BLOB 和 TEXT 值当作一个独立的对象处理。
两者都是为了存储很大数据而设计的字符串类型,分别采用二进制和字符方式存储。
ENUM 枚举类型
有时候可以使用枚举代替常用的字符串类型,枚举把一些不重复的字符串存储成一个预定义的集合。
枚举在保存时是(数字-字符串)的形式。
将可优化的数据类型字段改为 ENUM 枚举类型后,关联查询性能可提升将近一倍。(高性能MySQL书中列子暂不细说)
SELECT SQL_NO_CACHE COUNT(*) FROM tables
JOIN tables2 USING(day, account...);
日期和时间类型
MySQL 使用许多类型保存日期和时间值,例如 Year 和 Date,MySQL 能存储的最小时间粒度为秒。
MySQL 提供两种相似的日期类型:DATETIME 和 TIMESTAMP,
在某些场合一个比另一个工作的更好。
DATETIME 这个类型保存的最大值从1001到9999年,精度为秒。
TIMESTAMP 使用 4 个字节保持日期,默认 NOT NULL。
除了特殊行为之外,通常也应该尽量使用 TIMESTAMP 类型。
位类型
BIT 最大长度 64 个位。MySQL 把 BIT 当作字符串类型,而不是数字类型。
// 很多开发人员喜欢使用 BIT 作为 BOOL 类型来存储某些数据,true/false 这种,但是最好避免使用 BIT 类型。
如果想在一个 bit 的存储空间中存储一个 true/false 的话,建议使用一个可以为空的 CHAR(0) 类型。
同时也可以考虑使用 SET 类型,SET 类型 MySQL 内部是以一系列打包位的集合来表示的,
MySQL 有像 FIND_IN_SET 和 FIELD 这样的函数,方便在查询中使用。
他的主要缺点是改变列的代价太高,也无法在SET上通过索引查找。