MySQL数据类型 - 米扑博客

计算机互联网，要把现实世界中的各种信息转换成计算机能理解的东西，这些转换后的信息就形成了数据。

例如，某人的出生日期是“1996年5月20日”，他的身高是180厘米，银行存款1000万人民币等等。数据不仅包括中文、数字、字母、文字和其他特殊字符组成的文本形式的数据，而且还包括图形、图像、动画、影像、声音等多媒体数据。但使用最多、最基本的仍然是文本数据。

1. MySQL的数据类型

在MySQL中有如下几种数据类型：

1）数值型 （ tinyint、int、bigint、float、double ）

数值是诸如32 或153.4 这样的值。MySQL 支持科学表示法，科学表示法由整数或浮点数后跟“e”或“E”、一个符号（“+”或“-”）和一个整数指数来表示。

例如：1.24E+12 和23.47e-1 都是合法的科学表示法表示的数，而1.24E12 不是合法的，因为指数前的符号未给出。

浮点数由整数部分、一个小数点和小数部分组成。整数部分和小数部分可以分别为空，但不能同时为空。

数值前可放一个负号“-”以表示负值。

相关知识拓展

3.4e-38，3.4e+38中e表示什么

e是 exponent 的缩写，意为指数或幂，以10为底的指数。

例如：3.4e-38表示3.4×10^(-38)，是一般意义上的科学记数法。

C/C++等语言支持这种形式，表示一个字面量，方便计算机表示。

单精度（float）的范围是如何算出来的为 -3.4×10（38）~ 3.4×10（38）

单精度（float）和双精度（double）变量的范围与整数不同

1）整数型

整数的每个位只存放0和1，每位作为一个有效位出现，可以直接通过将二进制转换为十进制的方式来完成最大最小值的获得。例如：128 = 2^8

2）浮点型

浮点型的比较复杂，在IEEE754标准中进行了单精度浮点数(float)和双精度数浮点数(double)的定义。

float有32bit，double有64bit。它们的构成包括符号位、指数位、尾数位

这些位的构成如下（最低位是从 0 开始的）：

种类-------符号位-------------指数位----------------尾数位

float---第31位(占1bit)---第30-23位(占8bit)----第22-0位(占23bit)

double--第63位(占1bit)---第62-52位(占11bit)---第51-0位(占52bit)

用 s, e, f 来分别表示符号位、指数位、尾数位：

浮点数从最高位到最低位的表示 s(1位), e(8位), f(23位)

浮点数大小是：(-1)^s * 2^(e-127) * 1.f

float 和 double 取值范围主要看指数位的部分：

float的指数部分有8bit(2^8)，2^7=128，由于是有符号型，所以得到对应的指数范围 -128~127

double的指数部分有11bit(2^11)，2^10=1024，由于是有符号型，所以得到对应的指数范围 -1024~1023

由于float的指数部分对应的指数范围为-128~127，所以取值范围为：

-2^128 ~ 2^128，即约等于 -3.4E38 ~ +3.4E38 （E 表示指数幂的底数为10，即 3.4*10^38）

精度(有效数字)主要看尾数位：

float的尾数位是23bit，2^23 = 8388608，对应7~8位十进制数，所以有效数字有的编译器是7位，也有的是8位。

为什么浮点型比整数型处理速度会慢的多，主要是表示的方式不一样，浮点型转换比整数型转换的工作量大的多。

一个字节（Byte）的数据大小范围为什么是 -128~127

Byte字节是8位，最高位是符号位，最高位为0则是正数，最高位为1则是负数

如果一个数是正数，最大数则为：01111111，转为十进制为127，

如果一个数是负数，按照一般人都会觉得是11111111，转为十进制为-127，

但是：一个+0表示为：00000000，一个-0表示为：1000000，因为是0值，符号位不算在里面（0不分正负），所以就会有两个0，所以从一开始发明二进制的时候，就把-0规定为-128，如此二进制的补码就刚好在计算机中运作中吻合。（这是国内教材中的解释）

更详细解释请见米扑博客：Byte 8位的范围为何是-128~127，而不是-127~128

公式：计算一个数据类型的数据大小范围：-2^（字节数*8-1）~2^(字节数*8-1)-1

示例：

