mysql支持的字段类型_MySQL支持的数据类型

整形类型

整型类型中，按照取值范围和存储方式不同，分为tinyint，smallint,mediumint,int和bigInt这五个类型。如果超出类型范围的操作，会发生”Out of range”错误提示。

对于整型数据,MySQL还支持在类型后面的小括号内指定显示宽度，例如int(5)表示当数值宽度小于5位的时候在数字前面填满宽度，如果不显示指定宽度则默认为int(11)。一般配合zerofill使用，顾名思义，zerofill就是用”0”填充的意思，也就是在数字位数不够的空间用“0”填满。

在id1和id2中都插入数值1，可以发现格式没有异常：

分别修改id1和id2的字段类型，加入zerofill约束

再查看表

可以发现，在数值前面用字符“0”填充了剩余的宽度。

如果设置了宽度限制后，插入了大于宽度限制的值，那么不会对插入的数据有任何影响，还是会按照类型的实际精度进行保存，这是宽度格式实际已经没有意义了，左边不会再填充任务的“0”字符

id2中显示了正确的数值，并没有受宽度限制影响

整数类型有一个属性：AUTO_INCREMENT。在需要产生唯一标识符或顺序值时，可利用此属性，这个属性只用于整数类型。AUTO_INCREMENT值一般从1开始，每行增加1。在插入NULL到一个AUTO_INCREMENT列时，MySQL插入一个比该列中当前最大值大1的值。一个表中最多只能有一个AUTO_INCREMENT，对于任何想要使用AUTO_INCREMENT的列，应该定义为NOT NULL,并定义为PRIMARY KEY或定义为UNIQUE键。

可按下列任何一种方式定义AUTO_INCREMENT列：

浮点型

对于小数的表示，MySQL分为两种方式：浮点数和定点数。浮点数包括float(单精度)和double(双精度)，而定点数(计算机表示数字的一种方式，参与运算的数的小数点位置固定不变。)则只有decimal一种表示。定点数在MySQL内部以字符串形式存放，比浮点数更精确，适合用来表示货币等精度高的数据。

定点数和浮点数都可以用类型名称后加“(M,D)”的方式来进行表示，“(M,D)”表示该值一共显示M位数字(整数位+小数位)，其中D位位于小数点后面，M和D又称为精度和标度。例如，定义为float(7,4)的一个列可以显示为-999.9999。MySQL保存值时进行四舍五入，因此如果在float(7,4)列内插入999.00009，近似结果是999.0001.值得注意的是，浮点数后面跟“(M,D)”的用法是非标准用法，如果要用于数据库的迁移，则最好不要这么使用。float和double在不指定精度时，默认会按照实际的精度(由实际的硬件和操作系统决定)来显示，而decimal在不指定精度时，默认的整数位为10，默认的小数位为0。

下面通过体格例子来比较float,double和decimal三者之间的不同。

(1)创建测试表，分别将id1，id2,id3字段设置为float(5,2),double(5,2)，decimal(5,2)；

向id1和id2字段中插入数据1.234，而id3字段中仍然插入1.23

可以发现，id1，id2,id3都插入了表t1，但是id1和id2由于标度的限制，四舍五入舍去了最后一位，数据变为了1.23

再来看看这个例子，都插入1.235测试看看是否MySQL对数值进行了四舍五入的处理

将id1,id2,id3字段的精度和标度全部去掉，再次插入数据1.23

可以发现id1,id2字段中可以正常插入数据，而id3字段的小数位被截断。

如果浮点数不写精度和标度，则会按照实际精度值显示，如果有精度和标度，则会自动将四舍五入的结果插入，系统不会报错，定点数如果不写精度和标度，则按照默认值decimal(10,0)来进行操作，并且如果数据超越了精度和标度值，系统则阶段数据

插入id3时，系统给出的警告，id3这一列的数据被截断

进制类型

对于BIT(位)类型，用于存放位字段值，BIT(M)可以用来存放多位二进制数，M范围从1~64，如果不写则默认为1位。对于位字段，直接使用SELECT命令将不会看到结果，可以用bin()(显示为二进制格式)或者hex()(显示为十六进制格式)函数进行读取。

创建一张表

插入数据和查看数据，可以发现select * 的结果为null

改用BIN()或者HEX()函数可以正常显示值

数据插入bit类型字段时，首先转换为二进制，如果位数允许，将成功插入，如果位数小于实际定义的位数，则插入失败，下面我们在t2表插入数字2,2的二进制码是“10”，而id2的定义是bit(1)，将无法插入

将id2类型修改为bit(2)后，重新插入，插入成功

日期时间类型

