MySQL数据类型

要保持希望在每天清晨太阳升起。 --自己

---

数据类型分类

MySQL的数据类型大概可以分为这几类，分被为整形、浮点型、布尔类型、定点数类型、字符串类型、日期与时间类型、枚举类型和集合类型。这些数据类型用于存储不同类型的数据，例如整数、小数、字符串、日期、时间、布尔值等。选择合适的数据类型对于数据库性能和数据存储是非常重要的。

类型	成员
整形	TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT
浮点型	FLOAT、DOUBLE
定点数类型	DECIMAL
字符串类型	CHAR、VARCHAR、BINARY、VARBINARY、 BLOB（用于存储二进制数据）、TEXT（用于存储文本数据）
日期与时间类型	DATE、TIME、DATETIME、TIMESTAMP、YEAR
布尔类型	BOOL、BOOLEAN
枚举类型	ENUM
集合类型	SET
空间数据类型	GEOMETRY、POINT、LINESTRING、POLYGON、etc.

整形

TINYINT

TINYINT 是 MySQL 中的一种整数数据类型，用于存储小范围的整数值。其中有符号 TINYINT 的范围是从 -128 到 127，无符号 TINYINT 的范围是从 0 到 255。TINYINT 存储空间为 1 字节，由于其小范围，常用于存储一些状态标志、开关、或者表示很小的整数值的字段。且在默认情况下，TINYINT 的默认值是 0。

示例：

• 创建一个带有 TINYINT 类型的列的表：

  create table people (
      name varchar(255),
      age tinyint);
  

  

• 插入数据：

insert into people values('张三', 18);

• 越界测试：

insert into people values('赵六', 129);
insert into people values('赵六', 128);
insert into people values('赵六', 127);

insert into people values('王二', -129);
insert into people values('王二', -128);

注意事项：

• 当只需要存储较小范围的整数时，使用 TINYINT 可以节省存储空间。
• 在设计数据库表结构时，根据实际需求选择合适的整数类型是很重要的，以兼顾存储效率和数据范围。
• 在MySQL中，整型可以指定是有符号的和无符号的，默认是有符号的。
• 可以通过UNSIGNED来说明某个字段是无符号的

总体而言，TINYINT 是一种轻量级的整数类型，适用于存储不需要太大范围的整数数据。

• SMALLINT 用于存储较小范围的整数值。有符号 SMALLINT 的范围是从 -32768 到 32767，无符号 SMALLINT 的范围是从 0 到 65535。SMALLINT 存储空间为 2 字节，适用于需要存储中等范围的整数值的字段，默认情况下，SMALLINT 的默认值是 0。

• MEDIUMINT 用于存储较大范围的整数值。有符号 MEDIUMINT 的范围是从 -8388608 到 8388607，无符号 MEDIUMINT 的范围是从 0 到 16777215。MEDIUMINT 存储空间为 3 字节，适用于需要存储相对较大范围整数值的字段。默认情况下，MEDIUMINT 的默认值是 0。

• INT 用于存储较大范围的整数值。有符号 INT 的范围是从 -2147483648 到 2147483647，无符号 INT 的范围是从 0 到 4294967295。INT 存储空间为 4 字节，适用于需要存储较大范围整数值的字段。常用于自增主键、计数器等。默认情况下，INT 的默认值是 0。

• BIGINT用于存储非常大范围的整数值。有符号 BIGINT 的范围是从 -9223372036854775808 到 9223372036854775807，无符号 BIGINT 的范围是从 0 到 18446744073709551615。BIGINT 存储空间为 8 字节，适用于需要存储非常大范围整数值的字段，例如处理大型数据集、大量计数等。默认情况下，BIGINT 的默认值是 0。

注意事项：

• BIGINT 是用于存储非常大整数值的最大范围的整数类型。
• 在设计数据库表结构时，要谨慎选择 BIGINT，因为它占用较大的存储空间，可能会影响查询性能。
• 通常，只有当确实需要处理非常大整数值时才使用 BIGINT。

浮点型

FLOAT

