字段类型

整数类型

mysql有如下几种整数类型

类型（默认有符号）	大小	范围（有符号）	范围（无符号）	描述
TINYINT	1字节	[-128，127]	[0，255]	迷你整型
SMALLINT	2字节	[-32768，32767)]	[0，65535]	小整型
MEDIUMINT	3字节	[-8388608，8388607]	[0，16777215]	中整型
INT或INTEGER	4字节	[-2147483648，2147483647]	[0，4294967295]	标准整型
BIGINT	8字节	[-9233372036854775808，9223372036854775807]	[0，18446744073709551615]	大整型

创建表的时候如果添加整型字段，默认情况是有符号整型（可以存储负值），如果想要添加无符号的整型字段（不可以存储负值），需要使用unsigned这个字段属性

1 2 3 4

mysql> CREATE TABLE my_int(     ->   num tinyint unsigned     -> ); Query OK, 0 rows affected (0.04 sec)

我们来查看一下表结构

1 2 3 4 5 6 7

mysql> DESC my_int; +-------+---------------------+------+-----+---------+-------+ | Field | Type                | Null | Key | Default | Extra | +-------+---------------------+------+-----+---------+-------+ | num   | tinyint(3) unsigned | YES  |     | NULL    |       | +-------+---------------------+------+-----+---------+-------+ 1 row in set (0.00 sec)

字段类型成功设置为了“无符号迷你整型”

但这时我们又发现了一个问题，就是字段类型tinyint后面跟着一个“(3)”，这个“(3)”表示什么含义呢？

其实这表示的是“显示长度”，显示长度是指数据（本例中是迷你整型）在显示的时候，最多会占几位（包括符号位）

无符号tinyint类型可表示的整型范围为0～255，又不能表示负数不用考虑负号，因此最多占3位

如果是有符号迷你整型，它的表示范围是-128～127，可以表示负数需要考虑负号，因此最多占4位,tinyint后会跟一个“(4)”

我们来验证一下

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

mysql> DESC my_int; +-------+---------------------+------+-----+---------+-------+ | Field | Type                | Null | Key | Default | Extra | +-------+---------------------+------+-----+---------+-------+ | num   | tinyint(3) unsigned | YES  |     | NULL    |       | +-------+---------------------+------+-----+---------+-------+ 1 row in set (0.00 sec)   mysql> ALTER TABLE my_int MODIFY num tinyint; Query OK, 0 rows affected (0.09 sec) Records: 0  Duplicates: 0  Warnings: 0   mysql> DESC my_int; +-------+------------+------+-----+---------+-------+ | Field | Type       | Null | Key | Default | Extra | +-------+------------+------+-----+---------+-------+ | num   | tinyint(4) | YES  |     | NULL    |       | +-------+------------+------+-----+---------+-------+ 1 row in set (0.00 sec)

可以看到确实如此

还有一点要注意的就是这个“显示长度”是可以手动指定的，而且不受字段取值范围限制，当然指定为负数或很大的正整数是会报错的，指定为0会重置为默认值，所以我们一般情况下都是指定一个正常的正整数。

1 2 3 4 5 6 7 8 9 10 11

mysql> ALTER TABLE my_int MODIFY num tinyint(5); Query OK, 0 rows affected (0.01 sec) Records: 0  Duplicates: 0  Warnings: 0   mysql> DESC my_int; +-------+------------+------+-----+---------+-------+ | Field | Type       | Null | Key | Default | Extra | +-------+------------+------+-----+---------+-------+ | num   | tinyint(5) | YES  |     | NULL    |       | +-------+------------+------+-----+---------+-------+ 1 row in set (0.00 sec)

当你指定的显示长度小于数据实际长度的时候，表中数据仍然会按照数据实际长度显示，而且显示长度只是代表了数据最大可以达到指定的长度，但是不会自动满足到指定长度，如果想要数据显示的时候自动满足你指定的长度（不够就左边补0），那么还需要给字段增加一个zerofill属性才可以

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

