MySQL数据类型

2025-10-16 19:23:00
丁国栋
原创 33
摘要：本文记录和汇总常用的数据类型、长度和注意事项等。

MySQL 中常用的字段数据类型、其含义、长度/取值范围、用法以及重要的注意事项。

MySQL 数据类型主要分为三大类：数值类型、日期和时间类型、字符串类型。

一、 数值类型

数值类型用于存放数字，可以分为整数类型和浮点数/小数类型。

1. 整数类型

数据类型 长度/取值范围（有符号） 长度/取值范围（无符号） 用法与注意事项
TINYINT -128 到 127 0 到 255 用法：小整数值，如状态码（0-禁用，1-启用）、年龄等。
注意：长度指定（如TINYINT(3)）仅影响显示宽度，不影响存储范围。(M) 表示当字段开启 ZEROFILL 时，显示的最小宽度。
SMALLINT -32768 到 32767 0 到 65535 用法：较小的整数，如年份、小城市人口数等。
MEDIUMINT -8388608 到 8388607 0 到 16777215 用法：中等大小的整数。
INT / INTEGER -2147483648 到 2147483647 0 到 4294967295 用法：最常用的整数类型，适用于大多数整数场景，如自增主键ID、数量、用户ID等。
BIGINT -2^63 到 2^63-1 0 到 2^64-1 用法：非常大的整数，如天文数字、社交平台的唯一ID（雪花算法ID）。

整数类型通用注意事项：

  • 自增属性：常与 AUTO_INCREMENT 一起用于主键。
  • 无符号：使用 UNSIGNED 关键字限定为非负数，可以扩大正数的表示范围。
  • 选择原则：在满足需求的前提下，选择占用空间最小的类型。例如状态字段用TINYINT，主键ID用INT或BIGINT。

2. 浮点数和定点数类型

数据类型 用法与描述 注意事项
FLOAT(M, D) 单精度浮点数。M是总位数，D是小数点后的位数。 存在精度损失问题。在进行精确计算（如货币）时不要使用。计算是近似的。
DOUBLE(M, D) 双精度浮点数。精度比FLOAT更高。 同样存在精度损失。适用于科学计算，不适用于金融计算。
DECIMAL(M, D) 定点数，以字符串形式存储精确值。M是总位数（精度），最大65；D是小数点后的位数（标度），最大30。 用法：适用于需要精确计算的数值，如价格、金额等金融数据。
注意：相比FLOAT/DOUBLE，DECIMAL会占用更多的存储空间且计算速度稍慢，但能保证精确性。

二、 日期和时间类型

数据类型 格式 取值范围 用法与注意事项
DATE YYYY-MM-DD 1000-01-01 到 9999-12-31 用法：只存储日期，不包含时间。例如生日、创建日期。
TIME HH:MM:SS -838:59:59 到 838:59:59 用法：只存储时间，可表示时间间隔或一天中的某个时刻。
DATETIME YYYY-MM-DD HH:MM:SS 1000-01-01 00:00:00 到 9999-12-31 23:59:59 用法：存储日期和时间的组合。与时区无关，存储和检索的是什么值就是什么值。
TIMESTAMP YYYY-MM-DD HH:MM:SS 1970-01-01 00:00:01 UTC 到 2038-01-19 03:14:07 UTC 用法：存储时间戳，与时区有关，存入时会被转换为UTC，查询时再转换为当前时区。
注意：1. 有 2038年问题。2. 取值范围比DATETIME小。3. 通常用于自动记录行的创建或最后更新时间（可设置默认值CURRENT_TIMESTAMP和ON UPDATE）。
YEAR YYYY 1901 到 2155 用法：只存储年份。

日期时间类型注意事项：

  • 首选推荐：通常建议使用 DATETIME 或 TIMESTAMP。如果需要自动更新和时间戳特性，且确定值在2038年之前，可用TIMESTAMP；否则用DATETIME。
  • 时区问题：如果应用涉及多时区，必须清楚DATETIME和TIMESTAMP的区别。