FLOAT 是 MySQL 中用于存储单精度浮点数的数据类型。FLOAT 使用 4 字节的存储空间。FLOAT 是单精度浮点数，提供大约 7 位有效数字的精度，范围为 -3.4028235E38 到 -1.17549435E-38、0，以及 1.17549435E-38 到 3.4028235E38，适用于需要较高精度但不需要太大范围的浮点数。常用于存储温度、价格等需要小数部分的数值。

示例：

• 创建一个带有 FLOAT 类型的列的表：

  create table demo(
      id tinyint,
      price float);
  

• 插入数据：

  insert into demo values(1, 12.90), (2, 27.89), (3, 67.111);
  

  

除此之外，还可以指定显示的位数及小数点后几位，语法为：

float(a, b)  --其中a为显示的位数，b为小数位数

例如float(4,2)表示的范围是-99.99 ~ 99.99，而MySQL在保存值时会进行四舍五入。如果插入的数为23.346，在保存时就会四舍五入为23.35。如果定义的是float(4,2) unsigned 时，因为把它指定为无符号的数，范围是 0 ~ 99.99。

在结果中可以看到，当插入数据为23.345时，他保存的结果并没有进行四舍五入，但是如果插入的数据为23.3451时他就会进行进位。

由此可以看出，当插入的数据进位的第一位（b + 1位）为5且后面没有数据（或者为0）时，不会进行四舍五入。

注意事项：

• FLOAT 是单精度浮点数，提供了相对较高的精度，但在处理非常大或非常小的数值时可能存在精度损失。
• 在比较浮点数时，由于精度问题，最好使用范围或差值比较，而非直接相等比较。

总体而言，FLOAT 是一种适用于需要较高精度但不需要太大范围的浮点数的数据类型。在选择数据类型时，要考虑具体的应用场景和对精度的要求。

与FLOAT类似，在MySQL中，DOUBLE 是一种用于存储双精度浮点数的数据类型。它可以存储较大范围的数值，包括小数点后的精度。 DOUBLE 数据类型可以存储非常大的数值，约为1.8 × 10^308，同时也可以存储负数。DOUBLE 存储的是浮点数，因此它是近似值。在进行计算时可能存在舍入误差，如果未指定默认值，DOUBLE 类型的列默认为0。并且可以对 DOUBLE 类型的列执行基本的算术运算，如加法、减法、乘法和除法。

定点数类型

DECIMAL

在MySQL中，DECIMAL 是一种用于存储精确数值的数据类型，它用于表示固定精度和小数位数的十进制数。与 DOUBLE 类型不同，DECIMAL 类型不是浮点数，而是一种精确数值类型。DECIMAL 存储的是一个以字符串形式表示的十进制数，它以固定的精度和小数位数存储。在定义 DECIMAL 类型时，需要指定总的精度（总位数）和小数位数。例如，DECIMAL(10, 2) 表示总共10位数字，其中2位是小数位。

注意：

1. 默认值： 如果未指定默认值，DECIMAL 类型的列默认为0。

2. 小数点后的零值： DECIMAL 不会在存储时去除小数点后的零。例如，2.00 将被精确存储。

3. 适用场景： DECIMAL 通常用于要求精确计算的场景，如货币、税率等。

语法：

DECIMAL(A, B) --定点数A指定长度，B表示小数点的位数

DECAML(5,2) 表示的范围是 -999.99 ~ 999.99，而DECIMAL(5,2) UNSIGNED 表示的范围为0 ~ 999.99。尽管DECIMAL和FLOAT很像，但是FLOAT和DECIMAL表示的精度不一样，FLOAT表示的精度大约是7位，DECIMAL整数最大位数为65，支持小数最大位数为30。如果B被省略，默认为0，如果A被省略，默认是10。

示例：

• 创建数据库

create table demo5(
    -> id tinyint,
    -> price1 float(10, 8),
    -> price2 decimal(10,8));

• 插入数据

insert into demo5 values(1, 10.123456789, 10.123456789);

从结果可以看出decimal的精度更高，如果希望小数的精度高，推荐优先使用decimal。

字符串类型

CHAR

