MySQL全面瓦解3：数据类型介绍与分析

概述

MySQL支持很多数据类型，以便我们能在复杂的业务场景中支持各种各样的数据格式，存储适当的数据内容。我们在设计数据库时，正确的使用数据库类型对整个数据库的整洁和高效，会有很大的帮助。

目前常用的数据类型大致上可以分为4大类：整数类型、浮点数类型、字符串(字符)类型、日期/时间类型。详细如下

4大类	主要类型
整数类型	tinyint 、 smallint 、 mediumint 、 int 、 bigint
浮点数类型	float 、 double 、 decimal
字符串(字符)类型	char 、 varchar 、 tinyblob 、 blob 、 mediumblob 、 longblob 、tinytext 、 text 、 mediumtext 、 longtext
日期/时间类型	Date 、 DateTime 、 TimeStamp 、 Time 、 Year

 

数值类型

MySQL支持所有标准SQL数值数据类型,包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC)，还有近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION)，所以MySQL中数据类型是丰富且完整的。

而作为SQL标准的扩展，MySQL也支持整数类型TINYINT、MEDIUMINT和BIGINT。在下面的表格中，我们总结了5个整数类型的存储大小和取值范围。

类型	字节数	有符号值范围	无符号值范围	作用
TINYINT	1 byte	(-128，127)	(0，255)	小整数值
SMALLINT	2 bytes	(-32 768，32 767)	(0，65 535)	大整数值
MEDIUMINT	3 bytes	(-8 388 608，8 388 607)	(0，16 777 215)	大整数值
INT或INTEGER	4 bytes	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
BIGINT	8 bytes	(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值

 

我们创建表赋予的类型默认是有符号类型的，无符号的需要在类型需要后面跟上unsigned 标记，上面的数据范围是根据字节对应二进制换算，比如一个字节转化为十进制最大为255（11111111），最小为0（00000000），具体可以去查下二进制表示相关资料。

测试一下：

mysql> create table tinyintdemo(
  tiny TINYINT,
  tinyunsi TINYINT UNSIGNED
);
Query OK, 0 rows affected

mysql> insert into tinyintdemo values(-128,-128);
1264 - Out of range value for column 'tinyunsi' at row 1
mysql> insert into tinyintdemo values(-128,0);
Query OK, 1 row affected

mysql> insert into tinyintdemo values(255,255);
1264 - Out of range value for column 'tiny' at row 1
mysql> insert into tinyintdemo values(127,255);
Query OK, 1 row affected

 上面我们故意输入不正确的值，无符号下我们输入-128，默认有符号情况下我们输入255，都会提示 Out of range，超出范围。

mysql> select *  from  tinyintdemo;
+------+----------+
| tiny | tinyunsi |
+------+----------+
| -128 |        0 |
|  127 |      255 |
+------+----------+
2 rows in set

另外需注意的点：我们经常在创建表的时候定义字段为int(n)，比如int(11) , 或者bitint(19) , 其实这不是描述字段长度的意思，在int(n)中，int占据的字节是固定4个，所以他的范围也固定是在 (-2 147 483 648，2 147 483 647) 之间，

并不会因为n的长度是多少而改变，而n的目的是为了表实显示宽度用的，在显示宽度不足的时候补充0。

再测试一下：

mysql> CREATE TABLE lengthdemo (
       `nor` int,
       `norlen` int(8),
       `norfill` int(8) zerofill
);
Query OK, 0 rows affected

mysql> insert into lengthdemo values(12345,12345,12345);
Query OK, 1 row affected

mysql> select * from lengthdemo;
 
+-------+--------+---------+
| nor   | norlen | norfill |
+-------+--------+---------+
| 12345 |  12345 |00012345 |
+-------+--------+---------+
1 row in set

mysql> show create table lengthdemo;
+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                                                                                                   |
+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| lengthdemo | CREATE TABLE `lengthdemo` (
  `nor` int(11) DEFAULT NULL,
  `norlen` int(8) DEFAULT NULL,
  `norfill` int(8) unsigned zerofill DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

从这个执行结果中，我们可以总结如下：

1、n的值不管填写多少，int类型只占据4个字节。

2、如果设置了n的显示宽度之后，再加上unsigned zerofill，显示宽度不足的用0补足，超过则直接显示完整数值。

3、加 zerofill属性的字段，会默认为 unsigned 无符号类型。

浮点类型 

float数值类型表示单精度浮点数值，double数值类型表示双精度浮点数值，float和double都是浮点型，而decimal是定点型。浮点型和定点型可以用类型名称后加（M，D）来表示，M表示该值的总共长度，D表示小数点后面的长度，M和D又称为精度和标度。

float和double在不指定精度时，默认会按照实际的精度来显示，而DECIMAL在不指定精度时，默认整数为10，小数为0。 

类型	大小	有符号值范围	无符号值范围	作用
FLOAT	4 bytes	(-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 bytes	(-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的值	小数值

浮点型和定点型可以用类型名称后加(M,D)来表示,M表示该值的总共长度，D表示小数点后面的长度，M和D又称为精度和标度。

float和double在不指定精度时，默认会按照实际的精度来显示，而DECIMAL在不指定精度时，默认只取整数，小数丢弃。

同样的，我们来测试下三个类型的使用：

mysql> insert into accuracy values (1,1,1),(2.124,2.124,2.124),(3.125,3.125,3.125),(4.115,4.115,4.115),(5.136,5.136,5.136);
Query OK, 5 rows affected, 4 warnings (0.07 sec)
Records: 5  Duplicates: 0  Warnings: 4

mysql> select * from accuracy;
+------+------+------+
| a    | b    | c    |
+------+------+------+
| 1.00 | 1.00 | 1.00 |
| 2.12 | 2.12 | 2.12 |
| 3.12 | 3.12 | 3.13 |
| 4.12 | 4.12 | 4.12 |
| 5.14 | 5.14 | 5.14 |
+------+------+------+
5 rows in set (0.00 sec)

 从这个执行结果，总结如下：

1、c是decimal类型，采用的是四舍五入

2、a和b分别为float 和double，采用的是四舍六入五成双

说明下四舍六入五成双：5以下舍弃，5以上进位，遇到5的时候，如果5后面还有不为0的任何数字，进位，如果没有，需要检查5前面的数字，奇数进位，偶数丢弃，观察结果中 3.125和4.115，可以得到规律。

 

我们再将浮点类型的(M,D)精度和标度都去掉，结果如下： 

mysql> create table accuracy2(a float,b double,c decimal);
Query OK, 0 rows affected

mysql> insert into accuracy2 values (1,1,1),(1.23,1.23,1.23);
Query OK, 2 rows affected
Records: 2  Duplicates: 0  Warnings: 1

mysql> select * from accuracy2;
+------+------+---+
| a    | b    | c |
+------+------+---+
|    1 |    1 | 1 |
| 1.23 | 1.23 | 1 |
+------+------+---+
2 rows in set

总结如下：

如果浮点数float、double如果不写精度和标度，则会按照实际的结果显示，而decimal会将小数四舍五入，并且插入时发出警告信息。

float，double等非标准类型，在DB中保存的是近似值，而decimal则以字符串的形式保存准确的数值，比如银行、金融系统之类的对统计精度要求比较高的，建议采用decimal。 

日期和时间类型

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。

TIMESTAMP类型有专有的自动更新特性，将在后面描述。

类型	大小	值范围	格式	作用
DATE	3 bytes	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3 bytes	'-838:59:59'/'838:59:59'	HH:MM:SS	时间值或持续时间
YEAR	1 bytes	1901/2155	YYYY	年份值
DATETIME	8 bytes	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4 bytes	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 bytes	定长字符串
VARCHAR	0-65535 bytes	变长字符串
TINYBLOB	0-255 bytes	不超过 255 个字符的二进制字符串
TINYTEXT	0-255 bytes	短文本字符串
BLOB	0-65 535 bytes	二进制形式的长文本数据
TEXT	0-65 535 bytes	长文本数据
MEDIUMBLOB	0-16 777 215 bytes	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215 bytes	中等长度文本数据
LONGBLOB	0-4 294 967 295 bytes	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295 bytes	极大文本数据

 

注意点：

1、CHAR(N) 和 VARCHAR(N) 中N代表字符的个数，但并不代表字节个数，比如 CHAR(10) 代表可以存储 10 个字符。

2、CHAR 和VARCHAR类型类似，但它们保存和检索的方式不同，最大长度和是否尾部空格被保留等方面也不同，同时在存储或检索过程中不进行大小写转换。

如果存放的数据为固定长度的建议使用char类型，如：手机号码、身份证等固定长度的信息。

3、BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值。

4、BLOB是一个二进制大对象，可以容纳可变数量的数据，包含4 种类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB，区别是可容纳存储范围不同。

5、TEXT 类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型，可存储的最大长度不同，可根据实际业务场景的选择。  

MySQL、Java类型对照

MySQL Type Name	Return value ofGetColumnClassName	Returned as Java Class
BIT(1) (new in MySQL-5.0)	BIT	java.lang.Boolean
BIT( > 1) (new in MySQL- 5.0)	BIT	byte[]
TINYINT	TINYINT	java.lang.Boolean if the configuration property tinyInt1isBit is set to true (the default) and the storage size is 1, or java.lang.Integer if not.
BOOL, BOOLEAN	TINYINT	See TINYINT, above as these are aliases forTINYINT(1), currently.
SMALLINT[(M)] [UNSIGNED]	SMALLINT [UNSIGNED]	java.lang.Integer (regardless if UNSIGNED or not)
MEDIUMINT[(M)] [UNSIGNED]	MEDIUMINT [UNSIGNED]	java.lang.Integer, if UNSIGNED java.lang.Long
INT,INTEGER[(M)] [UNSIGNED]	INTEGER [UNSIGNED]	java.lang.Integer , if UNSIGNED java.lang.Long
BIGINT[(M)] [UNSIGNED]	BIGINT [UNSIGNED]	java.lang.Long , if UNSIGNED java.math.BigInteger
FLOAT[(M,D)]	FLOAT	java.lang.Float
DOUBLE[(M,B)]	DOUBLE	java.lang.Double
DECIMAL[(M[,D])]	DECIMAL	java.math.BigDecimal a
DATE	DATE	java.sql.Date Ja
DATETIME	DATETIME	java.sql.Timestamp
TIMESTAMP[(M)]	TIMESTAMP	java.sql.Timestamp
TIME	TIME	java.sql.Time
YEAR[(2|4)] 公	YEAR 众	If yearIsDateType configuration property is set to false, then the returned object type is java.sql.Short . If set to true (the default) then an object of type java.sql.Date (with the date set to January 1st, at midnight).
CHAR(M)	CHAR	java.lang.String (unless the character set for the column is BINARY, then byte[] is returned.
VARCHAR(M) [BINARY]	VARCHAR	java.lang.String (unless the character set for the column is BINARY, then byte[] is returned.
BINARY(M)	BINARY	byte[]
VARBINARY(M)	VARBINARY	byte[]
TINYBLOB	TINYBLOB	byte[]
TINYTEXT	VARCHAR	java.lang.String
BLOB	BLOB	byte[]
MySQL Type Name	Return value ofGetColumnClassName	Returned as Java Class
TEXT	VARCHAR	java.lang.String
MEDIUMBLOB	MEDIUMBLOB	byte[]
MEDIUMTEXT	VARCHAR	java.lang.String
LONGBLOB	LONGBLOB	byte[]
LONGTEXT	VARCHAR	java.lang.String
ENUM('value1','value2',...)	CHAR	java.lang.String
SET('value1','value2',...)	CHAR	java.lang.String

使用建议

不建议选择较大的数值范围，尽量选择合适的数据范围，越小的数据类型会更快，占用磁盘，内存和CPU缓存也更小。

越简单的类型执行的成本越低，比如整型比字符类型操作代价要小得多，很多固定范围的文本内容字段可以用整型表示。

尽量制定列为NOT NULL，有NULL的列值会使得索引、索引统计和值比较更加复杂。

浮点类型的建议统一选择decimal，精度会好很多。

记录时间的建议使用时间戳格式，存储方便，索引高效