MySQL：mysql的数据类型

MySQL 作为一个流行的关系型数据库管理系统，支持多种数据类型以满足不同的数据处理和存储需求。正确理解和使用这些数据类型对于提高数据库性能、确保数据完整性和准确性至关重要。

MySQL 数据类型

数据类型定义了列中可以存储什么数据以及该数据怎样存储的规则。

数据库中的每个列都应该有适当的数据类型，用于限制或允许该列中存储的数据。例如，列中存储的为数字，则相应的数据类型应该为数值类型。

如果使用错误的数据类型可能会严重影响应用程序的功能和性能，所以在设计表时，应该特别重视数据列所用的数据类型。更改包含数据的列不是一件小事，这样做可能会导致数据丢失。因此，在创建表时必须为每个列设置正确的数据类型和长度。

MySQL 的数据类型有大概可以分为 5 种，分别是

• 整数类型
• 小数类型 (浮点数类型、定点数类型)
• 日期和时间类型
• 文本字符串类型
• 二进制类（二进制字符串)

定义字段的数据类型对数据库的优化是十分重要的。

数值类型

整数类型和浮点数类型可以统称为数值数据类型。

整数类型

整数类型又称数值型数据，数值型数据类型主要用来存储数字。

MySQL提供了多种数值型数据类型，不同的数据类型提供不同的取值范围，可以存储的值范围越大，所需的存储空间也会越大。

MySQL 主要提供的整数类型有 TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，其属性字段可以添加 AUTO_INCREMENT 自增约束条件。

类型名称	范围(有符号)	范围(无符号)	内存占据
TINYINT	-128〜127 (很小的整数)	0 〜255	1 bytes
SMALLINT	-32768〜32767 (小的整数)	0〜65535	2 bytes
MEDIUMINT	-8388608〜8388607 (中等大小的整数)	0〜16777215	3 bytes
INT(INTEGER)	-2147483648〜2147483647 (普通大小的整数)	0〜4294967295	4 bytes
BIGINT	-92233720368547758〜9223372036854775807 (大整数)	0〜18446744073709551615	8 bytes

显示宽度和数据类型的取值范围是无关的。显示宽度只是指明 MySQL 最大可能显示的数字个数，数值的位数小于指定的宽度时会由空格填充。
如果插入了大于显示宽度的值，只要该值不超过该类型整数的取值范围，数值依然可以插入，而且能够显示出来。
例如，year 字段插入 19999，当使用 SELECT 查询该列值的时候，MySQL 显示的将是完整的带有 5 位数字的 19999，而不是 4 位数字的值。

小数类型

Mysql 中使用 浮点数 和 定点数 来表示小数。

浮点类型有两种，分别是单精度浮点数（FLOAT）和双精度浮点数（DOUBLE）；

定点类型只有一种，就是 DECIMAL。

浮点类型和定点类型都可以用 (M, D) 来表示，其中 M 称为 精度，表示总共的位数(显示宽度)；D称为 标度，表示小数的位数。

浮点数类型的取值范围为 M（1～255） 和 D（1～30，且不能大于 M-2），分别表示显示宽度和小数位数。

M 和 D 在 FLOAT 和 DOUBLE 中是可选的，FLOAT 和 DOUBLE 类型将被保存为硬件所支持的最大精度。

DECIMAL 的默认 D 值为 0、M 值为 10。

类型名称	说明	存储需求
FLOAT	单精度浮点数	4 bytes
DOUBLE	双精度浮点数	8 bytes
DECIMAL(M, D), DEC	压缩的“严格”定点数	M+2 bytes

FLOAT 类型的取值范围如下：

• 有符号的取值范围：-3.402823466E+38～-1.175494351E-38。

• 无符号的取值范围：0、 -1.175494351E-38 ～-3.402823466E+38。

DOUBLE 类型的取值范围如下：

• 有符号的取值范围：-1.7976931348623157E+308～-2.2250738585072014E-308。

• 无符号的取值范围：0 和 -2.2250738585072014E-308～-1.7976931348623157E+308。

DECIMAL 可能的最大取值范围与 DOUBLE 相同，但是有效的取值范围由 M 和 D 决定。如果改变 M 而固定 D，则取值范围将随 M 的变大而变大。

不论是定点还是浮点类型，如果用户指定的精度超出精度范围，则会四舍五入进行处理。

