MySQL基本数据类型

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上通过索引查找。