字节型（Byte）8bit ，数据范围 -2^7 ~ 2^7-1 ，即 -128 ~ 127

短整型（Short）16bit ，数据范围 -2^15 ~ 2^15-1 ，即 -32768 ~ 32767

浮点型（float）32bit ，双精度（double）64bit ，这些位的构成如下：

种类-------符号位-------------指数位----------------尾数位

float---第31位(占1bit)---第30-23位(占8bit)----第22-0位(占23bit)

double--第63位(占1bit)---第62-52位(占11bit)---第51-0位(占52bit)

2）字符（串）型 （ char、varchar、decimal ）

字符型（也叫字符串型，简称串）是诸如“Hello, world!”或“一个馒头引起的血案”这样的值，或者是电话号码87398413这样的值。既可用单引号也可用双引号将串值括起来。

初学者往往分不清数值87398143和字符串87398143的区别。都是数字啊，怎么一个要用数值型，一个要用字符型呢？关键就在于：数值型的87398143是要参与计算的，比如它是金融中的一个货款总额；而字符型的87398143是不参与计算的，只是表示电话号码，这样的还有街道号码、门牌号码等等，它们都不参与计算。

3）日期和时间型

日期和时间是一些诸如“2006-07-12”或“12:30:43”这样的值。

MySQL还支持日期/时间的组合，如“2006-07-12 12:30:43”。

4）NULL值

NULL表示未知值，比如填写表格中通讯地址不清楚，留空不填写，这就是NULL值。

我们用Create Table语句创建一个表（参看前面的章节），这个表中包含列的定义。

例如：创建一个joke表，这个表中有content和writer两个列：

定义一个列的语法如下：

其中列名由col_name 给出。列名可最多包含64个字符，字符包括字母、数字、下划线及美元符号。列名可以使用合法的任何符号（包括数字）开头。但列名不能完全由数字组成，因为那样可能使其与数据分不开。MySQL保留诸如SELECT、DELETE、CREATE、ALTER等关键词，这些词不能用做列名，但是函数名（如POS和MIN）是可以使用的。

列类型col_type表示列可存储的特定值。列类型说明符还能表示存放在列中的值的最大长度。对于某些类型，可用一个数值明确地说明其长度。而另外一些值，其长度由类型名蕴含。例如，CHAR(10) 明确指定了10个字符的长度，而TINYBLOB值隐含最大长度为255个字符。有的类型说明符允许指定最大的显示宽度（即显示值时使用多少个字符）。浮点类型允许指定小数位数，所以能控制浮点数的精度值为多少。

可以在列类型之后指定可选的类型说明属性，以及指定更多的常见属性。

属性起修饰类型的作用，并更改其处理列值的方式，属性有以下类型：

（1）专用属性用于指定列。例如，UNSIGNED 属性只针对整型，而BINARY属性只用于 CHAR 和 VARCHAR

（2）通用属性除少数列之外可用于任意列。可以指定NULL 或NOT NULL 以表示某个列是否能够存放NULL。还可以用DEFAULT，def_value 来表示在创建一个新行但未明确给出该列的值时，该列可赋予值def_value。def_value 必须为一个常量；它不能是表达式，也不能引用其他列。不能对BLOB 或TEXT 列指定缺省值。

如果想给出多个列的专用属性，可按任意顺序指定它们，只要它们跟在列类型之后、通用属性之前即可。类似地，如果需要给出多个通用属性，也可按任意顺序给出它们，只要将它们放在列类型和可能给出的列专用属性之后即可

NULL 更多知识与用法，请见米扑博客：MySQL 中的 NULL 和空字符('') 区别

2. MySQL的列（字段）类型

数据库中的每个表都是由一个或多个列（字段）构成的。

在用CREATE TABLE语句创建一个表时，要为每列（字段）指定一个类型。

列（字段）的类型比数据类型更为细化，它精确地描述了给定表列（字段）可能包含的值的种类，如是否带小数、是否文字很多。

MySQL有整数和浮点数值的列类型，如表1所示。整数列类型可以有符号也可无符号。

有一种特殊的属性允许整数列值自动生成，这对需要唯一序列或标识号的应用系统来说是非常有用的。