FLOAT 和 DOUBLE 在不指定精度时，默认会按照实际的精度（由计算机硬件和操作系统决定），DECIMAL 如果不指定精度，默认为（10，0）。

浮点数相对于定点数的优点是在长度一定的情况下，浮点数能够表示更大的范围；缺点是会引起精度问题。

在对精度要求比较高的时候（如货币、科学数据），使用 DECIMAL 的类型比较好，另外两个浮点数进行减法和比较运算时也容易出问题，所以在使用浮点数时需要注意，并尽量避免做浮点数比较。

日期和时间类型

在MySQL中，有多处表示日期的数据类型：YEAR、TIME、DATE、DTAETIME、TIMESTAMP。

每一个类型都有合法的取值范围，当指定不合法的MySQL无法表示的值时，系统将“零”值插入数据库中

类型	范围	格式	用途	存储
YEAR	1901~2155	YYYY	年份值	1 bytes
DATE	1000-01-01~9999-12-31	YYYY-MM-DD	日期值	3 bytes
TIME	‘-838:59:59’~‘838:59:59’	HH:MM:SS	时间值或持续时间	3 bytes
DATETIME	‘1000-01-01 00:00:00’ ~ ‘9999-12-31 23:59:59’	YYYY-MM-DD hh:mm:ss	混合日期和时间值	8 bytes
TIMESTAMP	‘1970-01-01 00:00:01’ UTC ~ ‘2038-01-19 03:14:07’ UTC	YYYY-MM-DD hh:mm:ss	混合日期和时间值，时间戳	4 bytes

YEAR类型

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

• 以 4 位字符串或者 4 位数字格式表示的 YEAR，范围为 ‘1901’～’2155’。输入格式为 ‘YYYY’ 或者 YYYY，例如，输入 ‘2010’ 或 2010，插入数据库的值均为 2010。

• 以 2 位字符串格式表示的 YEAR，范围为 ‘00’ 到 ‘99’。‘00’～’69’ 和 ‘70’～’99’ 范围的值分别被转换为 2000～2069 和 1970～1999 范围的 YEAR 值。‘0’ 与 ‘00’ 的作用相同。插入超过取值范围的值将被转换为 2000

• 以 2 位数字表示的 YEAR，范围为 1～99。1～99 和 70～99 范围的值分别被转换为 2001～2069 和 1970～1999 范围的 YEAR 值。注意，在这里 0 值将被转换为 0000，而不是 2000。

两位整数范围与两位字符串范围稍有不同。例如，插入 3000 年，读者可能会使用数字格式的 0 表示 YEAR，实际上，插入数据库的值为 0000，而不是所希望的 3000。只有使用字符串格式的 ‘0’ 或 ‘00’，才可以被正确解释为 3000，非法 YEAR值将被转换为 0000。

TIME 类型

TIME 类型用于只需要时间信息的值，在存储时需要 3 个字节。格式为 **HH:MM:SS。HH 表示小时，MM 表示分钟，SS 表示秒 。

TIME 类型的取值范围为 -838：59：59～838：59：59，小时部分如此大的原因是 TIME 类型不仅可以用于表示一天的时间（必须小于 24 小时），还可能是某个事件过去的时间或两个事件之间的时间间隔（可大于 24 小时，或者甚至为负）。

可以使用各种格式指定 TIME 值，如下所示。

• 'D HH:MM:SS' 格式的字符串。还可以使用这些“非严格”的语法：
  'HH:MM:SS'、'HH:MM'、'D HH' 或 'SS'。这里的 D 表示日，可以取 0～34 之间的值。在插入数据库时，D 被转换为小时保存，格式为 “Dx24+HH”

• 'HHMMSS' 格式、没有间隔符的字符串或者 HHMMSS 数字格式的数值，假定是有意义的时间。例如，‘101112’ 被理解为’10：11：12’，但是 ‘106112’ 是
  不合法的（它有一个没有意义的分钟部分），在存储时将变为 00：00：00。

为 TIME 列分配简写值时应注意：如果没有冒号，MySQL 解释值时，假定最右边的两位表示秒。（MySQL 解释 TIME 值为过去的时间而不是当前的时间）。

例如，读者可能认为 ‘1112’ 和 1112 表示 11：12：00（即 11 点过 12 分钟），但MySQL 将它们解释为 00：11：12（即 11 分 12 秒）。同样 ‘12’ 和 12 被解释为00：00：12。相反，TIME 值中如果使用冒号则肯定被看作当天的时间，也就是说，‘11：12’ 表示 11：12：00，而不是 00：11：12

