关于MySQL数据类型，平时用的最多的是varchar、datetime、enum。今天好好研究一下MySQL数据类型。

转自：https://www.cnblogs.com/Caveolae/p/7058890.html

MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。

1.数值

1.1整数类型

   

1.2定点数

 DECIMAL和NUMERIC类型在MySQL中视为相同的类型。它们用于保存必须为确切精度的值，如：DECIMAL(6,2)取值范围-9999.99~9999.99。

DECIMAL(M,D)，M的默认取值为10，D默认取值为0。如果创建表时，某字段定义为decimal类型不带任何参数，等同于decimal(10,0)。带一个参数时，D取默认值。

    M的取值范围为1~65，取0时会被设为默认值，超出范围会报错。
    D的取值范围为0~30，而且必须<=M，超出范围会报错。

1.3浮点数

float，double和real。他们定义方式为：FLOAT(M,D) 、 REAL(M,D) 、 DOUBLE PRECISION(M,D)。

1.4BIT

BIT(M)类型允许存储M位值。M范围为1~64，默认为1。

2.字符串类型 字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。

2.1、CHAR和VARCHAR类型

CHAR列的长度固定为创建表时声明的长度。长度可以为从0到255的任何值。当保存CHAR值时，在它们的右边填充空格以达到指定的长度。当检索到CHAR值时，尾部的空格被删除掉，所以，我们在存储时字符串右边不能有空格，即使有，查询出来后也会被删除。在存储或检索过程中不进行大小写转换。 所以当char类型的字段为唯一值时，添加的值是否已经存在以不包含末尾空格（可能有多个空格）的值确定，比较时会在末尾补满空格后与现已存在的值比较。

 VARCHAR列中的值为可变长字符串。长度可以指定为0到65,535之间的值。同CHAR对比，VARCHAR值保存时只保存需要的字符数，另加一个字节来记录长度(如果列声明的长度超过255，则使用两个字节)。

 VARCHAR值保存时不进行填充。当值保存和检索时尾部的空格仍保留，符合标准SQL。

如果分配给CHAR或VARCHAR列的值超过列的最大长度，则对值进行裁剪以使其适合。如果被裁掉的字符不是空格，则会产生警告。如果裁剪非空格字符，则会造成错误(而不是警告)并通过使用严格SQL模式禁用值的插入。

 下面显示了将各种字符串值保存到CHAR(4)和VARCHAR(4)列后的结果：

    

    表中最后一行的值只适用在不使用严格模式时；如果MySQL运行使用严格模式，超过列长度的值不保存，并且会出现错误。

    因为空格的原因，相同的值存入到长度都足够的varvhar和char中，取出可能会不同，比如”a”和”a  “。

2.2BINARY和VARBINARY类型

BINARY和VARBINARY类型类似于CHAR和VARCHAR类型，但是不同的是，它们存储的不是字符串，而是二进制串。所以它们没有字符集，并且排序和比较基于列值字节的数值。

当保存BINARY值时，在它们右边填充0x00(零字节)值以达到指定长度。取值时不删除尾部的字节。比较时所有字节都很重要（因为空格和0x00是不同的，0x00<空格），包括ORDER BY和DISTINCT操作。比如插入’a ‘会变成’a ‘。

对于VARBINARY，插入时不填充字符，选择时不裁剪字节。比较时所有字节都很重要。当类型为BINARY的字段为主键时，应考虑到上面介绍的存储方式。