类型	说明
TINYINT	非常小的整数
SMALLINT	较小整数
MEDIUMINT	中等大小整数
INT	标准整数
BIGINT	较大整数
FLOAT	单精度浮点数
DOUBLE	双精度浮点数
DECIMAL	一个串的浮点数

上表1：数值列类型，每种数值类型的名称和取值范围如下表2：

类型说明	取值范围
TINYINT[(M)]	有符号值：-128 到127（- 2⁷ 到2⁷ - 1）无符号值：0到255（0 到2⁸ - 1）
SMALLINT[(M)]	有符号值：-32768 到32767（- 2¹⁵ 到2¹⁵ - 1）无符号值：0到65535（0 到2¹⁶ - 1）
MEDIUMINT[(M)]	有符号值：-8388608 到8388607（- 2²³ 到2²³ - 1 ）无符号值：0到16777215（0 到2²⁴ - 1）
INT[(M)]	有符号值：-2147683648 到2147683647（- 2³¹ 到2³¹- 1）无符号值：0到4294967295（0 到2³² - 1）
BIGINT[(M)]	有符号值：-9223372036854775808 到9223373036854775807（- 2⁶³到2⁶³-1）无符号值：0到18446744073709551615（0到2⁶⁴ – 1）
FLOAT[(M, D)]	最小非零值：±1.175494351E - 38
DOUBLE[(M,D)]	最小非零值：±2.2250738585072014E - 308
DECIMAL (M, D)	可变；其值的范围依赖于M 和 D

表2：数值列类型的取值范围

类型说明	存储需求
TINYINT[(M)]	1字节
SMALLINT[(M)]	2字节
MEDIUMINT[(M)]	3字节
INT[(M)]	4字节
BIGINT[(M)]	8字节
FLOAT[(M, D)]	4字节
DOUBLE[(M, D)]	8字节
DECIMAL (M, D)	M字节（MySQL < 3.23），M+2字节（MySQL > 3.23 ）

表3：数值列类型的存储需求

综上小结

php中int的最大值为 2147483647 (有符号数，通常情况，和平台有关)

MySQL 整型 int 的最大值可以看成两个

1）有符号的（默认）：2147483647

2）无符号的： 4294967295

无符号的设定是 unsigned ，有符号后，int及与之相关的最大值最小值都会变

类型	字节	最小值	最大值
		(带符号的/无符号的)	(带符号的/无符号的)
TINYINT	1	-128	127
TINYINT	1	0	255
SMALLINT	2	-32768	32767
SMALLINT	2	0	65535
MEDIUMINT	3	-8388608	8388607
MEDIUMINT	3	0	16777215
INT	4	-2147483648	2147483647
INT	4	0	4294967295
BIGINT	8	-9223372036854775808	9223372036854775807
BIGINT	8	0	18446744073709551615

通常在创建id的时候会给主键、unsigned 、auto_increment 然后其他表与该表的id字段链接如果int 大于2147483647 在链接表里如果字段不是有符号的，最大值只能2147483647，再链接的时候就会查不到数据

2.1 数值类型

MySQL提供了五种整型：

TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT

INT为INTEGER的缩写。这些类型在可表示的取值范围上是不同的。整数列可定义为UNSIGNED从而禁用负值；这使列的取值范围为0以上。各种类型的存储量需求也是不同的，取值范围较大的类型所需的存储量较大。

MySQL 提供三种浮点类型：

FLOAT、DOUBLE、DECIMAL

与整型不同，浮点类型不能是UNSIGNED的，其取值范围也与整型不同，这种不同不仅在于这些类型有最大值，而且还有最小非零值。最小值提供了相应类型精度的一种度量，这对于记录科学数据来说是非常重要的（当然，也有负的最大和最小值）。

在选择了某种数值类型时，应该考虑所要表示的值的范围，只需选择能覆盖要取值的范围的最小类型即可。选择较大类型会对空间造成浪费，使表不必要地增大，处理起来没有选择较小类型那样有效。

对于整型值，如果数据取值范围较小，如人员年龄或兄弟姐妹数，则TINYINT最合适。

MEDIUMINT 能够表示数百万的值并且可用于更多类型的值，但存储代价较大。

BIGINT 在全部整型中取值范围最大，而且需要的存储空间是表示范围次大的整型INT类型的两倍，因此只在确实需要时才用。

