MySQL知识汇总：详解MySQL基本数据类型_mysql数据基本类型

第一章：MySQL数据类型概述

MySQL 的数据类型有大概可以分为 5 种，分别是整数类型、浮点数类型和定点数类型、日期和时间类型、字符串类型、二进制类型等。
注意：整数类型和浮点数类型可以统称为数值数据类型。

一：数值类型

1：整数

整数类型包括 TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT

2：浮点数

浮点数类型包括 FLOAT 和 DOUBLE，定点数类型为 DECIMAL。

计算机不仅需要存储整数，还需要存储小数。由于计算机中并没有专门的部件对小数中的小数点进行存储和处理，所有需要一种规范，使用二进制数据表示小数。这种规范分两种：定点数表示方式+浮点数表示方式
注意：定点数并不是仅仅只能表示整数，定点数也可以表示小数。 浮点数同样可以表示小数和整数；定点数和浮点数只是计算机表示数据的两种不同方式而已。

1)：定点数

定点数的意思是：小数点的位置在计算机的存储是约定好的，固定的。一个小数的整数部分和小数部分分别转化为二进制的表示。
例如：十进制的 25.125
整数部分：25使用二进制表示为：11001
小数部分：0.125使用二进制表示为：.001
所以合起来使用11001.001 表示十进制的25.125
所以在一个8位的计算机中，前5位表示一个小数的整数部分，后3位表示小数部分，小数点默认是第五位后的位置（实际上计算机不会存储小数点，只是大家这么约定）。

使用11001001表示十进制的25.125。看起来很完美，也很容易理解，但是这么表示没有其他问题吗？
问题在于：一个8位的计算机，整数部分111111十进制最大只能表示为31 ;小数部分0.111 最大只能表示0.875，

表示的数据范围太小了。当然在16位的计算机中，可以通过增大整数部分位数表示更大的数，增大小数部分的位数可以提高小数精度。但是这种方式对计算机来说开销非常大，所以大多数计算机并没有选择使用定点数表示小数，而是采用浮点数表示小数。

2)：浮点数

计算机中使用浮点数表示小数类似于以前数学中用科学计数法表示较大的数。
例如：352.47 = 3.5247 * 10的2次方，178.125转化为二进制为 10110010.001，又可表示为：1.0110010001 乘以 2的111次方（111是7的二进制表示）

10110010001 这部分被称作尾数(M)
111这部分被称作阶码（P）
正负被称作数符（S）: 0表示正数 ， 1表示负数。
那么一个浮点数可以使用三部分表示：数符（S）,阶码（P）,尾数（M）。

根据IEEE754标准，64位计算机的长实数浮点表示：

178.125在计算机中使用浮点数表示为：

3)：浮点数与定点数的比较

当相同位数的计算机表示数据（比如64位），浮点数能表示的数据范围远远大于定点数表示的数据范围。
当相同位数的计算机表示数据（比如64位），浮点数的相对精度比定点数要高。
浮点数在计算时，要分阶码部分的计算和尾数部分的计算，而且运算结果要求规格化，故浮点运算步骤比定点数运算步骤多，运算速度比定点运算速度低。
目前大多数计算机使用浮点数表示小数。

二：日期/时间类型

包括 YEAR、TIME、DATE、DATETIME 和 TIMESTAMP。

1：YEAR类型

YEAR类型用来表示年份，在所有的日期时间类型中所占用的存储空间最小，只需要1个字节 的存储空间。:
从MySQL5.5.27开始，2位格式的YEAR已经不推荐使用。

2：DATE类型

DATE类型表示日期，没有时间部分，格式为YYYY-MM-DD，其中，YYYY表示年份，MM表示月份，DD表示日期。需要3个字节的存储空间。在向DATE类型的字段插入数据时，同样需要满足一定的格式条件。

使用CURRENT_DATE()或者NOW()函数，会插入当前系统的日期。

3：TIME类型

TIME类型用来表示时间，不包含日期部分。在MySQL中，需要3个字节的存储空间来存储TIME类型的数据，可以使用“HH:MM:SS”格式来表示TIME类型，其中，HH表示小时，MM表示分钟，SS表示秒。

使用CURRENT_TIME()或者NOW()，会插入当前系统的时间。

4：DATETIME类型

DATETIME类型在所有的日期时间类型中占用的存储空间最大，总共需要8个字节的存储空间。在格式上为DATE类型和TIME类型的组合，可以表示为YYYY-MM-DD HH:MM:SS，其中YYYY表示年份，MM表示月份，DD表示日期，HH表示小时，MM表示分钟，SS表示秒。

