MySQL 数据类型详解

MySQL 是一种广泛使用的关系型数据库管理系统，它支持多种数据类型以满足各种应用场景的需求。本文将详细介绍 MySQL 支持的数据类型、它们的使用场景以及实现原理，并通过图示帮助读者更直观地理解。

目录

1. 简介
2. 数值类型
   • 整型
   • 浮点型
   • 定点型
3. 日期和时间类型
4. 字符串类型
   • 字符串
   • 二进制字符串
5. 空间数据类型
6. JSON 数据类型
7. 枚举和集合类型
8. 总结

简介

MySQL 支持多种数据类型，分为数值类型、日期和时间类型、字符串类型、空间数据类型、JSON 数据类型以及枚举和集合类型。合理选择数据类型不仅可以提高数据库的性能，还可以节省存储空间。

数值类型

数值类型用于存储数值数据，包括整数和浮点数。MySQL 支持以下数值类型：

整型

整型数据类型用于存储整数，根据存储空间和范围的不同，分为以下几种：

• TINYINT：1 字节，范围 -128 到 127，无符号时范围 0 到 255
• SMALLINT：2 字节，范围 -32768 到 32767，无符号时范围 0 到 65535
• MEDIUMINT：3 字节，范围 -8388608 到 8388607，无符号时范围 0 到 16777215
• INT：4 字节，范围 -2147483648 到 2147483647，无符号时范围 0 到 4294967295
• BIGINT：8 字节，范围 -9223372036854775808 到 9223372036854775807，无符号时范围 0 到 18446744073709551615

每种整型数据类型都有其应用场景，例如，存储年龄可以使用 TINYINT，而存储用户 ID 则可以使用 INT 或 BIGINT。

使用场景：

• TINYINT：适用于存储小范围整数值，如布尔值（0 或 1）、年龄等。
• SMALLINT：适用于存储中小范围整数值，如年份、商品数量等。
• MEDIUMINT：适用于存储中等范围整数值，如大型系统的用户 ID、订单编号等。
• INT：适用于存储大范围整数值，如用户 ID、订单编号、库存量等。
• BIGINT：适用于存储非常大范围整数值，如银行账户余额、大型电商系统的订单编号等。

实现原理： 整型数据类型在 MySQL 内部是通过固定长度的字节数组实现的，依据存储数据的范围不同，分配的字节数也不同。无符号整型数据类型通过去掉符号位，扩大了可表示的数值范围。

浮点型

浮点型数据类型用于存储带有小数部分的数值，分为以下两种：

• FLOAT：4 字节，单精度浮点数，范围 -3.402823466E+38 到 3.402823466E+38
• DOUBLE：8 字节，双精度浮点数，范围 -1.7976931348623157E+308 到 1.7976931348623157E+308

浮点型数据类型适用于需要存储精确度较高的数值数据，例如科学计算和统计数据。

使用场景：

• FLOAT：适用于存储精度要求不高但范围较大的数值，如传感器数据、天气数据等。
• DOUBLE：适用于存储高精度数值，如科学计算、金融数据等。

实现原理： 浮点型数据类型在 MySQL 内部采用 IEEE 754 标准表示法，通过浮点数的符号位、指数位和尾数位来表示一个浮点数值。FLOAT 和 DOUBLE 的区别在于存储的字节数不同，导致它们的精度和范围也不同。

定点型

定点型数据类型用于存储精度固定的数值，主要有以下一种：

• DECIMAL：用于存储精确的定点数值，通常用于货币计算。指定格式为 DECIMAL(M, D)，其中 M 表示总位数，D 表示小数位数。

DECIMAL 数据类型非常适合需要精确计算的应用，例如财务系统。

使用场景：

• DECIMAL：适用于存储需要精确计算的小数值，如货币金额、财务报表、交易数据等。

实现原理： 定点型数据类型在 MySQL 内部通过字符串存储数值，保证了高精度。这种存储方式使得 DECIMAL 类型在进行算术运算时不会出现浮点数的精度丢失问题。

日期和时间类型

日期和时间类型用于存储日期和时间数据，分为以下几种：

• DATE：存储日期，格式为 'YYYY-MM-DD'
• TIME：存储时间，格式为 'HH:MM'
• DATETIME：存储日期和时间，格式为 'YYYY-MM-DD HH:MM'
• TIMESTAMP：存储时间戳，自动记录数据行的创建和修改时间
• YEAR：存储年份，格式为 'YYYY'

这些数据类型在处理日期和时间数据时非常有用，例如记录用户的注册时间和订单的创建时间。

使用场景：

• DATE：适用于存储日期数据，如生日、注册日期等。
• TIME：适用于存储时间数据，如上班时间、下班时间等。
• DATETIME：适用于存储日期和时间数据，如订单创建时间、日志时间等。
• TIMESTAMP：适用于存储时间戳数据，自动记录数据行的创建和修改时间，如数据更新时间、记录创建时间等。
• YEAR：适用于存储年份数据，如出版年份、生产年份等。

实现原理： 日期和时间类型在 MySQL 内部是通过整数存储的，不同的日期和时间类型对应不同的整数表示方法。例如，DATE 类型使用一个三字节整数存储日期，TIME 类型使用一个三字节整数存储时间，而 TIMESTAMP 类型使用四字节整数存储 Unix 时间戳。

字符串类型

字符串类型用于存储文本数据，分为以下几种：

字符串

• CHAR：固定长度字符串，长度范围 0 到 255 字符
• VARCHAR：可变长度字符串，长度范围 0 到 65535 字符
• TEXT：大文本数据，最大长度 65535 字符
• MEDIUMTEXT：更大文本数据，最大长度 16777215 字符
• LONGTEXT：最大文本数据，最大长度 4294967295 字符