三、 字符串（文本和二进制）类型

1. 短文本/字符串类型

数据类型 最大长度 用法与描述 注意事项
CHAR(M) M 字符，0 <= M <= 255 定长字符串。如果存入的字符串长度小于M，会用空格在右侧填充到指定长度。 用法：适合存储长度固定或几乎固定的短字符串，如MD5哈希值（32位）、国家代码（如’CN‘）、枚举代码（如’M‘， ’F‘）。
优点：存取速度比VARCHAR快（因为是定长的）。
缺点：会浪费存储空间。
VARCHAR(M) M 字符，0 <= M <= 65,535（受行最大长度限制，约64K） 变长字符串。只占用实际字符串长度+1或2个字节的长度标识。 用法：最常用的字符串类型，适用于长度变化较大的字符串，如姓名、标题、描述等。
优点：节省存储空间。
注意：1. M指的是字符数，而不是字节数（与编码有关，utf8mb4下，一个字符最多占4字节）。2. 行长度不能超过65535字节。
TINYTEXT 255 字节 短文本字符串。 类似于VARCHAR，但不能有默认值。适合存储非常短的、不需要索引的文本。
TEXT 65,535 字节（约64KB） 长文本数据。 用法：存储大段文本，如文章内容、产品详细描述。
注意：1. TEXT类型会存储在行外，影响查询效率。2. 排序和查询时只能使用前缀索引。

2. 长文本/二进制类型

数据类型 最大长度 用法与描述
MEDIUMTEXT 16,777,215 字节（约16MB） 中等长度的文本数据。
LONGTEXT 4,294,967,295 字节（约4GB） 极大文本数据。
BLOB / MEDIUMBLOB / LONGBLOB 同对应的TEXT类型 二进制大对象，用于存储图片、音频、视频等二进制文件。

长文本/二进制类型注意事项：

  • 强烈不推荐直接将大文件（如图片、视频）以BLOB类型存入数据库。这会导致数据库体积庞大，备份和恢复缓慢。最佳实践是将文件存储在文件系统或对象存储（如AWS S3、阿里云OSS）中，而在数据库中只存储文件的URL路径。
  • TEXT和BLOB类型的字段在排序、分组等操作上效率较低。

3. 枚举和集合类型

数据类型 用法 注意事项
ENUM(‘value1’， ‘value2’, …) 枚举类型。只能从预定义的列表中选择一个值。 用法：适合存储固定选项的值，如性别(‘male‘， ’female‘)、订单状态(’pending‘， ’paid‘， ’shipped‘)。
优点：存储紧凑（内部用整数表示）。
缺点：修改枚举值需要ALTER TABLE，不灵活。
SET(‘value1’， ‘value2’, …) 集合类型。可以从预定义的列表中选择零个或多个值。 用法：适合存储多选项，如用户标签(’food‘, ’travel‘, ’music‘)。
缺点：同样不灵活，设计需谨慎。

总结和建议

  1. 合适即最好：选择最精确、占用空间最小的数据类型。例如，IP地址可以存储为INT UNSIGNED（使用INET_ATON()和INET_NTOA()函数转换）或VARCHAR(45)。
  2. 精确数字用DECIMAL：涉及金钱、金融等需要精确计算的字段，务必使用DECIMAL，避免使用FLOAT和DOUBLE。
  3. 主键选择：优先使用INT或BIGINT自增主键，除非有分布式ID需求（如雪花算法）。
  4. 文本类型选择：
    • 固定长度用CHAR（如UUID、哈希值）。
    • 可变长度用VARCHAR（绝大多数情况）。
    • 超长文本用TEXT。
  5. 文件存储：不要在数据库中直接存储大文件（BLOB），存路径是更优解。
  6. 日期时间：理解DATETIME和TIMESTAMP的区别，根据是否需要时区转换和自动更新特性来选择。
  7. 谨慎使用ENUM和SET：虽然紧凑，但修改麻烦，有时用一张小的外键表可能是更好的选择。
发表评论
博客分类