DATE类型

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

在给 DATE 类型的字段赋值时，可以使用字符串类型或者数字类型的数据插入，只要符合 DATE 的日期格式即可。

• 以 'YYYY-MM-DD' 或者 'YYYYMMDD' 字符串格式表示的日期，取值范围为 ‘1000-01-01’～’9999-12-3’。例如，输入 ‘2015-12-31’ 或者 ‘20151231’，插入数据库的日期为2015-12-31。

• 以 'YY-MM-DD' 或者 'YYMMDD'字符串格式表示日期，在这里YY表示两位的年值。MySQL 解释两位年值的规则：‘00～69’ 范围的年值转换为 ‘2000_{2069’*，*'70}99’ 范围的年值转换为 ‘1970～1999’。
  例如，输入 ‘15-12-31’，插入数据库的日期为 2015-12-31；
  输入 ‘991231’，插入数据库的日期为 1999-12-31。

• 以 YYMMDD 数字格式表示的日期，与前面相似，00~69 范围的年值转换为 2000～2069，70～99 范围的年值转换为 1970～1999。
  例如，输入 151231，插入数据库的日期为 2015-12-31，输入 991231，插入数据库的日期为 1999-12-31。

• 使用 CURRENT_DATE 或者 NOW()，插入当前系统日期。

MySQL 允许“不严格”语法：任何标点符号都可以用作日期部分之间的间隔符例如，‘98-11-31’、‘98.11.31’、‘98/11/31’和’98@11@31’ 是等价的，这些值也可以正确地插入数据库。

DATETIME 类型

DATETIME 类型用于需要同时包含日期和时间信息的值，在存储时需要 8 个字节。日期格式为 'YYYY-MM-DD HH:MM:SS'，其中 YYYY 表示年，MM 表示月，DD 表示日，HH 表示小时，MM 表示分钟，SS 表示秒。

在给 DATETIME 类型的字段赋值时，可以使用字符串类型或者数字类型的数据插入，只要符合 DATETIME 的日期格式即可，如下所示。

• 以 'YYYY-MM-DD HH：MM：SS' 或者 'YYYY-MM-DD HH:MM:SS' 字符串格式表示的日期，取值范围为 ‘1000-01-01 00：00：00’～’9999-12-3 23：59：59’。
  例如，输入 ‘2014-12-31 05：05：05’ 或者 '20141231050505’，插入数据库的 DATETIME 值都为 2014-12-31 05：05：05。

• 以 'YY-MM-DD HH:MM:SS' 或者 'YY-MM-DD- HH:MM:SS' 字符串格式表示的日期，在这里 YY 表示两位的年值。与前面相同，‘00～79’ 范围的年值转换为 ‘2000～2079’，‘80～99’ 范围的年值转换为 ‘1980～1999’。
  例如，输入 ‘14-12-31 05：05：05’，插入数据库的 DATETIME 为 2014-12-31 05：05：05；输入 141231050505，插入数据库的 DATETIME 为 2014-12-31 05：05：05。

• 以 YYYY-MM-DD HH:MM:SS 或者 YY-MM-DD- HH:MM:SS 数字格式表示的日期和时间。
  例如，输入 20141231050505，插入数据库的 DATETIME 为 2014-12-31 05:05:05；输入 140505050505，插入数据库的 DATETIME 为 2014-12-31 05:05:05。

MySQL 允许“不严格”语法：任何标点符号都可用作日期部分或时间部分之间的间隔符。
例如，‘98-12-31 11:30:45’、‘98.12.31 11+30+35’、‘98/12/31 11%30%45’ 和 ‘98@12@31 11³⁰45’ 是等价的，这些值都可以正确地插入数据库。

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 。在插入数据时，要保证在合法的取值范围内。

协调世界时（英：Coordinated Universal Time，法：Temps Universel Coordonné）又称为世界统一时间、世界标准时间、国际协调时间。英文（CUT）和法文（TUC）的缩写不同，作为妥协，简称 UTC。

TIMESTAMP 与 DATETIME 除了存储字节和支持的范围不同外，还有一个最大的区别是：

• DATETIME 在存储日期数据时，按实际输入的格式存储，即输入什么就存储什么，与时区无关；

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

如果为一个 DATETIME 或 TIMESTAMP 对象分配一个 DATE 值，结果值的时间部分被设置为 ‘00：00：00’，因此 DATE 值未包含时间信息。
如果为一个 DATE 对象分配一个 DATETIME 或 TIMESTAMP 值，结果值的时间部分被删除，因此DATE 值未包含时间信息。

文本字符串类型

字符串类型用来存储文本字符串数据，还可以存储图片和声音的二进制数据。字符串可以区分或者不区分大小写的串比较，还可以进行正则表达式的匹配查找。

存储和检索大型文本数据可能会对数据库的性能和资源消耗产生影响。在处理文本数据时，需要合理使用和管理存储空间，以及通过适当的方式处理和优化查询操作。

MySQL中的文本字符串类型有 CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT、ENUM、SET 等。

M 可以为其指定字节

类型名称	说明	存储需求
CHAR(M)	固定长度非二进制字符串	M 字节，1<=M<=255
VARCHAR(M)	变长非二进制字符串	L+1字节，在此，L< = M和 1<=M<=255
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个字节，取决于枚举值的数目 (最大值为65535)
SET	一个设置，字符串对象可以有零个或 多个SET成员	1、2、3、4或8个字节，取决于集合 成员的数量（最多64个成员）

VARCHAR和 TEXT 类型是变长类型，其存储需求取决于列值的实际长度（在前面的表格中用 L 表示），而不是取决于类型的最大可能尺寸。

例如，一个 VARCHAR(10) 列能保存一个最大长度为 10 个字符的字符串，实际的存储需要字符串的长度 L 加上一个字节以记录字符串的长度。对于字符 “abcd”，L 是 4，而存储要求 5 个字节。

CHAR和VARCHAR类型

CHAR(M) 为固定长度字符串，在定义时指定字符串列长。当保存时，在右侧填充空格以达到指定的长度。M 表示列的长度，范围是 0～255 个字符。

例如，CHAR(4) 定义了一个固定长度的字符串列，包含的字符个数最大为 4。无论存入数据的长度为多少，所占用的空间均为4字节。当检索到 CHAR 值时，尾部的空格将被删除。

VARCHAR(M) 是长度可变的字符串，M 表示最大列的长度，M 的范围是 0～65535。VARCHAR 的最大实际长度由最长的行的大小和使用的字符集确定，而实际占用的空间为字符串的实际长度加 1。

例如，VARCHAR(50) 定义了一个最大长度为 50 的字符串，如果插入的字符串只有 10 个字符，则实际存储的字符串为 10 个字符和一个字符串结束字符，此时占用的空间为 11 字节；如果插入的字符串只有 4 个字符，那么此时占用的空间为 5 字节。
VARCHAR 在值保存和检索时尾部的空格仍保留。

TEXT 类型

TEXT 列保存非二进制字符串，如文章内容、评论等。当保存或查询 TEXT 列的值时，不删除尾部空格。

TEXT 类型分为 4 种：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。不同的 TEXT 类型的存储空间和数据长度不同。

• TINYTEXT 表示长度为 255（28-1）字符的 TEXT 列。

• TEXT 表示长度为 65535（216-1）字符的 TEXT 列。

• MEDIUMTEXT 表示长度为 16777215（224-1）字符的 TEXT 列。

• LONGTEXT 表示长度为 4294967295 或 4GB（232-1）字符的 TEXT 列。

ENUM类型

ENUM 是一种用于存储枚举值的数据类型。枚举值指的是一组有限的可能选项，可以在列定义时指定这些选项，并且每个列只能存储这些指定的枚举值之一。如果创建的成员中有空格，尾部的空格将自动被删除。

ENUM 类型的字段在取值时，能在指定的枚举列表中获取，而且一次只能取一个。

枚举值为表创建时列规定中枚举的一列值。其语法格式如下：

<字段名> ENUM( '值1', '值1', …, '值n' )
# 字段名指将要定义的字段，值 n 指枚举列表中第 n 个值。CREATE TABLE example (status ENUM('active', 'inactive', 'pending')
);