mysql> ALTER TABLE my_int MODIFY num tinyint(500); ERROR 1439 (42000): Display width out of range for column 'num' (max = 255) mysql> DESC my_int; +-------+------------+------+-----+---------+-------+ | Field | Type       | Null | Key | Default | Extra | +-------+------------+------+-----+---------+-------+ | num   | tinyint(5) | YES  |     | NULL    |       | +-------+------------+------+-----+---------+-------+ 1 row in set (0.00 sec)   mysql> ALTER TABLE my_int MODIFY num tinyint(3) zerofill; Query OK, 0 rows affected (0.08 sec) Records: 0  Duplicates: 0  Warnings: 0   mysql> DESC my_int; +-------+------------------------------+------+-----+---------+-------+ | Field | Type                         | Null | Key | Default | Extra | +-------+------------------------------+------+-----+---------+-------+ | num   | tinyint(3) unsigned zerofill | YES  |     | NULL    |       | +-------+------------------------------+------+-----+---------+-------+ 1 row in set (0.00 sec)

我们发现当给字段指定zerofill属性的时候，系统会自动给字段同时添加unsigned属性，这也就说明zerofill只能给无符号整型使用，负数是不能是使用zerofill的

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

mysql> ALTER TABLE my_int MODIFY num tinyint(4) zerofill; Query OK, 0 rows affected (0.08 sec) Records: 0  Duplicates: 0  Warnings: 0   mysql> DESC my_int; +-------+------------------------------+------+-----+---------+-------+ | Field | Type                         | Null | Key | Default | Extra | +-------+------------------------------+------+-----+---------+-------+ | num   | tinyint(4) unsigned zerofill | YES  |     | NULL    |       | +-------+------------------------------+------+-----+---------+-------+ 1 row in set (0.00 sec)   mysql> INSERT INTO my_int VALUES(1); Query OK, 1 row affected (0.08 sec)   mysql> SELECT * FROM my_int; +------+ | num  | +------+ | 0001 | +------+ 1 row in set (0.01 sec)   mysql> INSERT INTO my_int VALUES(-1); ERROR 1264 (22003): Out of range value for column 'num' at row 1

---

小数类型

mysql中将小数又分为两种类型：浮点型和定点型

浮点型

浮点型又称之为精度类型：是一种有可能丢失精度的数据类型，数据有可能不那么准确（尤其是在超出精度范围的时候）

浮点型之所以能够存储较大的数值（不精确），原因就是利用存储数据的位来存储指数。

类型	数符(s)	阶码(E)	尾数(M)	总位数	有效数字	数值范围
Float	1	8	23	32	6～7	-3.4*10^38  ～  +3.4*10^38
Double	1	11	52	64	15～16	-1.7*10^-308  ～  1.7*10^308

这其中数符来存储正负，阶码来存储指数，尾数存储有效数字

Float

又称之为单精度浮点型：系统提供4个字节用来存储数据，但是能表示的数据范围比整型大的多，大概是10^38，只能保证大概7个左右的精度（如果数据在7位数以内，那么基本是准确的，但是如果超过7位数，那么就是不准确的）

基本语法
Float：表示不指定小数位的浮点数
Float(M,D)：表示一共存储M个有效数字，其中小数部分占D位，例如Float(10,2)表示整数部分为8位，小数部分为2位

创建表示例

1 2 3 4 5 6 7 8 9 10 11 12 13 14

mysql> CREATE TABLE my_float(     ->   f1 float,     ->   f2 float(10,2)     -> ); Query OK, 0 rows affected (0.30 sec)   mysql> DESC my_float; +-------+-------------+------+-----+---------+-------+ | Field | Type        | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | f1    | float       | YES  |     | NULL    |       | | f2    | float(10,2) | YES  |     | NULL    |       | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.07 sec)

存入合法数据示例

1 2	mysql> INSERT INTO my_float VALUES(123.123,12345678.90); Query OK, 1 row affected (0.01 sec)

我们发现存入数据成功，现在来查看一下插入的数据

1 2 3 4 5 6 7

mysql> SELECT * FROM my_float; +---------+-------------+ | f1      | f2          | +---------+-------------+ | 123.123 | 12345679.00 | +---------+-------------+ 1 row in set (0.04 sec)

咦？我们明明存入的是12345678.90，怎么查看的时候变成了12345679.00？

仔细思考一下便明白，前面已经提到过float类型的数据有效数字（代表精确度）是6～7位，也就是说从第7位开始就不一定准确了，所以才产生了上面的结果

注意：如果数据精度丢失，那么浮点型是按照四舍五入的方式进行计算的

我们来尝试插入一个超过设定位数的数据观察会有什么结果

