MySQL 数据类型详解:字符串、数字、日期

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS PostgreSQL,高可用系列 2核4GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: 在 MySQL 数据库设计中,选择合适的数据类型对存储效率和查询性能至关重要。本文详细介绍了字符串、数字和日期三大类数据类型及其子类型,帮助开发者根据业务需求做出更优选择。内容涵盖 CHAR 与 VARCHAR 的区别、TEXT 和 BLOB 的使用场景、整数与浮点类型的适用范围,以及日期时间类型的特性。通过最佳实践建议,提升数据库性能并避免常见问题。

在 MySQL 中,选择合适的[数据类型]对于数据库的存储效率查询性能至关重要。MySQL 提供了**字符串(String)、数字(Numeric)和日期(Date & Time)**三大类数据类型,每种类型又有不同的子类型,以适应不同的业务需求。


1. 字符串(String)数据类型

[字符串类型]用于存储文本数据,主要分为定长(CHAR)变长(VARCHAR) ,以及大文本(TEXT 和 BLOB)

1.1 定长与变长字符串

数据类型 存储方式 特点 适用场景
CHAR(n) 定长存储(不足补空格) 访问速度快,占用空间固定 适用于固定长度的数据,如国家代码、身份证号
VARCHAR(n) 变长存储(按需分配空间) 节省存储空间,但查询时可能需要额外的存储开销 适用于长度不固定的文本,如用户昵称、电子邮件
CHAR vs. VARCHAR
  • CHAR(10)  始终占用 10 个字节(不足补空格)。
  • VARCHAR(10)  只存储实际字符,最多 10 个字节,并额外占用 1-2 字节用于存储长度信息。

一般来说:

  • 短小、长度固定的文本(如国家代码、MD5 哈希)用 CHAR
  • 长度不固定的文本(如姓名、地址)用 VARCHAR

1.2 TEXT 和 BLOB

当存储较长的文本或二进制数据时,使用 TEXT 和 BLOB 类型:

数据类型 最大存储大小 是否区分大小写 适用场景
TINYTEXT 255 字节 ✅ 区分大小写 短文本,如推文内容
TEXT 65,535 字节(64KB) ✅ 区分大小写 文章内容、评论
MEDIUMTEXT 16,777,215 字节(16MB) ✅ 区分大小写 书籍、日志记录
LONGTEXT 4GB ✅ 区分大小写 超长文本,如百科条目
TINYBLOB 255 字节 ❌ 不区分大小写 小二进制数据,如图片缩略图
BLOB 64KB ❌ 不区分大小写 图片、音频
MEDIUMBLOB 16MB ❌ 不区分大小写 大型媒体文件
LONGBLOB 4GB ❌ 不区分大小写 超大文件,如电影
TEXT vs. VARCHAR
  • TEXT 不支持 默认值,也无法使用 索引(仅支持前缀索引) ,但可以存储大量文本。
  • VARCHAR 可以索引整个字段,适用于需要频繁搜索的文本字段。

2. 数字(Numeric)数据类型

数字类型用于存储整数或小数,主要分为整数类型浮点/定点类型

2.1 整数类型

数据类型 存储大小 取值范围(无符号) 适用场景
TINYINT 1 字节 0 ~ 255 布尔值(0/1)、评分
SMALLINT 2 字节 0 ~ 65,535 小范围数值,如年龄
MEDIUMINT 3 字节 0 ~ 16,777,215 适用于 ID、自增字段
INT(INTEGER) 4 字节 0 ~ 4,294,967,295 适用于大部分场景,如用户 ID
BIGINT 8 字节 0 ~ 18,446,744,073,709,551,615 适用于超大数值,如银行账户余额

无符号(UNSIGNED) vs. 有符号

  • 默认情况下整数是有符号的(即支持负数)。
  • UNSIGNED 关键字可以使整数仅存储非负数,从而扩大可用范围。

sql

  • 体验AI代码助手
  • 代码解读
  • 复制代码
CREATE TABLE users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY
);

AI写代码sql
123

2.2 浮点与定点类型

数据类型 存储大小 适用场景
FLOAT(M, D) 4 字节 适用于非严格精度的计算,如游戏评分
DOUBLE(M, D) 8 字节 高精度计算,如科学计算
DECIMAL(M, D)(NUMERIC) 可变 财务计算,避免精度丢失
FLOAT vs. DECIMAL
  • FLOAT/DOUBLE 使用二进制存储,可能导致精度误差

sql

  • 体验AI代码助手
  • 代码解读
  • 复制代码
SELECT 0.1 + 0.2; -- 结果可能不是 0.3

AI写代码sql
1
  • DECIMAL 采用字符串存储,不会丢失精度

sql

  • 体验AI代码助手
  • 代码解读
  • 复制代码
CREATE TABLE transactions (
    amount DECIMAL(10,2) NOT NULL
);

AI写代码sql
123