在MySQL中，CHAR 是一种用于存储固定长度字符串的数据类型。因为CHAR 存储的字符串是固定长度的，这意味着无论实际字符串长度是多少，它都会被存储为指定的长度。如果实际字符串长度小于指定长度，则会用空格填充。在创建表时，需要指定 CHAR 类型的长度。例如，CHAR(10) 表示存储长度为10的字符串。由于是固定长度，CHAR 类型在存储和检索方面可能比可变长度的字符串类型（如 VARCHAR）更为高效。如果实际字符串长度小于指定长度，MySQL 会在字符串末尾填充空格。在比较字符串时，末尾的空格也会被考虑。CHAR适用于存储长度固定的数据，如固定长度的代码、标识符等。

之所以说CHAR 类型在存储和检索方面可能比 VARCHAR 类型更高效的原因主要与其固定长度的特性有关。

1. 存储空间的效率： 由于 CHAR 类型是固定长度，MySQL 在存储数据时无需额外的长度前缀来存储实际字符串的长度信息。相比之下，VARCHAR 类型需要存储额外的长度信息，以便在检索时能够正确解释字符串的边界。

2. 避免尾部空格的填充： 当使用 CHAR 类型时，MySQL 会在存储字符串时使用空格进行填充，以达到指定的固定长度。这意味着在检索时不需要去除尾部的填充空格，而 VARCHAR 类型则需要考虑实际字符串长度，可能需要进行额外的处理。

3. 定长字段的索引效率： 对于某些查询和索引操作，固定长度的字段可能更容易进行优化。例如，当使用固定长度的 CHAR 字段作为索引时，MySQL 可能更容易进行预测和优化查询操作。

4. 性能考虑： 由于 CHAR 类型的固定长度，数据库引擎可以更容易地预分配存储空间，而不需要动态调整空间大小。这在一些情况下可能带来性能优势。

需要注意的是，虽然 CHAR 类型在某些情况下可能更高效，但在其他情况下可能不如 VARCHAR。选择使用哪种类型取决于具体的应用场景和需求。如果字符串长度变化较大且可变，VARCHAR 类型可能更为合适，因为它可以动态调整存储空间，避免不必要的空间浪费。

语法：

char(n) --n表示存储的长度，最大为255

示例：

create table demo6(
    -> id tinyint,
    -> name char(255));

insert into demo6 values(1, '张三');
insert into demo6 values(2, 'Tom');

VARCHAR

在MySQL中，VARCHAR 是一种用于存储可变长度字符串的数据类型。VARCHAR 存储的字符串是可变长度的，它只使用实际字符串的存储空间，而不会填充额外的空格。这使得 VARCHAR 更为灵活，适用于存储不同长度的字符串。在创建表时，需要指定 VARCHAR 类型的最大长度。例如，VARCHAR(255) 表示最大长度为255的可变长度字符串。由于是可变长度，VARCHAR 类型在存储方面可以更为节省空间，尤其是对于较短的字符串。与 CHAR 不同，VARCHAR 不会在存储时填充额外的空格。它只存储实际的字符串内容。它适用于存储长度可变的数据，如变长文本、描述等。

语法：

varchar(n) --n表示字符长度，最大长度为65535个字节

varchar(len)的这个len值和表的编码密切相关，varchar长度可以指定为0到65535之间的值，但是有1 - 3 个字节用于记录数据大小，所以说有效字节数是65532。当我们的表的编码是utf8时，varchar(n)的参数n最大值是65532/3=21844（因为utf8中，一个字符占用3个字节），如果编码是gbk，varchar(n)的参数n最大是65532/2=32766（因为gbk中，一个字符占用2字节）。

示例：

create table demo8(
    -> id tinyint,
    -> name varchar(255));

insert into demo8 values(1, '张三'),(2, '李四');

TEXT

