理解 MySQL 数据类型-FreeOA

理解 MySQL 数据类型

2013-07-16 15:47:00

阿炯

MySQL的数据类型

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

(1)数值型
数值是诸如32 或153.4 这样的值。mysql支持科学表示法，科学表示法由整数或浮点数后跟“e”或“e”、一个符号(“+”或“-”)和一个整数指数来表示。1.24e+12 和23.47e-1 都是合法的科学表示法表示的数。而1.24e12 不是合法的，因为指数前的符号未给出。

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

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

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

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

(3)日期和时间型
日期和时间是一些诸如“2016-07-12”或“12:30:43”这样的值。mysql还支持日期/时间的组合，如“2016-07-12 12:30:43”。

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

表的列名可最多包含64个字符，字符包括字母、数字、下划线及美元符号。列名可以名字中合法的任何符号(包括数字)开头。但列名不能完全由数字组成，因为那样可能使其与数据分不开。mysql保留诸如select、delete和create这样的词，这些词不能用做列名，但是函数名(如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 列指定缺省值。

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

列(字段)类型

数据库中的每个表都是由一个或多个列(字段)构成的。在用create table语句创建一个表时，要为每列(字段)指定一个类型。列(字段)的类型比数据类型更为细化，它精确地描述了给定表列(字段)可能包含的值的种类，如是否带小数、是否需要存储很多文字。具体详见下文：

数据类型概览

* 整数类型
tinyint：1字节
smallint：2字节
int或integer：4字节
bigint：8字节
float(m,n)：4字节 //不指定m,n有可能无法通过where条件查询
double：8字节
decimal(m,d)：m>d?m+2:d+2：m代表数据的宽度，d代表小数宽度

* 字符串类型
char：定长字符串
varchar：变长字符串
text：长文本数据
blob：二进制长文本数据

* 日期和时间类型
date：4字节 yyyy-mm-dd 日期
time：3字节 hh:mm:ss 时间
year：1字节 yyyy 年份
datetime：8字节 yyyy-mm-dd hh:mm:ss 混合日期时间
timestamp：4字节 yyyy-mm-dd hh:mm:ss 混合日期时间如果未明确赋值或赋值为null，则自动使用当前系统时间

* 复合类型
enum：只能使用集合中的一个值或null
set：只能使用集合中的多个值或null

---------------
数值类型

MySQL的数值数据类型可以大致划分为两个类别，一个是整数，另一个是浮点数或小数。许多不同的子类型对这些类别中的每一个都是可用的，每个子类型支持不同大小的数据，并且 MySQL 允许我们指定数值字段中的值是否有正负之分或者用零填补。

表列出了各种数值类型以及它们的允许范围和占用的内存空间。
类型-大小-范围(有符号)-范围(无符号)-用途

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的值-小数值
BIT(M)-1~8-BIT(1)-BIT(64)

类型	说明
tinyint	非常小的整数
smallint	较小整数
mediumint	中等大小整数
int	标准整数
bigint	较大整数
float	单精度浮点数
double	双精度浮点数
decimal	一个串的浮点数

表：数值列类型

每种数值类型的名称和取值范围如下表所示。

类型说明	取值范围
tinyint[(m)]	有符号值：-128 到127(- 27 到27 - 1) 无符号值：0到255(0 到28 - 1)
smallint[(m)]	有符号值：-32768 到32767(- 215 到215 - 1) 无符号值：0到65535(0 到21 6 - 1)
mediumint[(m)]	有符号值：-8388608 到8388607(- 22 3 到22 3 - 1 ) 无符号值：0到16777215(0 到22 4 - 1)
int[(m)]	有符号值：-2147683648 到2147683647(- 231 到231- 1) 无符号值：0到4294967295(0 到232 - 1)
bigint[(m)]	有符号值：-9223372036854775808 到9223373036854775807(- 263到263-1) 无符号值：0到18446744073709551615(0到264 – 1)
float[(m, d)]	最小非零值：±1.175494351e - 38
double[(m,d)]	最小非零值：±2.2250738585072014e - 308
decimal (m, d)	可变；其值的范围依赖于m 和d

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

各种类型值所需的存储量如下表所示。

类型说明	存储需求
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：数值列类型的存储需求

---------------
INT 类型

在 MySQL 中支持的 5 个主要整数类型是 TINYINT，SMALLINT，MEDIUMINT，INT 和 BIGINT。这些类型在很大程度上是相同的，只有它们存储的值的大小是不相同的。

MySQL 以一个可选的显示宽度指示器的形式对 SQL 标准进行扩展，这样当从数据库检索一个值时，可以把这个值加长到指定的长度。例如，指定一个字段的类型为 INT(6)，就可以保证所包含数字少于 6 个的值从数据库中检索出来时能够自动地用空格填充。需要注意的是，使用一个宽度指示器不会影响字段的大小和它可以存储的值的范围。

当我们需要对一个字段存储一个超出许可范围的数字，MySQL 会根据允许范围最接近它的一端截短后再进行存储。还有一个比较特别的地方是，MySQL 会在不合规定的值插入表前自动修改为 0。

UNSIGNED 修饰符规定字段只保存正值。因为不需要保存数字的正、负符号，可以在储时节约一个“位”的空间。从而增大这个字段可以存储的值的范围。

ZEROFILL 修饰符规定 0(不是空格)可以用来真补输出的值，使用这个修饰符可以阻止 MySQL 数据库存储负值，即自动增加'unsigned'属性。

整数类型还有另外一个属性：auto_increment，该属性仅属整数类型，依次递加一；一个表中只能有一个auto_increment列，且应该定义为not null，以及primary key或unique键。

---------------
FLOAT、DOUBLE 和 DECIMAL 类型

MySQL 支持的三个浮点类型是 FLOAT、DOUBLE 和 DECIMAL 类型。FLOAT 数值类型用于表示单精度浮点数值，而 DOUBLE 数值类型用于表示双精度浮点数值。

与整数一样，这些类型也带有附加参数：一个显示宽度指示器和一个小数点指示器。比如语句 FLOAT(7,3) 规定显示的值不会超过 7 位数字，小数点后面带有 3 位数字。

对于小数点后面的位数超过允许范围的值，MySQL 会自动将它四舍五入为最接近它的值，再插入它。FLOAT占用4字节，DOUBLE占用8字节，DECIMAL9M，D0占用M+2字节。

DECIMAL 数据类型用于精度要求非常高的计算中，这种类型允许指定数值的精度和计数方法作为选择参数。精度在这里指为这个值保存的有效数字的总个数，而计数方法表示小数点后数字的位数。比如语句 DECIMAL(7,3) 规定了存储的值不会超过 7 位数字，并且小数点后不超过 3 位。

忽略 DECIMAL 数据类型的精度和计数方法修饰符将会使 MySQL 数据库把所有标识为这个数据类型的字段精度设置为 10，计算方法设置为 0。这是Mysql的用法而非标准用法。

UNSIGNED 和 ZEROFILL 修饰符也可以被 FLOAT、DOUBLE 和 DECIMAL 数据类型使用，并且效果与 INT 数据类型相同。在传统的SQLMode下，不符合定义格式的记录无法插入的，在非传统情况下，记录会被截断。

---------------
BIT(位)类型
用于存入多位二进制数，M范围为1~64，默认为1位，且通过直接使用select看不到结果，可以使用bin()或hex()函数进行读取。BIT(M)占用1 - 8字节，bit型以二进制位进行存储 BIT(1)表示1位，最大支持64位也就是8个字节长度。查看BIT类型的字段可以使用 HEX函数以十六进制的方式查看，或者BIN函数以二进制方式查看。

小结
如果ZEROFILL指定给数值列,则MYSQL会自动添加UNSIGNED属性

整数或浮点类型均可指定AUTO_INCREMENT属性,当被赋值NULL或0时会自动设置成下一个序列值,AUTO_INCREMENT序列从1开始

MYSQL将DOUBLE与DOUBLE PRECISION等同,将REAL也与DOUBLE PRECISION等同

DECIMAL与NUMERIC用来存储精确数值数据,NUMERIC是DECIMAL的一种实现,MYSQL5.5将DECIMAL与NUMERIC存储为二进制格式

BIT数据类型用来存储比特数值,BIT(M)中M允许从1到64,位数不足时会自动左侧补0

SERIAL 等同于BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE

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 到3 0，但是不应大于m - 2(如果熟悉odbc 术语，就会知道m 和d 对应于odbc 概念的“精度”和“小数点位数”)。m和d对float和double 都是可选的，但对于decimal是必须的。在选项m 和d时，如果省略了它们，则使用缺省值。

---------------
字符串类型

MySQL 提供了 8 个基本的字符串类型，可以存储的范围从简单的一个字符到巨大的文本块或二进制字符串数据。
类型-大小-用途
CHAR-0~255字节-定长字符串

VARCHAR-0~255字节-变长字符串，值长度+1字节

TINYBLOB-0~255字节-不超过 255 个字符的二进制字符串，值长度+1字节

TINYTEXT-0~255字节-短文本字符串，值长度+2字节

BLOB-0~65535字节-二进制形式的长文本数据，值长度+2字节

TEXT-0~65535字节-长文本数据，值长度+2字节

MEDIUMBLOB-0~16777215字节-二进制形式的中等长度文本数据，值长度+3字节

MEDIUMTEXT-0~16777215字节-中等长度文本数据，值长度+3字节

LOGNGBLOB-0~4294967295字节-二进制形式的极大文本数据，值长度+4字节

LONGTEXT-0~4294967295字节-极大文本数据，值长度+4字节

VARBINARY-0~M的变长字节字串，值的长度+1字节-大二进制形式的文本数据

BINARY-0~M的定长字节字串

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

类型名	说明
char	定长字符串
varchar	可变长字符串
tinyblob	非常小的blob(二进制大对象)
blob	小blob
mediumblob	中等的blob
longblob	大blob
tinytext	非常小的文本串
text	小文本串
mediumtext	中等文本串
longtext	大文本串
enum	枚举；列可赋予某个枚举成员
set	集合；列可赋予多个集合成员

表：字符串列类型

下表给出了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字节

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

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

---------------
CHAR 和 VARCHAR 类型

CHAR 类型用于定长字符串，并且必须在圆括号内用一个大小修饰符来定义。这个大小修饰符的范围从 0-255。比指定长度大的值将被截短，而比指定长度小的值将会用空格作填补。

CHAR 类型可以使用 BINARY 修饰符。当用于比较运算时，这个修饰符使 CHAR 以二进制方式参于运算，而不是以传统的区分大小写的方式。

CHAR 类型的一个变体是 VARCHAR 类型。它是一种可变长度的字符串类型，并且也必须带有一个范围在 0-255 之间的指示器。CHAR 和 VARCHGAR 不同之处在于 MuSQL 数据库处理这个指示器的方式：CHAR 把这个大小视为值的大小，不长度不足的情况下就用空格补足。而 VARCHAR 类型把它视为最大值并且只使用存储字符串实际需要的长度(增加一个额外字节来存储字符串本身的长度)来存储值。所以短于指示器长度的 VARCHAR 类型不会被空格填补，但长于指示器的值仍然会被截短。

因为 VARCHAR 类型可以根据实际内容动态改变存储值的长度，所以在不能确定字段需要多少字符时使用 VARCHAR 类型可以大大地节约磁盘空间、提高存储效率。

VARCHAR 类型在使用 BINARY 修饰符时与 CHAR 类型完全相同。

---------------
TEXT 和 BLOB 类型

对于字段长度要求超过 255 个的情况下，MySQL 提供了 TEXT 和 BLOB 两种类型。根据存储数据的大小，它们都有不同的子类型。这些大型的数据用于存储文本块或图像、声音文件等二进制数据类型。

TEXT 和 BLOB 类型在分类和比较上存在区别。BLOB 类型区分大小写，而 TEXT 不区分大小写。大小修饰符不用于各种 BLOB 和 TEXT 子类型。比指定类型支持的最大范围大的值将被自动截短。

在排序时，只有max_sort_length个字节被使用，默认值是1024，如果想在不改变此变量的前提下，使更多的字节加入排序，可以彩用SUBSTRING()方法。

当查询结果中包含BLOB或TEXT实例列时，MYSQL是采用在磁盘上创建临时表的方式处理的，因为MEMORY引擎是不支持这两种类型的，因为磁盘的使用会导致性能下降，所以除非确实需要；否则在查询中不要包含TEXT或BLOB列，特别是不要使用如SELECT *，来查询所有列。

BLOB与TEXT的最大值决定于它们的类型，但是客户端与服务器之间的实际可传输的最大值却取决于空闲内存的数量及通信缓冲区的大小.。我们可能通过调整max_allowed_packet来增大缓冲，但需要在客户端与服务器端同时设置。

---------------
日期和时间类型

在处理日期和时间类型的值时，MySQL 带有 5 个不同的数据类型可供选择。它们可以被分成简单的日期、时间类型，和混合日期、时间类型。根据要求的精度，子类型在每个分类型中都可以使用，并且 MySQL 带有内置功能可以把多样化的输入格式变为一个标准格式。
类型-大小(字节)-范围-格式-用途
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 年某时)-YYYYMMDD HHMMSS-混合日期和时间值，时间戳