建议:

  • 财务类数据使用 DECIMAL,避免计算误差。
  • 非关键计算(如统计数据)可使用 FLOATDOUBLE

3. 日期与时间(Date & Time)

MySQL 提供多个日期时间类型:

数据类型 存储大小 取值范围 适用场景
DATE 3 字节 1000-01-01 ~ 9999-12-31 生日、订单日期
DATETIME 8 字节 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59 需要精确到秒的时间,如创建时间
TIMESTAMP 4 字节 1970-01-01 00:00:01 ~ 2038-01-19 03:14:07 适用于存储 Unix 时间戳
TIME 3 字节 -838:59:59 ~ 838:59:59 记录时间间隔
YEAR 1 字节 1901 ~ 2155 适用于年份数据
DATETIME vs. TIMESTAMP
  • TIMESTAMP 受时区影响,适合存储事件发生时间。
  • DATETIME 不受时区影响,适合存储固定时间。

sql

体验AI代码助手

代码解读

复制代码

CREATE TABLE events (
    event_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

AI写代码sql
123

4. 选择数据类型的最佳实践

  1. 能用整数就不要用字符串(如性别可用 TINYINT)。
  2. 存储金额、财务数据时,使用 DECIMAL 而非 FLOAT
  3. 短文本(<255 字符)用 VARCHAR,长文本用 TEXT
  4. 索引字段尽量避免 TEXTBLOB,影响查询性能
  5. 时间戳数据尽量使用 TIMESTAMP 代替 DATETIME,节省存储空间

总结

MySQL 提供了多种数据类型,每种类型都有其适用场景。合理选择数据类型可以提升存储效率、优化查询性能,并避免精度损失。希望这篇文章能帮助你在数据库设计时做出更好的选择!


转载来源:https://juejin.cn/post/7514556093872619571

相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。 &nbsp; 相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/mysql&nbsp;
相关文章
|
关系型数据库 MySQL 数据挖掘
MYSQL日期与时间函数的实用技巧
MYSQL日期函数与时间函数是数据库操作的关键工具,可轻松处理、查询、比较和格式化日期时间数据。它们能提取日期的年、月、日等部分,便于筛选和统计;同时,也能处理时间数据,如计算时间差、获取当前时间,助力用户更好地管理时间信息。掌握这些函数,不仅能提升数据库操作效率,还能为数据分析和报表生成提供有力支持。无论初学者还是资深数据库管理员,精通MYSQL的日期和时间函数都至关重要,以满足各种数据处理需求,确保数据的准确性和高效性。
669 0
|
9天前
|
存储 关系型数据库 MySQL
MySQL数据库中进行日期比较的多种方法介绍。
以上方法提供了灵活多样地处理和对比MySQL数据库中存储地不同格式地日子信息方式。根据实际需求选择适当方式能够有效执行所需操作并保证性能优化。
76 10
|
4月前
|
关系型数据库 MySQL 定位技术
MySQL与Clickhouse数据库:探讨日期和时间的加法运算。
这一次的冒险就到这儿,期待你的再次加入,我们一起在数据库的世界中找寻下一个宝藏。
168 9
|
9月前
|
关系型数据库 MySQL
mysql中单独获取已知日期的年月日其中之一
通过上述内容,您应该能够更好地在MySQL中处理和提取日期和时间信息,以满足各种业务需求。
170 5
|
Oracle 关系型数据库 MySQL
MySQL中常用的几个日期函数
MySQL中常用的几个日期函数
105 0
|
10月前
|
关系型数据库 MySQL
Mysql 中日期比较大小的方法有哪些?
在 MySQL 中,可以通过多种方法比较日期的大小,包括使用比较运算符、NOW() 函数、DATEDIFF 函数和 DATE 函数。这些方法可以帮助你筛选出特定日期范围内的记录,确保日期格式一致以避免错误。
271 1
|
11月前
|
存储 自然语言处理 关系型数据库
mysql 8.0 日期维度表生成(可运行)
mysql 8.0 日期维度表生成(可运行)
242 2
|
10月前
|
SQL NoSQL 关系型数据库
|
11月前
|
关系型数据库 MySQL 数据处理
企业级应用 mysql 日期函数变量,干货已整理
本文详细介绍了如何在MySQL8.0中使用DATE_FORMAT函数进行日期格式的转换,包括当日、昨日及不同时间段的数据获取,并提供了实际的ETL应用场景和注意事项,有助于提升数据处理的灵活性和一致性。
115 0
|
关系型数据库 MySQL Linux
【MySQL-10】数据库函数-案例演示【字符串/数值/日期/流程控制函数】(代码演示&可cv代码)
【MySQL-10】数据库函数-案例演示【字符串/数值/日期/流程控制函数】(代码演示&可cv代码)
【MySQL-10】数据库函数-案例演示【字符串/数值/日期/流程控制函数】(代码演示&可cv代码)

推荐镜像

更多