MYSQL数据类型

1. MYSQL数据类型

MYSQL支持多种数据类型：数值类型，日期/时间类型，字符串类型

1.1 数值类型

数值类型主要用来存储数字。MYSQL提供的数值类型有整数类型TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT。浮点小数FLOAT, DOUBLE。定点小数DECIMAL。

1.1.1整数类型

整数类型可以使用字段AUTO_INCREMENT自增约束条件。下面是数值类型的相关信息。

类型名称	存储要求	有符号	无符号	有符号最小值数的宽度（默认显示宽度）
TINYINTT	1字节	-128~127	0~255	4
SMALLINT	2字节	-32768~32767	0~65535	6
MEDIUMINT	3字节	-2^24 ~ 2^24-1	0~2^25-1	9
INT	4字节	-2^32 ~ 2^32-1	0~2^33-1	11
BIGINT	8字节	-2^64 ~ 2^64-1	0~2^65-1	20

值得一提的是，在创建表的过程中，有“ID INT（11），YEAR INT（4）”这样的语句，这里的11、4指的是显示宽度，与数据类型的取值范围是不一样的。
显示宽度是指MYSQL中最大可能显示的数字个数，数值的位数小于指定的宽度时会有空格填充；如果插入了大于显示宽度的值，只要该值不超过该类型整数的最大取值范围，数值一样可以插入，而且能够显示出来。
在创建表的过程中，如果没有指明整数的显示宽度，系统将添加默认的显示宽度。所有整数类型的默认显示宽度与其有符号数的最小值的宽度相同。

1.1.2浮点数类型和定点数类型
MYSQL中使用浮点数和定点数表示 小数。浮点类型有FLOAT，DOUBLE两种。定点只有DECIMAL一种。浮点类型和顶点类型都可以用（M，N）表示。其中M称为精度，表示总共的位数。N称为标度，是表示小数的位数。

类型名称	存储要求
FLOAT	4字节
DOUBLE	8字节
DECIMAL(M,D),DEC	M+2字节

DIECIMAL类型不同于FLOAT和DOUBLE，DECIMAL实际是以串存放的，DECIMAL可能的最大取值范围与DOUBLE一样，但是其有效的取值范围由M和D的值决定。如果改变M而固定D，则其取值范围将随M的变大而变大。DECIMAL的存储空间并不是固定的，而是由其精度值M决定，占用M+2个字节。
FLOAT和DOUBLE在不指定精度时，默认会采用实际的精度（由计算机硬件和操作系统决定），DECIMAL不指定精度时会默认采用（10，0）.

在对精度要求比较高的时候使用DECIMAL的类型比较好。
使用浮点数的时候要注意尽量避免浮点数作比较。

1.2日期与时间类型

MYSQL中表示日期的数据类型主要有DATETIME，DATE，TIMESTAMP，TIME和YEAR。

类型名称	日期格式	日期范围	存储需求
YEAR	YYYY	1901~2155	1字节
TIME	HH:MM:SS	-838:59:59~838:59:59	3字节
DATE	YYYY-MM-DD	1000-01-01~9999-01-01	3字节
DATETIME	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00~9999-01-01 00:00:00	8字节
TIMESTAMP	YYYY-MM-DD HH:MM:SS	1970-01-01 00:00:01 UTC~2038-01-19 03:14:07 UTC	4字节

YEAR
YEAR类型是一个单字节类型，用于表示年，在存储时只需要1字节。可以使用各种格式指定YEAR值。

1. 以4位字符串或者4位数字格式表示的YEAR，范围位‘1901’~‘2155’。输入格式为‘YYYY’或者YYYY。‘2010’和2010插入到数据库中的值均为2010。
2. 以2位字符串格式表示的YEAR，范围为‘00’~‘99’。‘00’ ~‘69’和‘70’ ~‘99’范围的值分别被转化为2000 ~ 2069和1970 ~ 1999 范围的YEAR值。插入超过取值范围的值将被转化为2000.‘0’和‘00’的作用相同。
3. 以2位数字表示的YEAR，范围为1 ~ 99. 1 ~ 69和70 ~ 99分别被转化为2001 ~ 2069和1970 ~ 1999范围的YEAR值。在这里0值将被转化为0000，而非2000。

两位整数范围与两位字符串范围稍有不同，例如读者尝试使用数字格式的0表示2000，但插入数据库的值为0000. 要插入2000，需要使用字符串格式的‘0’或者‘00’.
非法的YEAR值将被转化为0000.