VARCHAR 是最常用的字符串类型，因为它能够根据实际存储的数据长度动态分配存储空间，从而节省空间。

使用场景：

• CHAR：适用于存储长度固定的字符串，如国家代码、邮政编码等。
• VARCHAR：适用于存储长度可变的字符串，如用户名、电子邮件地址等。
• TEXT：适用于存储较大文本数据，如文章内容、评论内容等。
• MEDIUMTEXT：适用于存储更大文本数据，如日志数据、大型文章等。
• LONGTEXT：适用于存储最大文本数据，如书籍内容、科研报告等。

实现原理： 字符串类型在 MySQL 内部通过字节数组存储字符数据。CHAR 类型总是分配固定长度的存储空间，而 VARCHAR 类型根据实际存储的数据长度分配存储空间。TEXT 类型在内部通过二级存储结构存储数据，以支持大文本数据的存储。

二进制字符串

• BINARY：固定长度二进制数据
• VARBINARY：可变长度二进制数据
• BLOB：大二进制数据，最大长度 65535 字节
• MEDIUMBLOB：更大二进制数据，最大长度 16777215 字节
• LONGBLOB：最大二进制数据，最大长度 4294967295 字节

这些数据类型适用于存储图像、视频等二进制数据。

使用场景：

实现原理： 二进制字符串类型在 MySQL 内部通过字节数组存储二进制数据。与文本字符串类型不同，二进制字符串类型不会进行字符集转换，直接存储原始的二进制数据。这些数据类型在处理和存储非文本数据时非常有用。

空间数据类型

空间数据类型用于存储地理空间数据，MySQL 支持以下几种空间数据类型：

使用场景：

实现原理： 空间数据类型在 MySQL 内部是通过空间索引进行高效存储和查询的。MySQL 使用 R-tree 或者 MBR（最小外接矩形）索引来优化空间查询，如范围查询、邻近查询等。空间数据类型支持多种空间函数，可以进行空间关系判断、距离计算、面积计算等操作。

JSON 数据类型

MySQL 从 5.7.8 版本开始支持 JSON 数据类型，用于存储 JSON 格式的数据。JSON 数据类型允许我们存储和查询结构化的数据，同时保持灵活性和可扩展性。

使用场景：

实现原理： JSON 数据类型在 MySQL 内部是通过字符串存储 JSON 文档，并在插入时进行解析和验证。MySQL 提供了一系列的 JSON 函数，用于解析、查询和修改 JSON 文档。例如，可以使用 JSON_EXTRACT 提取 JSON 数据中的特定字段，使用 JSON_SET 修改 JSON 数据中的特定字段。

枚举和集合类型

枚举和集合类型用于存储预定义的值集合：

使用场景：

实现原理： 枚举类型在 MySQL 内部是通过整数存储的，每个预定义的值映射到一个整数。这种实现方式使得存储和查询枚举值非常高效。集合类型类似于枚举类型，不同的是它可以存储多个预定义值的组合，内部通过位掩码表示多个值的组合。

总结

MySQL 支持多种数据类型，以满足不同应用的需求。合理选择数据类型不仅可以提高数据库的性能，还可以节省存储空间。在实际应用中，应该根据数据的特点和应用场景选择合适的数据类型。

选择合适的数据类型是数据库设计中的重要一环，可以显著提升数据库的性能和可维护性。希望本文对你理解 MySQL 的数据类型有所帮助，并能在实际应用中合理选择和使用这些数据类型。

4o

• BINARY：适用于存储长度固定的二进制数据，如密码哈希、固定格式的二进制标识符等。
• VARBINARY：适用于存储长度可变的二进制数据，如文件数据、二进制序列等。
• BLOB：适用于存储较大二进制数据，如图像、音频文件等。
• MEDIUMBLOB：适用于存储更大二进制数据，如视频文件、大型图像等。
• LONGBLOB：适用于存储最大二进制数据，如完整的视频库、大型档案文件等。
• GEOMETRY：通用空间数据类型，可以存储任意几何对象。
• POINT：表示单个点，存储一个坐标对。
• LINESTRING：表示线条，存储一系列坐标点。
• POLYGON：表示多边形，存储一组边界点。
• MULTIPOINT：表示多个点。
• MULTILINESTRING：表示多条线。
• MULTIPOLYGON：表示多个多边形。
• GEOMETRYCOLLECTION：表示几何对象的集合，可以包含多种几何对象。
• POINT：适用于存储单个地理位置，如用户位置、商店位置等。
• LINESTRING：适用于存储路径数据，如道路、轨迹等。
• POLYGON：适用于存储区域数据，如城市边界、地块等。
• MULTIPOINT：适用于存储多个独立的地理位置，如多个商店、多个用户位置等。
• MULTILINESTRING：适用于存储多个独立的路径，如多条线路、多条轨迹等。
• MULTIPOLYGON：适用于存储多个独立的区域，如多个城市边界、多个地块等。
• GEOMETRYCOLLECTION：适用于存储复杂的几何数据集合，如综合地理信息、复合空间分析数据等。
• JSON：用于存储 JSON 文档
• JSON：适用于存储复杂的、结构化的数据，如配置数据、日志数据、动态属性数据等。
• ENUM：枚举类型，表示一组预定义的字符串值，例如 'small', 'medium', 'large'。
• SET：集合类型，表示一组预定义的字符串值的组合，例如 'a', 'b', 'c'。
• ENUM：适用于存储固定的单选值，如性别（'male', 'female'）、状态（'active', 'inactive'）等。
• SET：适用于存储固定的多选值组合，如兴趣爱好（'reading', 'writing', 'coding'）等。