2.3BLOB和TEXT类型

 BLOB是一个二进制大对象，可以容纳可变数量的数据。有4种BLOB类型：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它们只是可容纳值的最大长度不同。有4种TEXT类型：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。这些对应4种BLOB类型，有相同的最大长度和存储需求。

 BLOB列被视为二进制字符串。TEXT列被视为字符字符串，类似CHAR和BINARY。

    在TEXT或BLOB列的存储或检索过程中，不存在大小写转换。

    未运行在严格模式时，如果你为BLOB或TEXT列分配一个超过该列类型的最大长度的值，值被截取以保证适合。如果截掉的字符不是空格，将会产生一条警告。使用严格SQL模式，会产生错误，并且值将被拒绝而不是截取并给出警告。

    在大多数方面，可以将BLOB列视为能够足够大的VARBINARY列。同样，可以将TEXT列视为VARCHAR列。

    BLOB和TEXT在以下几个方面不同于VARBINARY和VARCHAR：

    当保存或检索BLOB和TEXT列的值时不删除尾部空格。(这与VARBINARY和VARCHAR列相同）。 
    比较时将用空格对TEXT进行扩充以适合比较的对象，正如CHAR和VARCHAR。
    对于BLOB和TEXT列的索引，必须指定索引前缀的长度。对于CHAR和VARCHAR，前缀长度是可选的。
    BLOB和TEXT列不能有默认值。

    MySQL Connector/ODBC将BLOB值定义为LONGVARBINARY，将TEXT值定义为LONGVARCHAR。

    BLOB或TEXT对象的最大大小由其类型确定，但在客户端和服务器之间实际可以传递的最大值由可用内存数量和通信缓存区大小确定。你可以通过更改max_allowed_packet变量的值更改消息缓存区的大小，但必须同时修改服务器和客户端程序。

    每个BLOB或TEXT值分别由内部分配的对象表示。

    它们（TEXT和BLOB同）的长度：

    Tiny：最大长度255个字符(2^8-1)
    BLOB或TEXT：最大长度65535个字符(2^16-1)
    Medium：最大长度16777215个字符(2^24-1)
    LongText 最大长度4294967295个字符(2^32-1)

    实际长度与编码有关，比如utf-8的会减半。

2.4ENUM

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

 可以插入空字符串””和NULL：

    如果你将一个非法值插入ENUM(也就是说，允许的值列之外的字符串)，将插入空字符串以作为特殊错误值。该字符串与“普通”空字符串不同，该字符串有数值值0。
    如果将ENUM列声明为允许NULL，NULL值则为该列的一个有效值，并且默认值为NULL。如果ENUM列被声明为NOT NULL，其默认值为允许的值列的第1个元素。

    值的索引规则如下：

    来自列规定的允许的值列中的值从1开始编号。
    空字符串错误值的索引值是0。所以，可以使用下面的SELECT语句来找出分配了非法ENUM值的行：mysql> SELECT * FROM tbl_name WHERE enum_col=0;
    NULL值的索引是NULL。

    如下例：

    

    ENUM最多可以有65,535个元素。当创建表时，ENUM成员值的尾部空格将自动被删除。

使用方式：

CREATE TABLE shirts (
name VARCHAR(40),
size ENUM(‘x-small’, ‘small’, ‘medium’, ‘large’, ‘x-large’)
);

INSERT INTO shirts (name, size) VALUES (‘dress shirt’,‘large’),(‘t-shirt’,‘medium’),(‘polo shirt’,‘small’);

SELECT name, size FROM shirts WHERE size = ‘medium’;

UPDATE shirts SET size = ‘small’ WHERE size = ‘large’;

    如果将返回值设为数值，将返回索引值，比如讲上面的查询语句改为：

SELECT name, size+0 FROM shirts WHERE size = ‘medium’;

    如果将一个数字保存到ENUM列，数字被视为索引，并且保存的值是该索引对应的枚举成员(这不适合LOAD DATA，它将所有输入视为字符串）。不建议使用类似数字的枚举值来定义一个ENUM列，因为这很容易引起混淆。

    ENUM值根据索引编号进行排序）。例如，对于ENUM(‘a’，’b’)，’a’排在’b’前面，但对于ENUM(‘b’，’a’)，’b’排在’a’前面。空字符串排在非空字符串前面，并且NULL值排在所有其它枚举值前面。要想防止意想不到的结果，按字母顺序规定ENUM列。还可以使用GROUP BY CAST(col AS CHAR)或GROUP BY CONCAT(col)来确保按照词汇对列进行排序而不是用索引数字。