向 ENUM 列插入数据时，只能为列赋予定义的枚举值之一。例如：

INSERT INTO example (status) VALUES ('active');
INSERT INTO example (status) VALUES ('pending');# 查询包含ENUM列的数据时，返回的结果将显示存储的枚举值

如果尝试插入一个不在定义的枚举选项内的值，将会引发错误。

ENUM 值在内部用整数表示，每个枚举值均有一个索引值；列表值所允许的成员值从 1 开始编号，MySQL 存储的就是这个索引编号，枚举最多可以有 65535 个元素。

例如，定义 ENUM 类型的列（‘first’，‘second’，‘third’）:

值	索引
Null	null
“”	0
first	1
second	2
third	3

ENUM 值依照列索引顺序排列，并且空字符串排在非空字符串前，NULL 值排在其他所有枚举值前。

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

ENUM 列虽然提供了一种存储固定选项的方式，但它也有一些限制。添加新的枚举选项或者重新排序现有的选项需要执行ALTER TABLE语句，这可能会引起某些操作的复杂性。

SET类型

SET 是一种用于存储一组可选值的数据类型。允许从一个预定义的值集合中选择零个或多个值，并将其作为一组位标志存储在列中，SET 列 最多可以有 64 个成员，值为表创建时规定的一列值。