在MySQL中，TEXT 是一种用于存储大量文本数据的数据类型。它可以存储可变长度的字符串，适用于需要存储大段文本内容的场景。TEXT 类型用于存储可变长度的文本数据，包括较长的字符串、文档、文章等。与 VARCHAR 不同，TEXT 不需要指定最大长度。它可以存储非常大的文本数据，最多可达到 65,535 字节。TEXT 类型适合存储大块文本，但由于它是可变长度的，可能会在存储和检索时占用较多的存储空间。适用于存储较大的文本数据，如博客文章、评论、文档等。

示例：

create table demo9(
    -> id tinyint,
    -> comment text);

insert into demo9 values(1, '你干嘛哎呦');

BIT类型

BIT

在MySQL中，BIT 类型用于存储位字段，表示二进制位序列，通常用于表示布尔值或一组开关状态。BIT 类型用于存储固定长度的二进制位序列，其中每一位可以表示一个二进制位。你可以通过 BIT(len) 来指定位的数量，其中 len 表示位的数量。因此bit类型具有以下特点：

1. 位的操作： BIT 类型支持对位进行操作，包括设置、清除、翻转等。这使得它适用于表示一组开关状态或标志位。

2. 存储空间： BIT 类型的存储空间是固定的，不受存储的实际位数的影响。例如，BIT(1) 和 BIT(8) 在存储空间上都占用一个字节。

3. 适用场景： 适用于存储二进制数据，特别是当需要表示一系列布尔值或开关状态时。

语法：

bit(n) --n表示每个值的位数，范围从1到64。如果忽略，默认为1。

并且bit字段在显示时，是按照ASCII码对应的值显示 ，如下：

create table demo1(
    -> id int,
    -> flag bit(64));

insert into demo1 values(1, 1);
insert into demo1 values(1, 65);

日期与时间类型

DATE

在MySQL中，DATE 是一种用于存储日期值的数据类型。它用于表示年、月、日的日期信息。特性如下：

1. 存储日期信息： DATE 类型用于存储年、月、日的日期信息，不包含具体的时分秒。

2. 日期格式： DATE 类型的日期格式为 ‘YYYY-MM-DD’，其中 YYYY 表示四位数的年，MM 表示两位数的月，DD 表示两位数的日。

3. 合法的日期范围： MySQL DATE 类型允许存储的日期范围为 ‘1000-01-01’ 到 ‘9999-12-31’。

4. 日期操作： 可以对 DATE 类型的列执行各种日期操作，包括日期的比较、加减、格式化等。

示例：

create table demo1(
    -> id int,
    -> birth date);

insert into demo1 values(1, '1955-5-15');

DATETIME

在MySQL中，DATETIME 是一种用于存储日期和时间信息的数据类型。它包含年、月、日、时、分、秒等组成部分，可以表示精确到秒的日期时间。特性如下：

1. 存储日期时间信息： DATETIME 类型用于存储包括年、月、日、时、分、秒的日期时间信息。

2. 日期时间格式： DATETIME 类型的日期时间格式为 ‘YYYY-MM-DD HH:MM:SS’，其中 YYYY 表示四位数的年，MM 表示两位数的月，DD 表示两位数的日，HH 表示小时（24小时制），MM 表示分钟，SS 表示秒。

3. 合法的日期时间范围： MySQL DATETIME 类型允许存储的日期时间范围为 ‘1000-01-01 00:00:00’ 到 ‘9999-12-31 23:59:59’。

4. 日期时间操作： 可以对 DATETIME 类型的列执行各种日期时间操作，包括日期时间的比较、加减、格式化等。

示例：

create table demo1(
    -> id int,
    -> birth datetime);

insert into demo1 values(1, '1988-8-18 18:08:18');

TIMESTAMP

在MySQL中，TIMESTAMP 是一种用于存储日期和时间信息的数据类型，类似于 DATETIME，但有一些区别。特性如下：

1. 存储日期时间信息： TIMESTAMP 类型用于存储包括年、月、日、时、分、秒的日期时间信息，类似于 DATETIME。

2. 日期时间格式： TIMESTAMP 类型的日期时间格式为 ‘YYYY-MM-DD HH:MM:SS’，其中 YYYY 表示四位数的年，MM 表示两位数的月，DD 表示两位数的日，HH 表示小时（24小时制），MM 表示分钟，SS 表示秒。