TIME
TIME类型格式为‘HH:MM:SS’，分别表示时分秒。TIME类型的取值范围是-838:59:59~838:59:59，小时这么大的原因是TIME类型不仅可以用来表示一天的时间，也可能是过去的时间或者两个时间之间的时间间隔。可以使用两种格式定义TIME值。

1. ‘D HH:MM:SS’格式的字符串。还可以使用’HH:MM:SS‘, ‘HH:MM’, ‘D HH:MM’,'D HH’或者’SS‘。这里的D表示日，可以取0 ~34之间的值。再插入数据库时，D被转化为小时保存，格式为’D*24+HH‘。
2. ’HHMMSS‘格式的没有间隔的字符串或者HHMMSS格式的数值。
   例如’101112‘被理解为’10:11:12‘，但’109712‘是不合法的，存储时会变成’00:00:00‘.
   如果没有冒号，如’1112‘，1112在MYSQL里解释为00:11:12.
   如果有冒号，如’11:12‘表示11：12：00.

DATE

DATE类型用在仅需要日期值时，没有时间部分，在存储时需要三个字节。
格式为’YYYY-MM-DD‘。分别为年月日。

1. 以’YYYY-MM-DD‘或’YYYYHMMDD‘字符串格式表示的日期。取值范围为’1000-01-01‘ ~ ’9999-12-3‘。
2. 以’YY-MM-DD‘或者’YYMMDD‘字符串格式表示的日期。YY中’00 ~ 69‘表示’2000 ~ 2069‘；’70 ~ 99‘表示’1970 ~ 1999‘.
3. 以YY-MM-DD或者YYMMDD数字格式表示的日期，与前面类似。YY中00 ~ 69表示2000 ~ 2069；70 ~ 99表示1970 ~ 1999.
4. 使用CURRENT_DATE或者NOW()，插入当前系统日期。

DATETIME
DATETIME类型用在同时包含日期和时间信息的值，在存储时需要八字节。日期格式为’YYYY-MM-DD HH:MM:SS‘, 分别表示年月日时分秒。

1. 以’YYYY-MM-DD HH:MM:SS‘或者’YYYYMMDDHHMMSS‘字符串格式表示的值，范围为’1000-01-01 00:00:00‘ ~ ‘9999-12-3 23:59:59’。
2. 以’YY-MM-DD HH:MM:SS‘或者’YYMMDDHHMMSS‘与前面相同，YY中’00 ~ 69‘表示’2000 ~ 2069‘；’70 ~ 99‘表示’1970 ~ 1999‘.
3. 以YYYYMMDDHHMMSS或者YYMMDDHHMMSS数字格式表示的日期和时间。

TIMESTAMP
TIMESTAMP的显示格式与DATETIME相同，显示宽度固定在19个字符，日期格式为YYYY-MM-DD HH: MM:SS, 在存储时需要4个字节。但是TIMESTAMP列的取值范围小于DATETIME，为’1970-01-01 00:00:01’ UTC ~
‘2038-01-19 03:14:07’ UTC.UTC是世界标准时间。

DATETIME输入时间为多少，存储值为多少。
TIMESTAMP的存储是以UTC格式保存的，存储时对当前时区进行转换，检索时转换为当前时区。

CREATE TABLE tmp(ts TIMESTAMP);//创建例子
INSERT INTO tmp VALUES (NOW());插入当前时间23：54：30
SET TIME_ZONE='+10:00';设置时区为东10区
SELECT * FROM tmp;输出为01：54：30

1.3文本字符串类型

文本字符串类型用来存储字符串数据，图片或声音的二进制数据。MYSQL支持两类字符型数据：文本字符串和二进制字符串。

1.3.1文本字符串
文本字符串类型是指CHAR，VARCHAR，TEXT，ENUM，SET。

类型名称	说明	存储需要
CHAR(M)	固定长度非二进制字符串	M字节，1<=M<=255
VARCHAR(M)	变长非二进制字符串	L+1字节，L<=M
TINYTEXT	非常小的非二进制字符串	L+1字节，L<2^8
TEXT	小的非二进制字符串	L+2字节，L<2^16
MEDIUMTEXT	中等大小的非二进制字符串	L+3字节，L<2^24
LONGTEXT	大的非二进制字符串	L +4字节，L<2^32
ENUM	枚举类型，只能有一个枚举字符串值	1或2个字节，取决于枚举值的数目
SET	一个集合, 可以有零个或多个SET成员	1，2，3，4，或8个字节，取决于集合成员的数量（最多64）

由于VARCHAR和TEXT类型是变长类型，对于其存储要求实际上取决于列的实际长度L，例如对于字符串“ABCD”，L=4，而存储要求是5字节。