1 2	mysql> INSERT INTO my_float VALUES(123.1234567,123456789.00); ERROR 1264 (22003): Out of range value for column 'f2' at row 1

果然系统提示插入数据超出范围，因为我们创建表时指定第二个字段是float(10,2)，意味着整数部分不能插入超过8位的数字

我们在来尝试插入一个特殊的数字，这个特殊的数字长度刚好满足条件，但是会超出精度，例如我们插入“99999999.99”，这个数字整数部分8位，小数部分2位，刚好满足条件，但是float类型的精度不够8位，此时有效数字后面的数字会采用“四舍五入”的原则进行舍入，舍入后数字整数部分变为9位，不再满足条件，我们来观察一下结果

1 2 3 4 5 6 7 8 9 10 11

mysql> INSERT INTO my_float VALUES(123.1234567,99999999.99); Query OK, 1 row affected (0.04 sec)   mysql> SELECT * FROM my_float; +---------+--------------+ | f1      | f2           | +---------+--------------+ | 123.123 |  12345679.00 | | 123.123 | 100000000.00 | +---------+--------------+ 2 rows in set (0.00 sec)

我们发现竟然可以插入而且显示正常，这就说明：用户不能插入直接超过指定的整数部分长度的数据，但是如果这是由系统自动进位导致，那么系统可以承担

其实浮点数还可以采用科学计数法来存储数据

1 2 3 4 5 6 7 8 9 10 11 12

mysql> INSERT INTO my_float VALUES(123.123,10e5); Query OK, 1 row affected (0.01 sec)   mysql> SELECT * FROM my_float; +---------+--------------+ | f1      | f2           | +---------+--------------+ | 123.123 |  12345679.00 | | 123.123 | 100000000.00 | | 123.123 |   1000000.00 | +---------+--------------+ 3 rows in set (0.00 sec)

浮点数的应用：通常是用来保存一些数量特别大，大到可以不用那么精确的数据

Double

Double又称之为双精度浮点型：系统用8个字节来存储数据，表示的范围更大，10^308次方，但是精度也只有15位左右。其它特性可以参考float

定点数

定点数：能够保证数据精确的小数（小数部分可能不精确，超出长度会四舍五入），整数部分一定精确

Decimal

Decimal定点数：系统自动根据存储的数据来分配存储空间，每大概9个数就会分配四个字节来进行存储，同时小数和整数部分是分开的

基本语法
Decimal(M,D)：M表示总长度，最大值不能超过65，D代表小数部分长度，最长不能超过30

我们分别创建一个浮点型字段和定点型字段用来观察它们的区别

1 2 3 4 5

mysql> CREATE TABLE my_decimal(     ->   f1 float(10,2),     ->   d1 decimal(10,2)     -> ); Query OK, 0 rows affected (0.13 sec)

我们插入超出float有效数字范围的测试数据1：“12345678.90”

1 2 3 4 5 6 7 8 9 10

mysql> INSERT INTO my_decimal VALUES(12345678.90,12345678.90); Query OK, 1 row affected (0.01 sec)   mysql> SELECT * FROM my_decimal; +-------------+-------------+ | f1          | d1          | +-------------+-------------+ | 12345679.00 | 12345678.90 | +-------------+-------------+ 1 row in set (0.00 sec)

我们插入可能会导致进位使整数部分位数超出指定位数的测试数据2：“99999999.99”

1 2 3 4 5 6 7 8 9 10

mysql> INSERT INTO my_decimal VALUES(99999999.99,99999999.99); Query OK, 1 row affected (0.01 sec)   mysql> SELECT * FROM my_decimal; +--------------+-------------+ | f1           | d1          | +--------------+-------------+ |  12345679.00 | 12345678.90 | | 100000000.00 | 99999999.99 | +--------------+-------------+

由上面两个测试结果我们可以得出结论：定点型数据不会由于有效数字范围要进位导致整数部分不准确

定点数的应用：如果涉及到钱的时候有可能使用定点数

---

时间日期类型

Date

日期类型：系统使用3个字节来存储数据，对应的格式为：YYYY-mm-dd，能表示的范围是从1000-01-01～9999-12-31，初始值为
0000-00-00

Time

时间类型：能够表示某个指定的时间，系统提供3个字节来存储，对应的格式为：HH:mm:ss，但是mysql中的time类型能够表示时间范围要大的多，能表示从-838:59:59～838:59:59，在mysql中具体的用处是用来描述时间段