使用函数CURRENT_TIMESTAMP()和NOW()，可以向DATETIME类型的字段插入系统的当前日期和时间。

5：TIMESTAMP类型

TIMESTAMP类型也可以表示日期时间，其显示格式与DATETIME类型相同，都是YYYY-MM-DD HH:MM:SS，需要4个字节的存储空间。但是TIMESTAMP存储的时间范围比DATETIME要小很多，只能存储“1970-01-01 00:00:01 UTC”到“2038-01-19 03:14:07 UTC”之间的时间。其中，UTC表示世界统一时间，也叫作世界标准时间。

存储数据的时候需要对当前时间所在的时区进行转换，查询数据的时候再将时间转换回当前的时区。因此，使用TIMESTAMP存储的同一个时间值，在不同的时区查询时会显示不同的时间。

6：TIMESTAMP和DATETIME的区别:

TIMESTAMP存储空间比较小，表示的日期时间范围也比较小
底层存储方式不同，TIMESTAMP底层存储的是毫秒值，距离1970-1-1 0:0:0 0毫秒的毫秒值。
两个日期比较大小或日期计算时，TIMESTAMP更方便、更快。
TIMESTAMP和时区有关。TIMESTAMP会根据用户的时区不同，显示不同的结果。而DATETIME则只能反映出插入时当地的时区，其他时区的人查看数据必然会有误差的。

三：字符串类型

包括 CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM 和 SET 等。


M：表示列值的最大长度（二进制串以字节为单位，非二进制串以字符为单位）
L：表示某个给定值的字节长度
w：表示列字符集里最"宽"字符所占的字节数
BOLB和TEXT分别有多种变体，主要区别在于容纳的字符串最大长度不同

有些类型是用于保存二进制串(字节串)，而其他类型则用于保存非二进制串。例如，BINARY(20)可以容纳20个字节，而CHAR(20)也可以容纳20个字符(对于多字节字符，其存 储空间要求肯定会超过20个字节)，每种二进制串类型都有一种与之对应的非二进制串类型。

1：固定长度类型

BINARY和CHAR都是固定长度的字符串类型，会对那些比列的长度更短的值进行补齐：对于BINARY类型， 是使用0x00进行补齐。
对于CHAR类型，则是使用空格。
因为CHAR(M)列必须能够表示由列的字符集所确定的最大可能字符串，所以这种类型列需要占用M×w个字节，其中，w是字符集里最宽字符所占用的字节数。
1个ujis字符会占用1〜3个字节 ，因此CHAR(20)必须分配60个字节的空间，以应对20个字符全部占用3个字节的情况。
采用Unicode字符集，每个英文字符占用1字节，如果定义一个列为CHAR(10)，存储内容为’abc’，该列仍占用10字节。

2：可变长类型

其他字符串类型的长度都是可变的。对于每个值所占用的存储空间大小，各个行也各不相同；
具体是多少，则取决于存储在列里的那些值的最大允许长度。对于可变长度的类型，这个长度在上图中被表示为L。除去L以外的额外要求字节空间，与用来存储值的长度所需的字节数相等。
在处理长度可变的数据时 ，MySQL会把数据内容和数据长度都存储起来。这些额外的 “长度字节”前缀，会被当作无符号整数来对待。
在可变类型的最大长度(即该类型所要求的长度字节数)与占用相同字节数的无符号整数类型的取值范围之间，存在着一定的对应关系。例如，MEDIUMBLOB类型值的最大长度是个字节，并且需要3个字节来记录这个长度。而对于占用3个字节的整数类型MEDIUMINT，其最大无符号值也为。这可不是什么巧合！

3：CHAR和VARCHAR

字符串类型CHAR和VARCHAR主要用于保存非二进制串，因此它们有字符集和排序规则 。
CHAR和VARCHAR之间的主要区别在于：它们的长度是固定的，还是不变的；如何对待尾部空格。
CHAR是一种长度固定的类型，而VARCHAR是一种长度可变的类型。
从CHAR列检索出来的值，其尾部空格会被移除。对于CHAR(M)列，如果其值的长度小于M个字符，那么在存入时会用空格将长度补齐到M。但是，当在检索这些值时，其尾部的空格会被移除。如果启用SQL的PAD_CHAR_TO_ FULL_LENGTH模式，那么在检索CHAR列值 时就可以保留尾部空格。
对于VARCHAR列，其尾部空格在存储和检索时都会被保留。

1)：CHAR长度