MySQL中有多种数据类型可以用于日期和时间的表示，不同的版本可能有所差异。

这些数据类型的主要区别如下。

如果要用来表示年月日，通常用DATE来表示。

如果要用来表示年月日时分秒，通常用DATETIME表示

如果只用来表示时分秒，通常用TIME来表示。

如果需要经常插入或者更新日期为当前系统时间，则通常使用TIMESTAMP来表示。TIMESTAMP值返回后显示为“YYYY-MM-DD HH:MM:SS”格式的字符串，显示宽度固定为19个字符，如果想要获得数字值，应在TIMESTAMP列添加+“0”。

如果只是表示年份，可以用YEAR来表示，它比DATE占用更少的空间。

每种日期时间类型都有一个有效值范围，如果超出这个范围，在默认的SQLMode下，系统会进行错误提示，并将以零值来进行存储。不用日期类型零值的表示如图3-3

创建一张表，用now()函数插入当前日期：

再创建一张测试表 ，字段为TIMESTAMP类型，

可以发现，系统给id自动创建了默认值CURRENT_TIMESTAMP(系统日期)

插入一个NULL值试试：

可以发现id正确插入了系统的日期。

注意：MySQL只给表中的一个TIMESTAMP字段设置默认值为系统日期，如果要为第二个字段设置TIMESTAMP，则系统会报错(MySQL5.7版本)

TIMESTAMP还有一个重要特点，就是和时区相关，当插入日期时，会先转换为本地时区后存放；而从数据库里面取出时，也同样需要将日期转换为本地时区后显示。这样，两个时区的用户看到的同一日期可能是不一样的。

日期类型的插入格式有很多，看下图

做一个测试，来说明如何采用不同的格式将日期“2018-01-17 10:16:20”插入到DATETIME列中。

字符串类型

MySQL中提供了多种对字符数据的存储类型，不同的版本可能有所差异。MySQL包括了CHAR,VARCHAR,BINARY,VARBINARY,BLOB,TEXT,ENUM和SET等多种字符串类型

CHAR和VARCHAR类型

CHAR和VARCHAR很类似，都用来保存MySQL中较短的字符串，二者的主要区别在于存储方式的不同：CHAR列的长度固定为创建表时生命的长度，长度可以为从0~255的任何值，而VARCHAR列中的值为可变长字符串，0~65535之间的值。在检索的时候，CHAR列删除了尾部的空格，而VARCHAR则保留这些空格。

创建测试表vc，并定义两个字段“v VARCHAR(4)”和“c CHAR(4)”：

同时插入字符串“ab ”

显示查询结果

可以发现，CHAR列最后的空格在操作时都已经被删除了，而VARCHAR依然保留空格。

再来做一个测试，如果插入的值超出了类型的范围

那么将会报错

CHAR和VARCHAR的区别

CHAR(M)定义的列的长度为固定的，M取值可以为0～255之间，当保存CHAR值时，在它们的右边填充空格以达到指定的长度。当检 索到CHAR值时，尾部的空格被删除掉。在存储或检索过程中不进行大小写转换。CHAR存储定长数据很方便，CHAR字段上的索引效率级高，比如定义 char(10)，那么不论你存储的数据是否达到了10个字节，都要占去10个字节的空间,不足的自动用空格填充。

VARCHAR(M)定义的列的长度为可变长字符串，M取值可以为0~65535之间，(VARCHAR的最大有效长度由最大行大小和使用 的字符集确定。整体最大长度是65,532字节)。VARCHAR值保存时只保存需要的字符数，另加一个字节来记录长度(如果列声明的长度超过255，则 使用两个字节)。VARCHAR值保存时不进行填充。当值保存和检索时尾部的空格仍保留，符合标准SQL。varchar存储变长数据，但存储效率没有 CHAR高。如果一个字段可能的值是不固定长度的，我们只知道它不可能超过10个字符，把它定义为 VARCHAR(10)是最合算的。VARCHAR类型的实际长度是它的值的实际长度+1。为什么”+1″呢？这一个字节用于保存实际使用了多大的长度。 从空间上考虑，用varchar合适；从效率上考虑，用char合适，关键是根据实际情况找到权衡点。

CHAR和VARCHAR最大的不同就是一个是固定长度，一个是可变长度。由于是可变长度，因此实际存储的时候是实际字符串再加上一个记录 字符串长度的字节(如果超过255则需要两个字节)。如果分配给CHAR或VARCHAR列的值超过列的最大长度，则对值进行裁剪以使其适合。如果被裁掉 的字符是空格，则会产生一条警告。如果裁剪非空格字符，则会造成错误(而不是警告)并通过使用严格SQL模式禁用值的插入。