Datetime

日期时间类型：就是将前面的date和time合并起来，表示的时间，使用8个字节存储数据，格式为YYYY-mm-dd HH:mm:ss，能表示的区间1000-01-01 00:00:00到9999-12-31 23:59:59，其可以为0值：0000-00-00 00:00:00

Timestamp

时间戳类型：时间戳是指格林威治时间1970年01月01日00时00分00秒(北京时间1970年01月01日08时00分00秒)起至现在的总秒数，使用4个字节存储数据，mysql中的时间戳只是表示从格林威治时间开始，但是其格式依然是YYYY-mm-dd HH:mm:ss

Year

年类型：占用1个字节来保存，能表示1901～2155年，但是year有两种数据插入方式：0～99和四位数的具体年

时间日期类型数据的特性

创建对应的时间日期类型的数据表

插入一个示例数据，查看结果

year类型既可以采用2位数的数据插入，也可以采用4位数的年份插入。我们发现year类型我们插入“69”显示的是“2069”

我们尝试采用4位数的年份插入

year进行两位数插入的时候，有一个区间划分，临界点为69和70：当输入69以下，那么系统时间为“20+数字”，如果是70以上，那配系统时间为“19+数字”

现在插入“70”验证一下

time类型特殊性：本质是用来表示时间区间，能表示的范围比较大

在进行时间类型（time）录入的时候还可以使用一个简单的日期代替时间，在时间格式之前加一个空格，然后指定一个数字（可以是负）系统会自动将该数字转换成“天数*24小时”，再加上后面的时间。

对于timestamp和datetime类型，可以给它们添加一个特殊的字段属性“ON UPDATE CURRENT_TIMESTAMP”，那这个属性有什么作用呢？ON UPDATE CURRENT_TIMESTAMP翻译过来就是“在更新时间戳时”，它的作用顾名思义，就是将本字段的值在更新本条记录（即使更新的不是本字段）时置为当前时间戳

我们测试一下，给d4添加ON UPDATE CURRENT_TIMESTAMP属性，然后修改d2的值，看d4的值是否会变为当前时间戳

注意观察，我们更新的确实是d2的值，但d4果然也产生了变化，变为了当前时间戳

---

字符串类型

Char

定长字符串：指定长度之后，系统一定会分配指定的空间用于存储数据

基本语法：char(L)，L代表字符数（中文与英文字母一样），L长度为0到255

Varchar

变长字符串：指定长度之后，系统会根据实际存储的数据来计算长度，分配合适的长度（数据不能超出指定长度）

基本语法：varchar（L），L代表字符数，L的长度理论值位0到65535因为varchar要记录数据长度（系统根据数据长度自动分配空间），所以每个varchar数据产生后，系统都会在数据后面增加1-2个字节的额外开销，是用来保存数据所占用的空间长度，如果数据本身小于127个字符，额外开销一个字节，如果大于127个，就开销两个字节

注意:char和varchar的区别绝对不是char(L)类型只能存L个字符，事实上char(L)类型与varchar(L)一样也能存小于L个字符，但是即使如此，char(L)依然会占用L个字符所占的空间，这就是两者的区别

char和varchar数据存储对比（假设使用utf8，一个字符都会占用3个字节）

存储数据	char(2)	varchar(2)	char所占字节	varchar所占字节
A	A	A	2*3=6	1*3+1=4
AB	AB	AB	2*3=6	2*3+1=7

这就体现出了二者的区别，char(L)所占空间是确定的，varchar(L)会随着所存字符数改变而改变

另外char有一个优点就是char的查询效率要比varchar高

使用场景：char和varchar都是在字符数量不是很多的时候使用，如果确定数据一定是占指定长度，那么使用char类型，如果不确定数据到底有多少，那么使用varchar类型。如果数据长度超过255个字符，不论是否固定长度，都会使用text，不再使用char和varchar

Text和Blob

text用来存储普通的字符文本，blob（binary large object）存储一个二进制大对象，mysql系统提供四种text和blob

TinyBlob	最大长度255个字元(2^8-1)
TinyText	最大长度255个字元(2^8-1)
Blob	最大长度65535个字元(2^16-1)
Text	最大长度65535个字元(2^16-1)
MediumBlob	最大长度16777215个字元(2^24-1)
MediumText	最大长度16777215个字元(2^24-1)
LongBlob	最大长度4294967295个字元 (2^32-1)
LongText	最大长度4294967295个字元 (2^32-1)