语法格式如下:

SET( '值1', '值2', …, '值n' )CREATE TABLE example (colors SET('red', 'green', 'blue')
);

使用 INSERT 或 UPDATE 语句向 SET 列插入数据时，与 ENUM 列不同的是：
可以选择一个或多个列选项。多个选项应以逗号分隔。例如：

INSERT INTO example (colors) VALUES ('red');
INSERT INTO example (colors) VALUES ('red,green');# 查询包含SET列的数据时，返回的结果将显示存储的选项
# 还可以使用LIKE运算符进行SET列的模糊匹配查询

与 ENUM 类型相同，SET 值在内部用整数表示，列表中每个值都有一个索引编号。当创建表时，SET 成员值的尾部空格将自动删除。

如果插入 SET 字段中的列值有重复，则 MySQL 自动删除重复的值；插入 SET 字段的值的顺序并不重要，MySQL 会在存入数据库时，按照定义的顺序显示；如果插入了不正确的值，默认情况下，MySQL 将忽视这些值，给出警告。

SET 类型有一些限制，例如SET列在存储和检索数据时会有一些额外的复杂性，并且在定义列后更改选项集可能会使用 ALTER TABLE 语句来进行更改。

二进制类

二进制类型可以用于存储各种二进制数据，例如图像、音频、视频、文档等

MySQL 中的二进制字符串（有时也被直接成为二进制类型）有 BIT、BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。

m 表示可以为其指定长度

类型名称	说明	存储需求
BIT(M)	位字段类型	大约 (M+7)/8 字节
BINARY(M)	固定长度二进制字符串	M 字节
VARBINARY (M)	可变长度二进制字符串	M+1 字节
TINYBLOB (M)	非常小的BLOB	L+1 字节，在此，L<2^8
BLOB (M)	小 BLOB	L+2 字节，在此，L<2^16
MEDIUMBLOB (M)	中等大小的BLOB	L+3 字节，在此，L<2^24
LONGBLOB (M)	非常大的BLOB	L+4 字节，在此，L<2^32

BIT 类型

BIT 是一种用于存储位(bit)值的数据类型。BIT 类型可以存储二进制位的序列，其中每个位可以是0或1。

当查询包含BIT列的数据时，返回的结果将显示存储的位(bit)值。

BIT 类型有两种形式：固定长度的BIT(M) 和 可变长度的BIT。

1. 固定长度的BIT(M)：表示具有固定长度 M 的位(bit)序列。在创建表时，需要指定位序列的长度 M ，范围可以是 1~64 。

# 创建一个固定长度为8的位（BIT）列
CREATE TABLE example (flags BIT(8)
);

如果为 BIT(M) 列分配的值的长度小于 M 位，在值的左边用 0 填充。例如，为 BIT(6) 列分配一个值 b’101’，其效果与分配 b’000101’ 相同。

2. 变长度的BIT：表示可以存储不定长度位(bit)序列的数据类型。这意味着可以根据需要存储不同长度的位(bit)序列。

# 创建一个可变长度的位列
CREATE TABLE example (data BIT
);

BIT 数据类型用来保存位字段值
例如: 以二进制的形式保存数据 13，13 的二进制形式为 1101，在这里需要位数至少为 4 位的 BIT 类型，即可以定义列类型为 BIT(4)。
大于二进制 1111 的数据是不能插入 BIT(4) 类型的字段中的。