类型名	说明
date	“yyyy-mm-dd”格式表示的日期值
time	“hh:mm:ss”格式表示的时间值
datetime	“yyyy-mm-dd hh:mm:ss”格式
timestamp	“yyyymmddhhmmss”格式表示的时间戳值
year	“yyyy”格式的年份值

表：日期时间列类型

类型名	取值范围	存储需求
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字节

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

---------------
DATE、TIME 和 TEAR 类型

MySQL 用 DATE 和 TEAR 类型存储简单的日期值，使用 TIME 类型存储时间值，这些类型可以描述为字符串或不带分隔符的整数序列。如果描述为字符串，DATE 类型的值应该使用连字号作为分隔符分开，而 TIME 类型的值应该使用冒号作为分隔符分开。

需要注意的是，没有冒号分隔符的 TIME 类型值，将会被 MySQL 理解为持续的时间，而不是时间戳。

MySQL 还对日期的年份中的两个数字的值，或是 SQL 语句中为 TEAR 类型输入的两个数字进行最大限度的通译，因为所有 TEAR 类型的值必须用 4 个数字存储。MySQL 试图将 2 个数字的年份转换为 4 个数字的值，把在 00-69 范围内的值转换到 2000-2069 范围内，把 70-99 范围内的值转换到 1970-1979 之内。如果 MySQL 自动转换后的值并不符合我们的需要，请输入 4 个数字表示的年份。