CHAR和VARCHAR
CHAR(M)为固定长度字符串，在定义时指定字符长。
VARCHAR(M)时长度可变的字符串，M表示最大列长度。VARCHAR最大实际长度由最长的行的大小和使用的字符集确定，而实际占用的空间为字符串的实际长度加1。

插入值	CHAR(4)	存储要求	VARCHAR(4)	存储要求
‘’	’ ’	4字节	‘ ’	1字节
‘abc’	'abc ’	4字节	‘abc ’	4字节
‘abcd’	‘abcd’	4字节	‘abcd’	5字节
‘abcdef’	‘abcd’	4字节	‘abcd’	5字节

CHAR定义了固定长度为M的列，不管存入数据为多少，所占用的空间均为M字节；VARCHAR定义的列所占的字节数为实际长度+1.

TEXT
TEXT列保存非二进制字符串，如文章内容，评论等。当保存或查询TEXT列的值的时候，不删除尾部空格。TEXT类型分为4中：TINYTEXT(2^8 -1), TEXT(2^16-1), MEDIUMTEXT(2^24-1), LONGTEXT(2^32-1).

ENUM
ENUM是一个字符串对象，其值为表创建时在列规定中枚举的一列值。

字段名 ENUM ('值1','值2','值3'....)；

字段名指要定义的字段，值n指枚举列表中的第n个值。ENUM类型的字段在取值时，只能那个在指定的枚举列表中取，而且一次只能取一个。
例如，定义ENUM类型的列(‘first’,‘second’,‘third’);该列可以取的值和每个值的索引如下。

值	索引
NULL	NULL
‘’	0
first	1
second	2
third	3

ENUM按照索引顺序排列，并且空字符串排在非空字符串前，NULL值排在所有枚举值之前。
查看列成员的索引值。

CREATE TABLE tmp(enu ENUM('first', 'second', 'third'));
INSERT INTO tmp VALUES('first'), (‘second'),('third"),(NULL);
SELCET enu, enu+0 FROM tmp;

利用ENUM实现成绩管理。
我们看到level中的‘good’，2的效果是相同。

mysql> create table tmp(
    -> soc int,
    -> level enum('excellent','good','bad'));
Query OK, 0 rows affected (0.03 sec)

mysql> insert into tmp values (70,'good'),(90,1),(75,2),(50,3);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from tmp;
+------+-----------+
| soc  | level     |
+------+-----------+
|   70 | good      |
|   90 | excellent |
|   75 | good      |
|   50 | bad       |
+------+-----------+
4 rows in set (0.00 sec)

SET
SET 时一个字符串对象，可以有零或多个值，最多可以有64个成员。

SET('值1'，‘值2’，……)；

与ENUM相同，SET值在内部用整数表示，列表中每一个值都有一个索引编号。当创建表时，SET成员值的尾部空格将自动被删除。与ENUM类型不同的是，ENUM类型的字段只能从定义的列值中选择一个直插入，而SET类型的列可以从定义的列值中选择多个字符的集合。
如果插入SET字段中列值有重复，那么MYSQL会自动删除重复的值，插入SET字段的值的顺序并不重要，MySQL会在存入数据库时按照定义的顺序显示；如果插入不正确的值，MySQL会忽视这些值并给出警告。

mysql> create table tmp(s SET ('a','b','c','d'));
Query OK, 0 rows affected (0.03 sec)

mysql> insert into tmp values ('a'),('a,b,a'),('c,a,d');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from tmp;
+-------+
| s     |
+-------+
| a     |
| a,b   |
| a,c,d |
+-------+
3 rows in set (0.00 sec)

插入‘a,b,a’，MySQL去除重复的；
插入‘a,c,d’, MySQL自动按照顺序排列值。

二进制字符串类型

MySQL中的二进制数据类型有BIT，BINARY，VARBINARY，TINYBLOB，BLOB，MEDIUMBOLB，LONGBLOB。

BIT(M)类型是位字段类型, M表示每个值的位数，范围位1~64。 如果M被省略，默认为1。如果为BIT（M）列分配的值小于M位，在值的左边用0填充。例如，为BIT（6）列分配一个值b’101’, 其效果与分配b’000101’相同。
BINARY和VARBINARY类型类似于CHAR和VARCHAR。

列名称 BINARY(M)或者VARBINARY(M);

BINARY类型的长度是固定的，指定长度后，不足最大长度的，将在左边填充‘\0’。
VARBINARY类型的长度是可变的，指定好长度后，其长度可以在0到最大值之间。
BLOB列存储的是二进制字符串。TEXT存储的是非二进制字符串。