默认情况下，MySQL 不可以插入超出该列允许范围的值，因而插入数据时要确保插入的值在指定的范围内。

需要注意的是，BIT类型主要用于存储小的位(bit)序列，例如存储布尔值或使用位掩码表示多个选项的情况。对于存储较大的位序列，可能需要考虑使用BLOB或其他适当的数据类型。

BINARY 和 VARBINARY 类型

BINARY 和 VARBINARY 类型类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字节字符串。

语法格式如下：

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

1. BINARY类型用于存储固定长度的二进制数据。在创建表时，需要指定BINARY列的长度，范围可以是 1~255。
   指定长度后，不足最大长度的，将在它们右边填充 “\0” 补齐，以达到指定长度。

2. VARBINARY类型用于存储可变长度的二进制数据。VARBINARY列可以存储不同长度的二进制数据，根据实际需要分配所需的空间,实际占用的空间字符串的实际长度+1。

CREATE TABLE example(data BINARY(3)
);INSERT INTO example (data) VALUES ('a');
# 插入后的列值将会是：01100001 00000000 （总共3个字节），前面是字符 ‘a’ 的二进制表示，后面被填充了一个字节的0CREATE TABLE example(data VARBINARY(255)
);

BLOB 类型

BLOB（Binary Large Object) 是一种在数据库中存储 大量 二进制数据的数据类型。在MySQL中，BLOB用于存储 大型 的 可变数量 的非结构化二进制数据，例如图像、音频、视频、文档等。

数据类型	存储范围
TINY BLOB	最大长度为255 (28-1) bytes
BLOB	最大长度为65535 (216-1) bytes
MEDIUM BLOB	最大长度为16777215 (224-1) bytes
LONG BLOB	最大长度为4294967295或4GB (231-1) bytes

BLOB列存储的是二进制字符串（字节字符串）。BLOB 列是字符集，且排序和比较基于列值字节的数值；

TEXT 列存储的是非进制字符串（字符字符串）。TEXT 列有一个字符集，且根据字符集对值进行排序和比较。

需要注意的是，存储和检索大型BLOB数据可能会对数据库的性能和资源消耗产生影响。在处理 BLOB 类型数据时，通常建议合理使用和管理存储空间，以及通过适当的方式处理和优化查询操作。

特殊类型

在数据库中，NULL 是一个特殊的值，表示缺失或未知的数据。它表示某个字段或列没有具体的值。

以下是关于 NULL 的一些重要事项：

• NULL 不等于任何值，包括它自己。所以，NULL = NULL 的比较结果是 NULL，而不是 true 或 false。

• 在条件比较中，可以使用 IS NULL 或 IS NOT NULL 来判断字段或列是否为 NULL。

• NULL 值可以在数据库表中出现，它表示该字段在特定行中没有具体的值。

• NULL 不是空字符串（''），它表示一个缺失的值，而空字符串是一个有效的字符串。

• 在进行各种计算和操作时，与 NULL 相关的结果通常为 NULL。

对于包含 NULL 值的列，需要特别小心处理，确保查询和计算不产生意外的结果。可以使用 IFNULL() 或 COALESCE() 函数来处理 NULL 值，提供默认值或进行转换。

MySQL 数据类型的选择

MySQL 提供了大量的数据类型，为了优化存储和提高数据库性能，在任何情况下都应该使用最精确的数据类型。

字符串类型是通用的数据类型，任何内容都可以保存在字符串中，数字和日期都可以表示成字符串形式。

但是也不能把所有的列都定义为字符串类型。对于数值类型，如果把它们设置为字符串类型的，会使用很多的空间。用字符串类型存储数字相较于使用数值类型存储而言效率较为低下。

另外需要注意的是，由于对数字和字符串的处理方式不同，查询结果也会存在差异。

例如，对数字的排序与对字符串的排序是不一样的：

数字 2 小于数字 11，但字符串 ‘2’ 却比字符串 ‘11’ 大。此问题可以通过把列放到数字上下文中来解决，如下面 SQL 语句：

SELECT course+ 0 as num ... ORDER BY num;

让 course 列加上 0，可以强制列按数字的方式来排序，但这么做很明显是不合理的。

如果让 MySQL 把一个字符串列当作一个数字列来对待，会迫使让列里的每一个值都执行从字符串到数字的转换，操作效率低。