对于浮点值，DOUBLE占用FLOAT的两倍空间。除非特别需要高精度或范围极大的值，一般应使用只用一半存储代价的FLOAT型来表示数据。

在定义整型列时，可以指定可选的显示尺寸M。如果这样，M应该是一个1 到255的整数。它表示用来显示列中值的字符数。例如，MEDIUMINT(4)指定了一个具有4个字符显示宽度的MEDIUMINT列。如果定义了一个没有明确宽度的整数列，将会自动分配给它一个缺省的宽度。缺省值为每种类型的“最长”值的长度。如果某个特定值的可打印表示需要不止M个字符，则显示完全的值；不会将值截断以适合M个字符。

对每种浮点类型，可指定一个最大的显示尺寸M 和小数位数D。

M 的值应该取1 到255。D的值可为0 到30，但是不应大于M - 2（如果熟悉ODBC 术语，就会知道M 和 D 对应于ODBC 概念的“精度”和“小数点位数”）

M和D对FLOAT和DOUBLE 都是可选的，但对于DECIMAL是必须的。

在选项 M 和 D时，强烈推荐明确指定 M 和 D ！如果省略了它们，则使用缺省值，往往可能造成不准确，例如：float 默认没有小数点，四舍五入到了整数，造成了无法比较小数位。

2.2 字符串列类型

MySQL提供了几种存放字符数据的串类型，其类型如下：

类型名	说明
CHAR	定长字符串
VARCHAR	可变长字符串
TINYBLOB	非常小的BLOB（二进制大对象）
BLOB	小BLOB
MEDIUMBLOB	中等的BLOB
LONGBLOB	大BLOB
TINYTEXT	非常小的文本串
TEXT	小文本串
MEDIUMTEXT	中等文本串
LONGTEXT	大文本串
ENUM	枚举；列可赋予某个枚举成员
SET	集合；列可赋予多个集合成员

表4：字符串列类型

下表给出了MySQL 定义串值列的类型，以及每种类型的最大尺寸和存储需求。

对于可变长的列类型，各行的值所占的存储量是不同的，这等于实际存放在列中的值的长度。

这个实际长度在表中用 L 表示。

类型说明	最大尺寸	存储需求
CHAR(M)	M 字节	M 字节
VARCHAR(M)	M 字节	L + 1字节
TINYBLOB, TINYTEXT	2⁸- 1字节	L + 1字节
BLOB, TEXT	2¹⁶ - 1 字节	L + 2字节
MEDIUMBLOB, MEDIUMTEXT	2²⁴- 1字节	L + 3字节
LONGBLOB, LONGTEXT	2³²- 1字节	L + 4字节
ENUM(“value1”, “value2”, ...)	65535 个成员	1 或 2字节
SET (“value1”, “value2”, ...)	64个成员	1、2、3、4 或8字节

表5：串列类型最大尺寸及存储需求

L 以外所需的额外字节为存放该值的长度所需的字节数。MySQL 通过存储值的内容及其长度来处理可变长度的值。这些额外的字节是无符号整数。请注意，可变长类型的最大长度、此类型所需的额外字节数以及占用相同字节数的无符号整数之间的对应关系。例如，MEDIUMBLOB 值可能最多2²⁴ - 1字节长并需要3 个字节记录其结果。3 个字节的整数类型MEDIUMINT 的最大无符号值为2²⁴ - 1。这并非偶然。

2.3 日期时间列类型

MySQL 提供了几种时间值的列类型，它们分别是：DATE、DATETIME、TIME、TIMESTAMP、YEAR

下表给出了MySQL 为定义存储日期和时间值所提供的这些类型，并给出了每种类型的合法取值范围。

类型名	说明
DATE	“YYYY-MM-DD”格式表示的日期值
TIME	“hh:mm:ss”格式表示的时间值
DATETIME	“YYYY-MM-DD hh:mm:ss”格式
TIMESTAMP	“YYYYMMDDhhmmss”格式表示的时间戳值
YEAR	“YYYY”格式的年份值

表6：日期时间列类型

类型名	取值范围	存储需求
DATE	“1000-01-01”到“9999-12-31”	3字节
TIME	“-838:59:59”到“838:59:59”	3字节
DATETIME	“1000-01-01 00:00:00” 到“9999-12-31 23:59:59”	8字节
TIMESTAMP	19700101000000 到2037 年的某个时刻	4字节
YEAR	1901 到2155	1字节