在定义CHAR列时 ，其最大长度M的取值范围是0~255。
对于CHAR类型，M是可选的；如果省略，则其默认值为1。
请注意，CHAR(0)也是合法的。如果你允许CHAR(0)列为NULL，那么它可以用它来表示“开/关”值 。这种列只有两种取值：NULL或空串。在表里，CHAR(0)列只占用非常少的存储空间——仅一个二进 制位。

2)：VARCHAR长度

对于于VARCHAR(M)，其中的M在语法上的取值范围是1~65535，但它实际能够容纳的最大字符个数肯定小于65535。这是因为在MySQL里，行的最大长度为65535个字节。
下面是一些潜在的影响点：
一个长VARCHAR列需要2个字节来存放字符串的长度，最终长度不能超过行的总长。
使用多字节字符可以减少字符个数，从而使字符总长度不会超过最大行长度。
表里的其他列也会减少行里VARCHAR列的可用空间量。
CHAR和VARCHAR这两种数据类型的选择：
如果所有值的长度都是M个字符，那 么VARCHAR(M)列会比CHAR(M)列多占用存储空间，因为前一种类型还需要用额外的字节来记录值的长度。
反之，如果数据长短不一，那么选用VARCHAR类型可以节省空格占用的存储空间。CHAR(M)列始终会占用M个字符的空间，即使其值为空或NULL也一样。
如果使用的是MylSAM表，并且各个值的长度差别不大，那么选用CHAR往往会比选用VARCHAR更好些，因为MylSAM存储引擎对固定长度行的处理效率，要比对长度可变行的高。更多相关信息在后面"选择利于高效查询的表存储格式"会介绍。

4：BINARY和VARBINARY

BINARY和VARBINARY类型与CHAR和VARCHAR相似，不同之处在于以下几点：
CHAR和VARCHAR都是用于存储字符的非二进制类型，并且都有字符集和排序规则。比较 操作依据的是排序序列。
BINARY和VARBINARY都是用于存储字节的二进制类型，它们没有字符集和排序规则。比较操作依据的是字节值的大小。
对于BINARY(M)列，当存储那些长度短于M个字节的值时，它们会用0x00字节进行补齐，以保证 长度为凡在检索时，不会去除任何内容。对于VARBINARY，在存储值时，不会进行补齐；在检 索时，也不会去除任何内容。

5：BLOB和TEXT

BLOB
“blob”指的是二进制大对象(binary large object)，它实际上是一个能够存放任何内容的容器，并且可以大到超出你的想象，多达4GB。
在MySQL里，BLOB类型实际上是一个类型家族，它包括TINYBLOB、BLOB、MEDIUMBLOB和 LONGBLOB。除各自所能存放的最大信息量不同以外，这些类型在其他方面完全一致。
BLOB列存储的是二进制串。如果要保存的信息有可能会急剧膨胀 ，或者各行的长短差异很大，那么BLOB类型会非常适合。像压缩数据、加密数据、图像和声音，都适合使用BLOB。
TEXT
MySQL还有一个TEXT类型家族，它包括TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。
它们与相应的BLOB类型有很多相似之处，但TEXT类型存储的是非二进制串，而非二进制串。也就是说，它们存储的是字符，而不是字节，它们与字符集和排序规则相关联。这也是二进制串和非二进制串的不同之处。例如，BLOB值的比较是以字节为单位的；而TEXT值的比较则是以字符为单位，并且依据的是列的排序规则 。
不过，TEXT类型的最大长度与BLOB类型的是一样的。也就是说 ，它们都是以字节为单位来衡量最大长度的，而不是以字节为单位(对于BLOB类型)和以字符为单位(对于TEXT类型)进行衡量。
BLOB和TEXT列能否被索引，具体取决于所使用的存储引擎：
存储引擎InnoDB和MylSAM都支持对BLOB和TEXT列进行索引。但必须指定一个前缀长度，以方便索引使用。这样可以避免创建出可能会过于庞大的索引，如果出现这样的情况，则会完全抵消掉索引所带来的好处。不过凡事有例外，TEXT列的FULLTEXT索引并不会使用那个前缀长度，因为FULLTEXT索引是以索引列的完整内容为基础的，指定的前缀长度会被忽略。
MEMORY表不支持BLOB和TEXT索引，因为MEMORY引擎根本不支持BLOB和TEXT列。
对于BLOB或TEXT列，需要特别注意以下几点：
由于BLOB和TEXT列里的值在长度方面的差异通常很大，因此在多次执行删除和修改操作之后，表里容易产生大量碎片。如果是使用MylSAM表来存储BLOB或TEXT值 ，那么定期运行OPTIMIZE TABLE命令可以减少碎片和改善系统性能。更多相关信息请参考后面"查询优化"相关文章。
max_sort_length系统变量会对BLOB和TEXT类型值的比较和排序操作产生影响。对于每个值，只有其前面的max_sort_length个字节才会被使用到。 (这意味着，对于使用了多字节字符集的TEXT列，参与比较 的字符数会少于max_sort_length。)如果max_sort_length的默认值 (为1024)会造成问 题，则可以在执行比较之前把它调大。
对于数据量非常大的值，可能需要配置MySQL服务器，增大max_allowed_packet参数的值。更多相关信息在后面"服务器调整"文章会介绍。对于所有想要使用大数据量值的客户端程序，则需要在客户端增大数据包的大小。客户端程序mysql和mysqldump都支持直接使用启动选项来设置这个值。