---------------
DATEYIME 和 TIMESTAMP 类型

除了日期和时间数据类型，MySQL 还支持 DATEYIME 和 TIMESTAMP 这两种混合类型，它们可以把日期和时间作为单个的值进行存储。这两种类型通常用于自动存储包含当前日期和时间的时间戳，并可在需要执行大量数据库事务和需要建立一个调试和审查用途的审计跟踪的应用程序中发挥良好作用。

如果我们对 TIMESTAMP 类型的字段没有明确赋值，或是被赋与了 null 值，MySQL 会自动使用系统当前的日期和时间来填充它。它的存放范围有限(19700101080001~2038年某一天)，注意选择，当插入超出范围时溢出时使用'0000-00-00 00:00:00'进行填充。

如果经常插入或更新日期为当前系统时间，则通常使用timestamp来表示，它的值返回后显示为"yyyy-mm-dd hh:mm:ss"格式的定长为19字符串，如果想取得数字，则可使用该列+0。

Mysql只给表中的第一个timestamp字段设置默认值为系统日期(current_timestamp)且在记录修改更改时自动记录当前时间，第二个timestamp字段默认值设为0。
`ts1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`ts2` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'

timestamp是与时区相关的，当插入日期时，会转换为本地时区后存放；当从库里读取时，也同样将记录转换为本地时区后显示。因此不同时区的用户看到的同一日期可能是不一样的。
mysql> show variables like 'time_zone';
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| time_zone | SYSTEM |
+---------------+--------+

mysql> set time_zone='+9:00';
mysql> alter table tstamp modify ts2 datetime;
mysql> insert into tstamp values(3,now(),now());
mysql> select * from tstamp;

| 3 | 2013-07-16 16:14:43 | 2013-07-16 15:14:43 |

可见前者快了一个小时。

小结
MYSQL会尝试从各种格式的输入中解析日期与时间，但还是有格式限制的

只有两位数的YEAR，会让MYSQL感到迷惑，所以MYSQL会尝试作下面的解析：在70～99之间的年会被解析成：1970～1999，在00～69之间的年会被解析成2000～2069

MYSQL在解析日期时，日期的输入顺序必须为：year-month-day,否则无法正确解析

默认情况下，MYSQL会将超出范围或不合法的日期与时间，转换成0，但对于超出范围的TIME，会将其归整到一个恰当的时间点上。下面是“0”格式的格式，前提是没有设置NO_ZERO_DATE,否则会抛出警告

DATEIME，DATE与TIMESTAMP的区别在于所表示的日期时间范围不同：DATETIME是 p, li { white-space: pre-wrap; } ：'1000-01-01 00:00:00' to '9999-12-31 23:59:59'，DATE是：‘1000－01－01’到‘9999－12－31’，而TIMESTAMP是：‘1970－01－01 00：00：01’UTC到‘2038－01－19 03：14：07’UTC(随MYSQL的版本不同而改变)

MYSQL识别日期时间时遵循相对“松散”的格式，即不关心日期日间之间的分隔符是什么，有或没有，只要日期正确，都可以识别，如：‘10－12－04 21：50：59’，‘10.12.04 21+50+59’，‘10/12/04 21*50^59’,‘101204215059’，都是一样的，都可以被正确识别出来。

TIMESTAMP 类型会自动初始化及更新. TIMESTAMP默认值为CURRENT_TIMESTAMP,除非其被指定了其它默认值,参考如下:
自动初始化与更新 ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

自动初始化 ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP

自动更新 ts TIMESTAMP DEFAULT 0 NO UPDATE CURRENT_TIMESTAMP

无 ts TIMESTAMP DEFAULT 0

---------------
复合类型

MySQL 还支持两种复合数据类型 ENUM 和 SET，它们扩展了 SQL 规范。虽然这些类型在技术上是字符串类型，但是可以被视为不同的数据类型。一个 ENUM 类型只允许从一个集合中取得一个值；而 SET 类型允许从一个集合中取得任意多个值。

---------------
ENUM 类型

ENUM 类型因为只允许在集合中取得一个值，有点类似于单选项。在处理相互排拆的数据时容易让人理解，比如人类的性别。ENUM 类型字段可以从集合中取得一个值或使用 null 值，除此之外的输入将会使 MySQL 在这个字段中插入一个空字符串。另外如果插入值的大小写与集合中值的大小写不匹配，MySQL 会自动使用插入值的大小写转换成与集合中大小写一致的值。

ENUM 类型在系统内部可以存储为数字，并且从 1 开始用数字做索引。一个 ENUM 类型最多可以包含 65536 个元素，其中一个元素被 MySQL 保留，用来存储错误信息，这个错误值用索引 0 或者一个空字符串表示。

MySQL 认为 ENUM 类型集合中出现的值是合法输入，除此之外其它任何输入都将失败。这说明通过搜索包含空字符串或对应数字索引为 0 的行就可以很容易地找到错误记录的位置。它是忽略大小写的，在存储时会转换为大写，当插入的值不在其定义的范围时，将不会产生警告而是使用enum第一个成员来做为插入值。

对1~255个成员需要1个字节存储，对于255-65535个成员时需要2个字节存储。

---------------
SET 类型

SET 类型与 ENUM 类型相似但不相同，SET 类型可以从预定义的集合中取得任意数量的值。并且与 ENUM 类型相同的是任何试图在 SET 类型字段中插入非预定义的值都会使 MySQL 插入一个空字符串。如果插入一个即有合法的元素又有非法的元素的记录，MySQL 将会保留合法的元素，除去非法的元素。

一个 SET 类型最多可以包含 64 项元素。在 SET 元素中值被存储为一个分离的“位”序列，这些“位”表示与它相对应的元素。“位”是创建有序元素集合的一种简单而有效的方式。并且它还去除了重复的元素，所以 SET 类型中不可能包含两个相同的元素。

希望从 SET 类型字段中找出非法的记录只需查找包含空字符串或二进制值为 0 的行。它的存储大小与元素个数也是相关的：
1~8成员集合：1字节
9~16：2字节
17~24：3字节
25~32：4字节
33~64：8字节

对插入的值将会支重，如('a,ba')与('a,b')是相同的。

MySQL中存IP地址的数据类型

IP地址的格式
IPv4地址由四个数字组成，每个数字之间用句点分隔。例如，192.168.0.1就是一个IP地址。每个数字都有一个范围从0到255。因此IP地址的二进制表示形式是一个32位的数字，其中每个八位表示一个数字。在MySQL中，有几种数据类型可以用来存储IP地址。以下是一些常用的数据类型：

CHAR(n)/VARCHAR(n)
CHAR和VARCHAR是常见的字符数据类型，它们都可以用来存储IP地址。我们可以使用CHAR(15)或VARCHAR(15)来存储一个IP地址，因为它们是由15个字符组成的。虽然这种方法很简单，但是它会占用较大的存储空间，并且在查询时也会变慢。因此，这种方法不太适合存储大量的IP地址。

INT UNSIGNED
INT UNSIGNED是一种整数数据类型，它可以用来存储IP地址的十进制表示形式。可以将IP地址转换为32位无符号整数，然后将其存储在INT UNSIGNED类型的列中。例如IP地址192.168.0.1的十进制表示形式为3232235521。虽然这种方法占用的存储空间较小，并且在查询时速度较快，但它不太直观。必须使用一些函数来将IP地址转换为十进制表示形式，或者将十进制表示形式转换回IP地址。这种方法也不太适合存储IPv6地址，因为IPv6地址是一个128位的数字，不能存储在INT UNSIGNED类型的列中。INET_ATON()将IP转换成整数。
SELECT INET_ATON('127.0.0.1');
2130706433

BINARY(4)/VARBINARY(4)
BINARY和VARBINARY是二进制数据类型，它们可以用来存储IP地址的二进制表示形式。可以将IP地址转换为一个长度为4字节的二进制数据，然后将其存储在BINARY(4)或VARBINARY(4)类型的列中。例如IP地址192.168.0.1的二进制表示形式为11000000 10101000 00000000 00000001，这种方法占用的存储空间较小且在查询时速度也很快。此外它也适用于存储IPv6地址，因为IPv6地址是一个128位的数字，可以用长度为16字节的BINARY或VARBINARY类型的列来存储。