表7：日前时间列类型的取值范围和存储需求

下面举个例子：

这个例子创建一个student表，这个表中有name字段，字符类型列，不允许NULL（空值）。

有Chinese、Maths和English三个整数类型列。还有个Birthday日期类型列。

CREATE TABLE student ( Name varchar(20) NOT NULL， Chinese TINYINT(3)， Maths TINYINT(3)， English TINYINT(3)， Birthday DATE )

MySQL 字段设计之实践

1、创建表

CREATE TABLE `test_field_type` (
  `t_id` int(11) NOT NULL DEFAULT '0',
  `t_memo` varchar(1000) DEFAULT '',
  `t_tinyint` tinyint(4) NOT NULL DEFAULT '0',
  `t_smallint` smallint(6) NOT NULL DEFAULT '0',
  `t_mediumint` mediumint(9) NOT NULL DEFAULT '0',
  `t_mediumint2` mediumint(20) NOT NULL DEFAULT '0',
  `t_int` int(11) NOT NULL DEFAULT '0',
  `t_bigint` bigint(20) NOT NULL DEFAULT '0',
  `t_bigint2` bigint(20) NOT NULL DEFAULT '0',
  `t_float` float NOT NULL DEFAULT '0',
  `t_float2` float(10,2) NOT NULL DEFAULT '0.00',
  `t_double` double NOT NULL DEFAULT '0',
  `t_double2` double(20,4) NOT NULL DEFAULT '0.0000',
  `t_decimel` decimal(10,0) NOT NULL DEFAULT '0',
  `t_decimel2` decimal(10,2) NOT NULL DEFAULT '0.00',
  `t_real` double NOT NULL DEFAULT '0',
  PRIMARY KEY (`t_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

创建表的结果图（点击看大图）：

2、插入数据

insert into mytest.test_field_type values 
(1, "1", 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1), 
(2, "1.2", 1.2, 1.2, 1.2, 1.2, 1.2, 1.2, 1.2, 1.2, 1.2, 1.2, 1.2, 1.2, 1.2, 1.2), 
(3, "1.5", 1.5, 1.5, 1.5, 1.5, 1.5, 1.5, 1.5, 1.5, 1.5, 1.5, 1.5, 1.5, 1.5, 1.5), 
(4, "1.8", 1.8, 1.8, 1.8, 1.8, 1.8, 1.8, 1.8, 1.8, 1.8, 1.8, 1.8, 1.8, 1.8, 1.8), 
(5, "2.0", 2.0, 2.0, 2.0, 2.0, 2.0, 2.0, 2.0, 2.0, 2.0, 2.0, 2.0, 2.0, 2.0, 2.0), 
(6, "2.5", 2.5, 2.5, 2.5, 2.5, 2.5, 2.5, 2.5, 2.5, 2.5, 2.5, 2.5, 2.5, 2.5, 2.5), 
(7, "123", 123, 123, 123, 123, 123, 123, 123, 123, 123, 123, 123, 123, 123, 123), 
(8, "12345", 127, 12345, 12345, 12345, 12345, 12345, 12345, 12345, 12345, 12345, 12345, 12345, 12345, 12345), 
(9, "1234567890", -128, -32768, -8388608, 8388607, 2147483647, -9223372036854775808, 9223372036854775807, 1234.567890, 1234.567890, 1234.567890, 1234.567890, 1234.567890, 1234.567890, 1234.567890);

插入数据时，会提示警告，警告内容如下：

21:35:56 insert into mytest.test_field_type values (1, "1", 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1), (2, "1.2", 1.2, 1.2, 1.2, 1.2, 1.2, 1.2, 1.2, 1.2, 1.2, 1.2, 1.2, 1.2, 1.2, 1.2), (3, "1.5", 1.5, 1.5, 1.5, 1.5, 1.5, 1.5, 1.5, 1.5, 1.5, 1.5, 1.5, 1.5, 1.5, 1.5), (4, "1.8", 1.8, 1.8, 1.8, 1.8, 1.8, 1.8, 1.8, 1.8, 1.8, 1.8, 1.8, 1.8, 1.8, 1.8), (5, "2.0", 2.0, 2.0, 2.0, 2.0, 2.0, 2.0, 2.0, 2.0, 2.0, 2.0, 2.0, 2.0, 2.0, 2.0), (6, "2.5", 2.5, 2.5, 2.5, 2.5, 2.5, 2.5, 2.5, 2.5, 2.5, 2.5, 2.5, 2.5, 2.5, 2.5), (7, "123", 123, 123, 123, 123, 123, 123, 123, 123, 123, 123, 123, 123, 123, 123), (8, "12345", 127, 12345, 12345, 12345, 12345, 12345, 12345, 12345, 12345, 12345, 12345, 12345, 12345, 12345), (9, "1234567890", -128, -32768, -8388608, 8388607, 2147483647, -9223372036854775808, 9223372036854775807, 1234.567890, 1234.567890, 1234.567890, 1234.567890, 1234.567890, 1234.567890, 1234.567890) 9 row(s) affected, 6 warning(s): 1265 Data truncated for column 't_decimel' at row 2 1265 Data truncated for column 't_decimel' at row 3 1265 Data truncated for column 't_decimel' at row 4 1265 Data truncated for column 't_decimel' at row 6 1265 Data truncated for column 't_decimel' at row 9 1265 Data truncated for column 't_decimel2' at row 9 Records: 9 Duplicates: 0 Warnings: 6 0.0012 sec

3、查询出数据

SELECT * FROM mytest.test_field_type;

mysql> SELECT * FROM mytest.test_field_type;
+------+------------+-----------+------------+-------------+--------------+------------+----------------------+---------------------+---------+----------+------------+------------+-----------+------------+------------+
| t_id | t_memo     | t_tinyint | t_smallint | t_mediumint | t_mediumint2 | t_int      | t_bigint             | t_bigint2           | t_float | t_float2 | t_double   | t_double2  | t_decimel | t_decimel2 | t_real     |
+------+------------+-----------+------------+-------------+--------------+------------+----------------------+---------------------+---------+----------+------------+------------+-----------+------------+------------+
|    1 | 1          |         1 |          1 |           1 |            1 |          1 |                    1 |                   1 |       1 |     1.00 |          1 |     1.0000 |         1 |       1.00 |          1 |
|    2 | 1.2        |         1 |          1 |           1 |            1 |          1 |                    1 |                   1 |     1.2 |     1.20 |        1.2 |     1.2000 |         1 |       1.20 |        1.2 |
|    3 | 1.5        |         2 |          2 |           2 |            2 |          2 |                    2 |                   2 |     1.5 |     1.50 |        1.5 |     1.5000 |         2 |       1.50 |        1.5 |
|    4 | 1.8        |         2 |          2 |           2 |            2 |          2 |                    2 |                   2 |     1.8 |     1.80 |        1.8 |     1.8000 |         2 |       1.80 |        1.8 |
|    5 | 2.0        |         2 |          2 |           2 |            2 |          2 |                    2 |                   2 |       2 |     2.00 |          2 |     2.0000 |         2 |       2.00 |          2 |
|    6 | 2.5        |         3 |          3 |           3 |            3 |          3 |                    3 |                   3 |     2.5 |     2.50 |        2.5 |     2.5000 |         3 |       2.50 |        2.5 |
|    7 | 123        |       123 |        123 |         123 |          123 |        123 |                  123 |                 123 |     123 |   123.00 |        123 |   123.0000 |       123 |     123.00 |        123 |
|    8 | 12345      |       127 |      12345 |       12345 |        12345 |      12345 |                12345 |               12345 |   12345 | 12345.00 |      12345 | 12345.0000 |     12345 |   12345.00 |      12345 |
|    9 | 1234567890 |      -128 |     -32768 |    -8388608 |      8388607 | 2147483647 | -9223372036854775808 | 9223372036854775807 | 1234.57 |  1234.57 | 1234.56789 |  1234.5679 |      1235 |    1234.57 | 1234.56789 |
+------+------------+-----------+------------+-------------+--------------+------------+----------------------+---------------------+---------+----------+------------+------------+-----------+------------+------------+
9 rows in set (0.00 sec)

查询结果如下图（点击看大图）：