blob和text类型的区别

1. blob列被视为二进制字符串(字节字符串)
2. text列被视为非二进制字符串(字符字符串)
3. blob列没有字符集，并且排序和比较基于列值字节的数值值
4. text列有一个字符集，并且根据字符集的 校对规则对值进行排序和比较

blob和text类型的相同点：在text或blob列的存储或检索过程中，不存在大小写转换

Enum

枚举类型：在数据插入之前，先设定几个项，这几个项就是可能最终出现的数据结果

如果确定某个字段的数据只有那么几个值：如性别，男、女、保密，系统就可以在设定字段的时候规定当前字段只能存放固定的几个值：使用枚举

基本语法：enum(数据值1,数据值2,...)

系统提供了1到2个字节来存储枚举数据：通过计算enum列举的具体值来选择实际的存储空间：如果数据值列表在255个以内，那么一个字节就够，如果超过255但是小于65535，那么系统采用两个字节保存

我们尝试向其插入一个指定项之外的数据，观察结果

果然报错了，可见enum有规范数据的功能，能够保证插入的数据必须是设定的范围，其他值都不可以

枚举enum的存储原理：实际上字段上所存储的值并不是真正的字符串，而是字符串对应的下标：当系统设定枚举类型的时候，会给枚举中每个元素定义一个下标，这个下标规则从1开始

enum(1=>'男',2=>'女',3=>'保密')

特性：在mysql中系统是进行隐式类型转换的：如果数据碰到“+、-、*、/”系统就会自动将数据转换成数值,而普通字符串转换成数值0

我们可以利用此条特性来证明enum类型存储的是数字下标

我们同样可以利用这条特性来插入下标来代表enum数据

枚举的意义：

1. 规范数据本身，限定只能插入规定的数据项
2. 节省存储空间

Set

集合：是一种将多个数据选项可以同时保存的数据类型，本质是将指定的项按照对应的二进制位来进行控制，1表示该选项被选中，0表示该选项没有被选中。

基本语法：set('值1','值2','值3',...)

系统为set提供了多个字节进行保存，但是系统会自动计算来选择具体的存储单元

1个字节：set只能有8个选项
2个字节：set只能有16个选项
3个字节：set只能有24个选项
4个字节：set只能有32个选项
8个字节（最多）：set只能有64个选项

set和enum一样，最终存储到数据字段中的依然是数字而不是真实的字符串

插入数据：可以插入多个数据，就是在数据插入的字符串中，使用对应的逗号“，”将选项进行隔开（注意这个集合整体是一个数据项，引号不要点错了）

此外我们从上面的结果中发现，无论你插入时顺序是怎样的，存储是都会按照创建该集合字段时指定的顺序来存储

分析数据存储的方式：系统将对应的数据选项（设计）按照顺序进行编排，从第一个开始进行占位，每一个都对应一个二进制位。

在上面的例子中，集合中共有8个数据项，所以该字段使用1个字节存储数据，情形如下

插入数据时对应的数值如下

但是在mysql系统存储这些数据项的时候还会进行一个逆序转换，然后转换成十进制进行存储，情形如下

那么为什么要多此一举的逆序再存储呢？那是因为我们定义集合类型数据的时候，集合元素个数很多情况不是8的整倍数，拿上面例子来说，比如说我们定义字段hobby的类型为“set('篮球,乒乓球,冰球')”，集合元素个数只有3个，而系统存储以字节为单位，3个元素个数不够8个，但也占一整个字节存储，这就会让这个字节的后5位永远都是0，而如果把这个数逆序处理，就会让数字相对变的比原来更小，这样可以提升系统计算处理的效率（这一段纯属猜测，才疏学浅找不到资料）

同样，我们用数据隐式转换的原理来验证一下是不是这么存的

集合的意义：

1. 规范数据本身，限定只能插入规定的数据项
2. 节省存储空间

总结：enum类似单选，而set类似多选

所有字符串类型数据要注意：在mysql中，有一项规定：mysql的记录长度（record也可以理解成行）总长度不能超过65535个字节，所以在mysql中如果你发现varchar等类型数据明明没有达到本数据类型规定的极限但却被禁止插入就是这个原因了