3. 合法的日期时间范围： MySQL TIMESTAMP 类型允许存储的日期时间范围为 ‘1970-01-01 00:00:01’ 到 ‘2038-01-19 03:14:07’。这是由于 TIMESTAMP 使用的是32位整数，表示秒数，因此有一个最大的可表示时间。

4. 自动更新功能： TIMESTAMP 类型有一个特殊的自动更新功能。当插入新行时，如果 TIMESTAMP 列没有指定值，它将默认使用当前时间。

5. 时区影响： TIMESTAMP 类型存储的时间戳是基于时区的，而 DATETIME 类型是无时区的。这意味着在使用 TIMESTAMP 时，可能需要考虑时区的影响。

示例：

create table demo1(
    -> id int,
    -> birth timestamp);

insert into demo1(id) values(1);
insert into demo1 values(2, '1988-8-18 18:8:28');

枚举类型

ENUM

在MySQL中，ENUM 是一种用于定义枚举类型的数据类型。枚举类型允许在列中存储一个固定集合的可能值。特性如下：

1. 定义枚举类型： ENUM 类型允许在列中定义一个固定的、有限的可能值集合。

2. 单一值选择： ENUM 列只能存储预定义的可能值之一。例如，如果 status 是 ENUM('Active', 'Inactive', 'Pending')，则 status 列的值只能是这三个值中的一个。

3. 存储效率： ENUM 类型在存储时使用整数来表示可能值的索引，这可以在存储和检索时提供一些性能优势。

4. 默认值： 如果未指定默认值，ENUM 列的默认值将是第一个可能值。

示例：

create table demo1(
    -> name varchar(255),
    -> hobby enum('唱','跳','rap','篮球'));

insert into demo1 values('张三', '唱');
insert into demo1 values('李四', '鸡');

因为这些选项的每个选项值依次对应如下数字：1,2,3等等，最多65535个；当我们添加枚举值时，也可以添加对应的数字编号。

insert into demo1 values('李四', 2);

集合类型

SET

在MySQL中，SET 是一种用于定义集合（set）类型的数据类型。集合类型允许在列中存储多个可能值，这些值是预定义的。特性如下：

1. 定义集合类型： SET 类型允许在列中定义一个预定义的集合，列可以包含多个可能值。

2. 多值选择： SET 列可以存储预定义集合中的多个可能值。例如，如果 permissions 是 SET('Read', 'Write', 'Execute')，则 permissions 列的值可以是 ‘Read’、‘Write’、‘Read,Write’、‘Execute’、‘Read,Execute’ 等。

3. 存储效率： SET 类型在存储时使用整数来表示可能值的位掩码，这可以在存储和检索时提供一些性能优势。

4. 默认值： 如果未指定默认值，SET 列的默认值为空集。

示例：

create table demo1(
    -> name varchar(255),
    -> hobby set('唱','跳','rap','篮球'));

insert into demo1 values('张三', '唱,跳,rap');

因为这些选项的每个选项值依次对应如下数字：1,2,4,8,16,32以此类推，最多64个，所以也可以通过数字进行添加。但通常不建议在添加枚举值或者集合值的时候采用数字的方式，因为不利于阅读。

insert into demo1 values('李四', 3);
insert into demo1 values('王五', 8);

集合查询

find_ in_ set

在MySQL中，FIND_IN_SET 函数用于在逗号分隔的值列表中查找指定字符串的位置。它返回指定字符串在列表中第一次出现的位置，如果字符串未找到则返回0。FIND_IN_SET 的语法如下：

FIND_IN_SET(查找的字符串, 值列表)

• 查找的字符串：要在列表中查找的字符串。
• 值列表：逗号分隔的值列表。

如下图，直接查找是查找不出所有的打篮球的人。

而使用集合查询结果如下：

总结

文章介绍了MySQL中几种常用的数据类型，对这些常见的数据类型进行介绍分析，也标注了一些类型的特性，并结合示例演示，让读者理解起来更加容易，如果觉得文章对你有帮助的话就帮作者点一个👍呗！感谢支持。