而且在计算过程中使用这样的列，会导致 MySQL 不会使用这些列上的任何索引，从而进一步降低查询的速度。

在选择数据类型时，首先要考虑这个列存放的值是什么类型的。一般来说，用
数值类型列存储数字、用字符类型列存储字符串、用时态类型列存储日期和时间

数值类型

如果要存储的数字是整数（没有小数部分），则使用[[数值类型#整数类型|整数类型]]

• 如果列的取值范围是 1~99999 之间的整数，则UNSIGNED MEDIUMINT类型是最好的选择

CREATE TABLE exmple(num UNSIGNED MEDIUMINT
);

• 根据实际所需取值范围来选择适用的整数类型，选取合适的存储来提升效率

如果要存储的数字是[[数值类型#小数类型|小数类型]]（带有小数部分），则可以选用DECIMAL或浮点类型，但是一般选择FLOAT类型（浮点类型的一种）。

正确选择数据类型，不仅可以使表的存储空间变小，也会提高性能。因为与较长的列相比，较短的列的处理速度更快。当读取较短的值时，所需的磁盘读写操作会更少，并且可以把更多的键值放入内存索引缓冲区里。

如果无法获知各种可能值的范围,将来，还可以使用ALTER TABLE让该列变得更大些。

实际问题

如果数值类型需要存储的数据为货币，如人民币。在计算时，使用到的值常带有元和分两个部分。它们看起来像是浮点值，但 FLOAT 和 DOUBLE 类型都存在四舍五入的误差问题。

把货币表示成 DECIMAL(M,2) 类型，其中 M 为所需取值范围的最大宽度。这种类型的数值可以精确到小数点后 2 位。DECIMAL 的优点在于不存在舍入误差，计算是精确的。

对于电话号码、信用卡号和社会保险号都会使用非数字字符。由于空格和短划线不能直接存储到数字类型列里，除非去掉其中的非数字字符。但即使去掉了其中的非数字字符，也不能把它们存储成数值类型，以避免丢失开头的“零”。

日期和时间类型

如果同时需要记录日期和时间，则可以使用TIMESTAMP或者DATETIME类型。存储较大的日期最好使用 DATETIME。

默认情况下，当插入一条记录但并没有指定 TIMESTAMP 这个列值时，MySQL 会把 TIMESTAMP 列设为当前的时间。因此当需要插入记录和当前时间时，使用 TIMESTAMP 是方便的，另外 TIMESTAMP 在空间上比 DATETIME 更有效。

MySQL 没有提供时间部分为可选的日期类型。如果时间部分是可选的，那么可以使用 DATE 列来记录日期，再用一个单独的 TIME 列来记录时间。然后，设置 TIME 列可以为 NULL。

CREATE TABLE example(date DATE NOT NULL,  # 日期必需time TIME NULL       # 时间可选（可为NULL）
);

字符串类型

• 如果某个字符串列用于表示某种固定集合的值，那么可以考虑使用数据类型。ENUM(多个值中选取一个时) 或 SET（需要选取多个值时）。

• CHAR 是固定长度，所以它的处理速度比 VARCHAR 的速度要快，但是它的缺点就是浪费存储空间。所以对存储不大，但在速度上有要求的可以使用 CHAR 类型，反之可以使用 VARCHAR类型来实现。

• 对于 MyISAM 存储引擎，最好使用固定长度的数据列代替可变长度的数据列。这样可以使整个表静态化，从而使数据检索更快，用空间换时间。

• 对于 InnoDB 存储引擎，最好使用可变长度的数据列，因为 InnoDB 数据表的存储格式不分固定长度和可变长度，因此使用 CHAR 不一定比使用 VARCHAR 更好，但由于 VARCHAR 是按照实际的长度存储，比较节省空间，所以对磁盘 I/O 和数据存储总量比较好。

二进制类型

BLOB 是二进制字符串，TEXT 是非二进制字符串，两者均可存放大容量的信息。BLOB 主要存储图片、音频信息等，而TEXT 只能存储纯文本文件。

总结

正确选择和使用 MySQL 中的数据类型对于提高数据库性能、确保数据准确性和完整性至关重要。在选择数据类型时，需要根据业务需求和数据特性来决定，避免使用过于复杂或不必要的数据类型。同时，了解每种数据类型的优缺点和使用场景，可以帮助我们更好地设计和优化数据库结构。