MySQL数据库数据类型概述

痛定思痛。 2022-11-07 04:13 244阅读 0赞

### MySQL数据库数据类型概述 ###

MySQL数据类型官方文档 [https://dev.mysql.com/doc/refman/8.0/en/data-types.html][https_dev.mysql.com_doc_refman_8.0_en_data-types.html] ,在此选择常用的简要总结介绍。

**1. 整数类型**

<table> 
 <tbody> 
 <tr> 
 <td style="border-color:#c0c0c0;"> 类型名称 </td> 
 <td style="border-color:#c0c0c0;"> 存储需求 </td> 
 <td style="border-color:#c0c0c0;"> &nbsp;取值范围（有符号） </td> 
 </tr> 
 <tr> 
 <td style="border-color:#c0c0c0;vertical-align:top;"> tinyint(m) </td> 
 <td style="vertical-align:top;"> 1个字节 </td> 
 <td style="vertical-align:top;"> &nbsp;-128~127 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#c0c0c0;vertical-align:top;"> smallint(m) </td> 
 <td style="vertical-align:top;"> 2个字节 </td> 
 <td style="vertical-align:top;"> &nbsp;-32768~32767 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#c0c0c0;vertical-align:top;"> mediumint(m) </td> 
 <td style="vertical-align:top;"> 3个字节 </td> 
 <td style="vertical-align:top;"> &nbsp;-8388608~8388607 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#c0c0c0;vertical-align:top;"> int(m) </td> 
 <td style="vertical-align:top;"> 4个字节 </td> 
 <td style="vertical-align:top;"> &nbsp;-2147483648~2147483647 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#c0c0c0;vertical-align:top;"> bigint(m) </td> 
 <td style="vertical-align:top;"> 8个字节 </td> 
 <td style="vertical-align:top;"> &nbsp;-9223372036854775808~9223372036854775807 </td> 
 </tr> 
 </tbody> 
</table>

m表示该数据类型指定的显示宽度，指定能够显示的数值中数字的个数。比如说，定义：year int(4)，声明一个只显示4位数字宽度表示年的字段。

**显示宽度和数据类型的取值范围是无关的。**

**2. 浮点数据类型和定点数类型**

MySQL中使用浮点数和定点数来表示小数。它们都可以用(m,n)来表示，其中m称为精度，表示总共的位数；n称为标度，是表示小数的位数。

浮点型在数据库中存放的是近似值，而定点类型在数据库中存放的是精确值。不论是定点还是浮点类型，如果用户指定的精度超出精度范围，则会四舍五入进行处理。

**3. 日期时间类型**

当只记录年信息的时候，可以只使用YEAR类型，而没必要使用DATE类型。当指定确实不合法的值时系统将“零”值插入到数据库中。

<table> 
 <thead> 
 <tr> 
 <td style="border-color:#c0c0c0;"> 类型名称 </td> 
 <td style="border-color:#c0c0c0;"> 日期格式 </td> 
 <td style="border-color:#c0c0c0;"> 取值范围 </td> 
 <td style="border-color:#c0c0c0;"> &nbsp;存储需求 </td> 
 </tr> 
 </thead> 
 <tbody> 
 <tr> 
 <td style="border-color:#c0c0c0;vertical-align:top;"> year </td> 
 <td style="vertical-align:top;"> YYYY </td> 
 <td style="vertical-align:top;"> 1901~2155 </td> 
 <td style="vertical-align:top;"> 1字节 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#c0c0c0;vertical-align:top;"> time </td> 
 <td style="vertical-align:top;"> HH:MM:SS </td> 
 <td style="vertical-align:top;"> -838:59:59~838:59:59 </td> 
 <td style="vertical-align:top;"> 3字节 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#c0c0c0;vertical-align:top;"> date </td> 
 <td style="vertical-align:top;"> YYYY-MM-DD </td> 
 <td style="vertical-align:top;"> 1000-01-01~9999-12-31 </td> 
 <td style="vertical-align:top;"> 3字节 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#c0c0c0;vertical-align:top;"> datetime </td> 
 <td style="vertical-align:top;"> YYYY-MM-DD HH:MM:SS&nbsp; </td> 
 <td style="vertical-align:top;"> 1000-01-01 00:00:00~9999-12-31 23:59:59 </td> 
 <td style="vertical-align:top;"> 8字节&nbsp; </td> 
 </tr> 
 <tr> 
 <td style="border-color:#c0c0c0;vertical-align:top;"> timestamp </td> 
 <td style="vertical-align:top;"> YYYY-MM-DD HH:MM:SS </td> 
 <td style="vertical-align:top;"> 1970-01-01 00:00:01~2038-01-19 03:14:07 </td> 
 <td style="vertical-align:top;"> 4字节 </td> 
 </tr> 
 </tbody> 
</table>

time类型的取值范围如此大的原因是time不仅可以表示一天的时间，还可以是一个大的时间段。

timestamp值的存储是以UTC（世界标准时时间）格式保存的，存储时对当前时区进行转换，检索时再转换为当前时区。即查询时，根据当前时区的不同，显示的时间值不不同的。

**4. 字符串**

<table> 
 <thead> 
 <tr> 
 <td style="border-color:#c0c0c0;"> 类型名称 </td> 
 <td style="border-color:#c0c0c0;"> 说明 </td> 
 <td style="border-color:#c0c0c0;"> &nbsp;存储需求 </td> 
 </tr> 
 </thead> 
 <tbody> 
 <tr> 
 <td style="border-color:#c0c0c0;vertical-align:top;"> char(m) </td> 
 <td style="vertical-align:top;"> 固定长度的字符串 </td> 
 <td style="vertical-align:top;"> m字节，1&lt;=m&lt;=255 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#c0c0c0;vertical-align:top;"> varchar(m)&nbsp; </td> 
 <td style="vertical-align:top;"> 变长字符串 </td> 
 <td style="vertical-align:top;"> L+1字节，在此L&lt;=m和1&lt;=m&lt;=255 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#c0c0c0;vertical-align:top;"> tinytext&nbsp; </td> 
 <td style="vertical-align:top;"> 非常小的字符串 </td> 
 <td style="vertical-align:top;"> L+1字节，在此L&lt;2^8 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#c0c0c0;vertical-align:top;"> text&nbsp; </td> 
 <td style="vertical-align:top;"> 小的字符串 </td> 
 <td style="vertical-align:top;"> L+2字节，在此L&lt;2^16 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#c0c0c0;vertical-align:top;"> mediumtext&nbsp; </td> 
 <td style="vertical-align:top;"> 中等大小的字符串 </td> 
 <td style="vertical-align:top;"> L+3字节，在此L&lt;2^24 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#c0c0c0;vertical-align:top;"> longtext&nbsp; </td> 
 <td style="vertical-align:top;"> 大的字符串&nbsp; </td> 
 <td style="vertical-align:top;"> L+4字节，在此L&lt;2^32 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#c0c0c0;vertical-align:top;"> enum </td> 
 <td style="vertical-align:top;"> 枚举类型，只能有一个枚举字符串值 </td> 
 <td style="vertical-align:top;"> 1或2个字节，取决于枚举值的数目（最大值65535） </td> 
 </tr> 
 <tr> 
 <td style="border-color:#c0c0c0;vertical-align:top;"> set&nbsp; </td> 
 <td style="vertical-align:top;"> 一个集合，字符串对象可以有零个或多个set成员&nbsp; </td> 
 <td style="vertical-align:top;"> 1，2，3，4或8个字节，取决于集合成员的数量（最多64个成员）&nbsp; </td> 
 </tr> 
 </tbody> 
</table>

enum列总有一个默认值。如果将enum列声明为null，null值则为该列的一个有效值，并且默认值为null。如果enum列被声明为not null，其默认值为允许的值列表的第1个元素。

例：enum(“member1″, “member2″, … “member65535″)

enum数据类型就是定义了一种枚举，最多包含65535个不同的成员。当定义了一个enum的列时，该列的值限制为列定义中声明的值。如果列声明包含NULL属性，则NULL将被认为是一个有效值，并且是默认值。如果声明了NOT NULL，则列表的第一个成员是默认值。

set(“member”, “member2″, … “member64″)

set数据类型为指定一组预定义值中的零个或多个值提供了一种方法，这组值最多包括64个成员。值的选择限制为列定义中声明的值。

**5. 二进制类型**

<table> 
 <thead> 
 <tr> 
 <td style="border-color:#c0c0c0;"> 类型名称 </td> 
 <td style="border-color:#c0c0c0;"> 说明 </td> 
 <td style="border-color:#c0c0c0;"> &nbsp;存储需求 </td> 
 </tr> 
 </thead> 
 <tbody> 
 <tr> 
 <td style="border-color:#c0c0c0;vertical-align:top;"> bit(m) </td> 
 <td style="vertical-align:top;"> 位字段类型 </td> 
 <td style="vertical-align:top;"> 大约(m+7)/8个字节 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#c0c0c0;vertical-align:top;"> binary(m)&nbsp; </td> 
 <td style="vertical-align:top;"> 固定长度二进制字符串 </td> 
 <td style="vertical-align:top;"> m个字节 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#c0c0c0;vertical-align:top;"> varbinary(m) </td> 
 <td style="vertical-align:top;"> 可变长度二进制字符串 </td> 
 <td style="vertical-align:top;"> m+1个字节 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#c0c0c0;vertical-align:top;"> tinyblob(m) </td> 
 <td style="vertical-align:top;"> 非常小的BLOB </td> 
 <td style="vertical-align:top;"> L+1字节，在此L&lt;2^8 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#c0c0c0;vertical-align:top;"> blob(m) </td> 
 <td style="vertical-align:top;"> 小BLOB </td> 
 <td style="vertical-align:top;"> L+2字节，在此L&lt;2^16 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#c0c0c0;vertical-align:top;"> mediumblob(m) </td> 
 <td style="vertical-align:top;"> 中等大小的BLOB </td> 
 <td style="vertical-align:top;"> L+3字节，在此L&lt;2^24 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#c0c0c0;vertical-align:top;"> longblob(m) </td> 
 <td style="vertical-align:top;"> 非常大的BLOB </td> 
 <td style="vertical-align:top;"> L+3字节，在此L&lt;2^32 </td> 
 </tr> 
 </tbody> 
</table>

BLOB列存储的是二进制大对象（字节字符串）；TEXT列存储的是非二进制字符串（字符字符串）。BLOB列没有字符集，并且排序和比较基于值字节的数值；TEXT列有一个字符集，并且根据字符集对值进行排序和比较。

**关于auto\_increment属性的说明**

auto\_increment 可以通过此属性为新的行产生唯一的标识

auto\_increment能为新插入的行赋一个唯一的整数标识符。为列赋此属性将为每个新插入的行赋值为上一次插入的ID+1。

MySQL要求将auto\_increment属性用于作为主键的列。此外，每个表只允许有一个auto\_increment列。例如：

id smallint not null auto\_increment primary key

你可以使用LAST\_INSERT\_ID()SQL函数来查询最新的AUTO\_INCREMENT值。

**如何选择合适的MySQL数据类型**  
数据类型的合理选择，有利于我们在检索时，更快的获取数据，或者更精确的进行计算。这个问题不简单，牵扯很大，如：使用适合存储引擎、具体存储的特点、实现的意图等，需要综合考虑和实践经验，在此就不多介绍了，可参见： [https://www.jianshu.com/p/93d91f5192a0][https_www.jianshu.com_p_93d91f5192a0]

[https_dev.mysql.com_doc_refman_8.0_en_data-types.html]: https://dev.mysql.com/doc/refman/8.0/en/data-types.html
[https_www.jianshu.com_p_93d91f5192a0]: https://www.jianshu.com/p/93d91f5192a0