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 地理空间数据 地图坐标、地理围栏区域

选择字段类型的关键考虑因素：

  1. 存储的数据性质： 是数字、文本、日期、二进制还是其他？

  2. 数据范围： 数值有多大？日期在什么范围？字符串最大长度是多少？

  3. 精度要求： 数值需要精确计算（用 DECIMAL）还是可以近似（用 FLOAT/DOUBLE）？时间需要秒以下精度吗？

  4. 存储效率： 选择能满足需求的最小类型（如能用 TINYINT 就不用 INT，能用 CHAR(10) 就不用 VARCHAR(255) 如果长度固定）。

  5. 性能： 固定长度类型（CHAR, BINARY）通常比可变长度（VARCHAR, VARBINARY, TEXT, BLOB）在检索上稍快。整数运算通常比浮点数快。

  6. 功能需求： 需要自动更新时间戳吗（TIMESTAMP）？需要存储 JSON 并查询内部字段吗（JSON）？需要存储空间数据吗？

  7. 字符集和排序规则： 对于文本类型，选择合适的字符集（如 utf8mb4 支持所有Unicode字符，包括表情符号）和排序规则（决定字符串比较和排序规则）。

理解这些类型及其特性是设计高效、可靠数据库表结构的基础。务必根据你的具体业务需求仔细选择最合适的类型。

posted @ 2025-07-31 08:37  飘来荡去evo  阅读(169)  评论(0)    收藏  举报