6：ENUM和SET

ENUM和SET是比较特殊的字符串数据类型，它们只能从一个固定的(预先定义好的)字符串列表里取值。
这两种类型的主要区别是：ENUM列值必须包含且只能包含一个值列表成员，而SET列值则允许包含任意多个值列表成员 (可以为空，也可以是全体 成员 )。换句话说，enum类型的值不允许同时出现，而SET类型的值允许同时出现。
ENUM
ENUM类型定义的是枚举集合，它最多允许有65535个成员。赋给ENUM列的值只能是你在创建表时指定的值列表中的一个成员。
枚举类型通常用于表示类别值。例如，对于某个定义为 ENUM(‘N’, ‘Y’)的列，其值可以是’N’或 ‘Y’。另外，你也可以将ENUM类型用于表示某种产品的尺寸或颜色，或者用于表示某次调査问卷中的多重选择题的答案(仅限单选)：
employees ENUM(‘less than 100’, ‘100-500’, ‘501-1500’, ‘more than 1500’)
color ENUM(‘red’, ‘green’, ‘blue’, ‘black’)
size ENUM(‘S’, ‘M’, ‘L’, ‘XL’, ‘XXL’)
vote ENUM(‘Yes’, ‘No’, ‘Undecided’)
如果你正在处理来自Web页面的选择项，而该页面包含了很多互斥的单选按钮，那么你可 以使用ENUM来表示这些让网站的访问者可以从中进行选择的选项。
例如，你正在运营一个在线比萨饼订购服务，这时就可以用一个ENUM列来表示顾客所订比萨饼的口味和大小：
crust ENUM(‘thin’, ‘regular’, ‘pan style’, ‘deep dish’)
size ENUM(‘small’, ‘medium’, ‘large’)
SET
SET类型与ENUM类型有相似之处，如在创建SET列时，同样需要为它指定一个所允许的 集合成员列表。SET类型最多允许有64个成员。
它与ENUM类型有所不同，其每一个值列都可以由集合的任何成员构成。如果有一组固定值，但与在ENUM列里的情况不同，它们不是互斥的，这时就可以使用SET。
例如，上面那个在线比萨饼服务可能会有一组多选框 ，用于表示顾客想要的用作馅料的配料，而且可以有多种选择。这时就可以使用SET来表示这些配料：
SET(‘pepperoni’, ‘sausage’, ‘mushrooms’, ‘onions’, ‘ripe olives’)
于是，特定的SET值就代表了顾客实际所选择的那些配料：最后一个值（空串）表示顾客在订购时没有选择任何配料，SET是允许这样的。
‘pipperoni,mushrooms’
‘sausage,onions’
‘sausage,mushrooms,ripe olives’
‘onions’
‘’
SET定义时的相关注意事项：
SET列的定义 ，可以写成以逗号分隔的单个字符串列表，表示所有的集合成员。
另 一方面，SET列的值 ，必须是一个字符串。如果这个值由集合里的多个成员构成，则必须用逗号把各个成员分隔开。这意味着，你不应该把一个包含逗号的字符串用作SET的成员。

四：二进制类型

包括 BIT、BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。

MySQL中的每种数据类型都可以通过以下特征来确定
它用来表示数据值。占用的空间以及值是固定长度还是可变长度。数据类型的值可以被索引。MySQL如何比较特定数据类型的值。

参考文章如下：
原文链接：https://blog.csdn.net/weixin_54234280/article/details/126445852
原文连接：https://zhuanlan.zhihu.com/p/410047526
原文连接：https://blog.51cto.com/u_15346415/3671081