mysql中的字段类型都有哪些(ds)
MySQL 支持多种字段类型(也称为数据类型),用于定义表中列可以存储的数据种类。选择合适的类型对于数据完整性、存储效率和查询性能至关重要。主要可以分为以下几大类:
1. 数值类型 (Numeric Types)
-
整数类型 (Exact-Value):
-
TINYINT
: 非常小的整数(1字节,有符号:-128~127,无符号:0~255)。常用于状态标志、布尔值模拟。 -
SMALLINT
: 较小的整数(2字节,有符号:-32768~32767,无符号:0~65535)。 -
MEDIUMINT
: 中等大小的整数(3字节,有符号:-8388608~8388607,无符号:0~16777215)。 -
INT
/INTEGER
: 标准整数(4字节,有符号:-2147483648~2147483647,无符号:0~4294967295)。最常用。 -
BIGINT
: 大整数(8字节,有符号:-2^63~2^63-1,无符号:0~2^64-1)。 -
属性: 可指定
UNSIGNED
(非负数),ZEROFILL
(用零填充显示宽度,自动隐含UNSIGNED
),AUTO_INCREMENT
(自动递增,仅用于整数主键)。
-
-
定点数类型 (Exact-Value):
-
DECIMAL(M, D)
/NUMERIC(M, D)
: 精确的定点数。M
是总位数(精度),D
是小数点后的位数(标度)。适用于需要精确计算的值,如货币金额。存储空间可变,取决于 M 和 D。
-
-
浮点数类型 (Approximate-Value):
-
FLOAT(M, D)
: 单精度浮点数(4字节)。近似数值,存在精度损失风险。M
和D
在 MySQL 8.0.17 后已废弃,仅用于确定存储格式。 -
FLOAT
: 单精度浮点数(4字节)。 -
DOUBLE(M, D)
/DOUBLE PRECISION(M, D)
: 双精度浮点数(8字节)。近似数值,精度比FLOAT
高。M
和D
在 MySQL 8.0.17 后已废弃,仅用于确定存储格式。 -
DOUBLE
: 双精度浮点数(8字节)。
-
2. 日期和时间类型 (Date and Time Types)
-
DATE
: 日期值,格式 'YYYY-MM-DD'(3字节)。范围 '1000-01-01' 到 '9999-12-31'。 -
TIME[(fsp)]
: 时间值,格式 'hh:mm:ss[.fraction]'(3字节 + 小数秒存储)。范围 '-838:59:59.000000' 到 '838:59:59.000000'。fsp
指定小数秒精度(0-6)。 -
DATETIME[(fsp)]
: 日期和时间组合,格式 'YYYY-MM-DD hh:mm:ss[.fraction]'(5字节 + 小数秒存储)。范围 '1000-01-01 00:00:00.000000' 到 '9999-12-31 23:59:59.999999'。与时区无关。fsp
指定小数秒精度(0-6)。 -
TIMESTAMP[(fsp)]
: 时间戳(4字节 + 小数秒存储)。范围 '1970-01-01 00:00:01.000000' UTC 到 '2038-01-19 03:14:07.999999' UTC。存储的是 UTC 时间,检索时会根据当前会话的时区设置进行转换。具有自动初始化(DEFAULT CURRENT_TIMESTAMP
)和自动更新(ON UPDATE CURRENT_TIMESTAMP
)的特性。fsp
指定小数秒精度(0-6)。 -
YEAR[(4)]
: 年份值(1字节)。支持 2 位或 4 位格式(4 位是标准),范围 1901 到 2155,以及 0000。
3. 字符串类型 (String Types / Character Types)
- mysql中有了长文本类型为什么还需要短文本类型?
-
短文本/二进制:
-
CHAR(M)
: 固定长度字符串(0~255个字符)。M 定义字符长度。存储时会用空格填充到指定长度。检索时自动去除尾部空格。适合存储长度变化不大的数据(如 MD5 哈希值、国家代码)。 -
VARCHAR(M)
: 可变长度字符串(0~65535字节)。M 定义最大字符长度(实际最大字符数取决于字符集)。只存储实际数据(+1~2字节记录长度)。比CHAR
节省空间,但更新可能导致行迁移影响性能。适合存储长度变化大的数据(如姓名、地址)。 -
BINARY(M)
: 固定长度的二进制字节串(0~255字节)。存储时用0x00
填充。 -
VARBINARY(M)
: 可变长度的二进制字节串(0~65535字节)。存储实际字节(+1~2字节记录长度)。
-
-
长文本/二进制 (BLOB - Binary Large Object / TEXT):
-
TINYTEXT
/TINYBLOB
: 最大长度 255 (2⁸ - 1) 字节/字符。(约 0.25KB) -
TEXT
/BLOB
: 最大长度 65535 (2¹⁶ - 1) 字节/字符。(约 64KB) -
MEDIUMTEXT
/MEDIUMBLOB
: 最大长度 16777215 (2²⁴ - 1) 字节/字符。(约 16MB) -
LONGTEXT
/LONGBLOB
: 最大长度 4294967295 (2³² - 1) 字节/字符。(约 4GB) -
区别:
TEXT
类型存储字符数据(有字符集/排序规则),BLOB
类型存储二进制数据(无字符集)。它们通常作为独立对象存储,查询时可能涉及临时磁盘表。
-
-
枚举和集合:
-
ENUM('value1', 'value2', ...)
: 枚举类型。列值只能取定义列表中的一个值。内部存储为整数索引(1,2,...),节省空间。最多 65535 个不同值。 -
SET('value1', 'value2', ...)
: 集合类型。列值可以取定义列表中的零个或多个值的组合(用逗号分隔的字符串表示)。内部存储为位图(每个值对应一个位)。最多 64 个成员。
-
4. 空间数据类型 (Spatial Types)
用于存储地理空间数据,如点、线、多边形等。遵循 Open Geospatial Consortium (OGC) 标准。
-
GEOMETRY
: 所有空间类型的超类型。 -
POINT
: 一个点。 -
LINESTRING
: 一条曲线(由点序列定义)。 -
POLYGON
: 一个多边形。 -
MULTIPOINT
: 点集合。 -
MULTILINESTRING
: 线串集合。 -
MULTIPOLYGON
: 多边形集合。 -
GEOMETRYCOLLECTION
: 任意类型的几何对象集合。 -
这些类型使用
SPATIAL
索引进行高效查询。
5. JSON 类型 (JSON Type)
-
JSON
: 用于存储 JSON(JavaScript Object Notation)文档(MySQL 5.7.8 及以上版本)。提供自动验证、高效访问路径查询(->
,->>
)和优化存储格式(内部为二进制 BSON 类似格式)。比将 JSON 存储在TEXT
或VARCHAR
中更高效和功能强大。
常用类型总结表
类别 | 类型 | 描述 | 典型用途 |
---|---|---|---|
整数 | TINYINT |
非常小的整数 | 状态标志、布尔值模拟 |
INT |
标准整数(最常用) | 用户ID、数量计数 | |
BIGINT |
非常大的整数 | 大额交易ID、科学计算 | |
浮点数 | FLOAT |
单精度浮点数(近似值) | 科学数据、不需要精确计算的数值 |
DOUBLE |
双精度浮点数(近似值) | 精度要求稍高的科学计算 | |
定点数 | DECIMAL(M, D) |
精确的定点数 | 货币金额、需要精确计算的数值 |
日期时间 | DATE |
日期(年月日) | 出生日期、事件日期 |
TIME |
时间(时分秒) | 会议开始时间、持续时间 | |
DATETIME |
日期和时间组合(无时区概念) | 用户注册时间、订单创建时间(本地时间) | |
TIMESTAMP |
时间戳(UTC时间,自动转换时区) | 记录最后更新时间(如最后登录时间) | |
字符串(短) | CHAR(M) |
固定长度字符串(M个字符) | 国家代码(CN/US)、固定长度的编码(MD5) |
VARCHAR(M) |
可变长度字符串(最多M个字符) | 用户名、文章标题、地址(常用) | |
BINARY(M) |
固定长度二进制数据 | 加密的固定长度密钥 | |
VARBINARY(M) |
可变长度二进制数据 | 加密的可变长度数据 | |
字符串(长) | TEXT / BLOB |
长文本(TEXT )或二进制大对象(BLOB ) |
文章内容、产品描述、上传的文件 |
特殊类型 | ENUM |
枚举(只能选列表中的一个值) | 性别(男/女)、订单状态(待支付/已发货) |
SET |
集合(可以选列表中的多个值组合) | 用户兴趣标签(读书, 电影, 运动) | |
JSON |
JSON格式数据 (MySQL 5.7.8+) | 存储结构化/半结构化配置、API数据 | |
空间数据 | GEOMETRY /POINT |
地理空间数据 | 地图坐标、地理围栏区域 |
选择字段类型的关键考虑因素:
-
存储的数据性质: 是数字、文本、日期、二进制还是其他?
-
数据范围: 数值有多大?日期在什么范围?字符串最大长度是多少?
-
精度要求: 数值需要精确计算(用
DECIMAL
)还是可以近似(用FLOAT
/DOUBLE
)?时间需要秒以下精度吗? -
存储效率: 选择能满足需求的最小类型(如能用
TINYINT
就不用INT
,能用CHAR(10)
就不用VARCHAR(255)
如果长度固定)。 -
性能: 固定长度类型(
CHAR
,BINARY
)通常比可变长度(VARCHAR
,VARBINARY
,TEXT
,BLOB
)在检索上稍快。整数运算通常比浮点数快。 -
功能需求: 需要自动更新时间戳吗(
TIMESTAMP
)?需要存储 JSON 并查询内部字段吗(JSON
)?需要存储空间数据吗? -
字符集和排序规则: 对于文本类型,选择合适的字符集(如
utf8mb4
支持所有Unicode字符,包括表情符号)和排序规则(决定字符串比较和排序规则)。
理解这些类型及其特性是设计高效、可靠数据库表结构的基础。务必根据你的具体业务需求仔细选择最合适的类型。