Mysql表中的数据类型

数据类型

整数类型

数据类型 存储空间 有符号范围 无符号整数 无符号范围
TINYINT 1 字节 -128 到 127 TINYINT UNSIGNED 0 到 255
SMALLINT 2 字节 -32,768 到 32,767 SMALLINT UNSIGNED ` 0 到 65,535
MEDIUMINT 3 字节 -8,388,608 到 8,388,607 MEDIUMINT UNSIGNED ` 0 到 16,777,215
INT (INTEGER) 4 字节 --- INT UNSIGNED ---
BIGINT 8 字节 ---- BIGINT UNSIGNED ` --- 
CREATE TABLE users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    age TINYINT UNSIGNED
);
  • 有符号（Signed）：可以存储正数、负数和零
  • 无符号（Unsigned）：只能存储非负数（零和正数）

浮点数类型

数据类型 存储空间 描述
FLOAT（unsigned） 4 字节 单精度浮点数，精度约为 7 位小数
DOUBLE（unsigned） 8 字节 双精度浮点数，精度约为 15 位小数
DECIMAL(M, D)（unsigned） 变长 精确小数，M 为总位数，D 为小数位数
M：总位数（精度），最大 65
D：小数位数（标度），最大 30		 
CREATE TABLE products (
    price DECIMAL(10, 2), -- 总位数 10，小数位数 2
    weight FLOAT
);

  • 对于 FLOAT 和 DOUBLE，可以指定显示宽度和小数位数，但不会影响存储空间或精度

  • 对于 DECIMAL，须指定总位数和小数位数

  • 如果不指定，省略精度参数，MySQL 会使用默认值 DECIMAL(10,0)

    CREATE TABLE example (
    value FLOAT(7, 2), -- 显示宽度 7，小数位数 2
    price DECIMAL(10, 2) -- 总位数 10，小数位数 2
);

  • FLOAT 和 DOUBLE 是近似值类型，可能存在精度丢失问题

  • DECIMAL 是精确值类型，适合需要精确计算的场景

  • FLOAT 和 DOUBLE 的存储空间固定，分别为 4 字节和 8 字节

  • FLOAT 和 DOUBLE 的计算速度较快，适合大规模科学计算

字符串类型

数据类型 存储空间 描述
CHAR(M) M *（1-4）字节 固定长度字符串，M 为字符数（0-255）
VARCHAR(M) 实际长度 + 1或2 字节 可变长度字符串，M 为最大字符数
  1. char类型
    • CHAR 是固定长度的字符串类型。
    • 如果存储的字符串长度小于 M，MySQL 会用空格填充到 M 个字符
    • 检索时，MySQL 会自动去掉尾部空格
    • 如果存储的字符串长度超过定义的长度，MySQL 会截断超出部分
    • M：可存储字符的最大长度，范围是 0 到 255
    • 无论实际存储的字符数是多少，CHAR(M) 始终占用 M × 每个字符的最大字节数 的存储空间
      • utf8mb4：每个字符占用 1 到 4 字节
      • CHAR(10) 在 utf8mb4 字符集中始终占用 40 字节（10 × 4），即使只存储 1 个字符
    • 适合存储长度固定的字符串，例如国家代码、性别标志等
    • 由于是固定长度，CHAR 类型的查询性能通常比 VARCHAR 更高
  2. varchar类型
    • VARCHAR 是可变长度的字符串类型。
    • 存储的字符串长度可以动态变化，不会用空格填充。
    • 如果存储的字符串长度超过定义的长度，MySQL 会截断超出部分
    • M：字符串的最大长度，由于字符集、行大小限制和长度前缀的存在，M 的值会远小于 65,535
    • varchar类型存储范围是 0 到 65,535 字节（实际可用长度取决于字符集和行大小限制）
    • VARCHAR 类型占用的存储空间是实际字符串长度加上 1 或 2 个字节的长度前缀
    • 如果确实需要存储大量文本数据，可以考虑使用 TEXT 类型

布尔数据类型

  • MySQL 使用 TINYINT(1) 来存储布尔值

  • MySQL 支持 BOOL 和 BOOLEAN 关键字，但它们实际上是 TINYINT(1) 的别名

  • 可以使用 BOOL 或 TINYINT(1) 来定义布尔列

    CREATE TABLE users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    is_active BOOL,       -- 等价于 TINYINT(1)
    is_admin TINYINT(1)   -- 显式使用 TINYINT(1)
);

  • 可以使用 TRUE、FALSE、1 或 0 来插入布尔值

    INSERT INTO users (is_active, is_admin)
VALUES (TRUE, FALSE);  -- 插入 TRUE 和 FALSE

INSERT INTO users (is_active, is_admin)
VALUES (1, 0);        -- 插入 1 和 0

  • 查询时，可以使用 TRUE、FALSE、1 或 0 来过滤布尔列

    -- 查询所有活跃用户
SELECT * FROM users WHERE is_active = TRUE;

-- 查询所有非管理员用户
SELECT * FROM users WHERE is_admin = 0;

  • TINYINT(1) 占用 1 字节的存储空间

  • 虽然 TINYINT(1) 可以存储 -128 到 127 的值，但通常只用 0 和 1 来表示布尔值

  • TINYINT(1) 中的 (1) 表示显示宽度，但不会影响存储空间或取值范围

  • 从 MySQL 8.0.17 开始，显示宽度已被弃用，建议避免使用

  • TRUE 和 FALSE 是 1 和 0 的别名，TRUE 等价于 1，FALSE 等价于 0

日期和时间类型

数据类型 存储空间 格式 描述
DATE 3 字节 YYYY-MM-DD 日期值
TIME 3 字节 HH:MM:SS 时间值
DATETIME 8 字节 YYYY-MM-DD HH:MM:SS 日期和时间值
TIMESTAMP 4 字节 YYYY-MM-DD HH:MM:SS 时间戳，范围从 1970 年到 2038 年
YEAR 1 字节 YYYY 年份值 
CREATE TABLE events (
    event_date DATE,
    event_time TIME,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

时间戳类型

  • 1970-01-01 00:00:01 UTC 到 2038-01-19 03:14:07 UTC

  • 存储时间戳，通常用于记录数据的创建或修改时间

    • 存储的时间会自动转换为 UTC 时间。

    • 检索时会根据当前时区显示时间。

    • 支持自动更新（通过 DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP）

      CREATE TABLE logs (
    log_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    log_message TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

INSERT INTO logs (log_message)
VALUES ('This is a log message.');

    • DEFAULT CURRENT_TIMESTAMP在插入新记录时，若未显式指定该字段值，则自动填充当前时间戳

    • ON UPDATE CURRENT_TIMESTAMP当记录中任意字段被修改时，自动将该字段更新为当前时间戳

    • 仅当数据实际发生变更时才会触发 ON UPDATE。若执行 UPDATE 但未修改任何字段值，时间戳不会更新

    • MySQL 5.6.5 之前每个表仅允许 一个 字段使用自动时间戳功能。若未显式声明 DEFAULT，TIMESTAMP 字段默认同时包含 DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP

    • MySQL 5.6.5 及之后允许为多个 TIMESTAMP 或 DATETIME 字段设置自动更新。默认行为变更：未显式声明时，TIMESTAMP 字段不再隐式添加 ON UPDATE 属性

    • TIMESTAMP 字段会受 time_zone 系统变量影响，而 DATETIME 不会。跨时区系统需特别注意

    • TIMESTAMP 的存储空间较小（4 字节），查询性能较高

    • DATETIME 的存储空间较大（8 字节），但范围更大

文本类型

数据类型 存储空间 描述
TINYTEXT 最大 255 字节 短文本数据
TEXT 最大 65,535 字节 文本数据
MEDIUMTEXT 最大 16,777,215 字节 中等长度文本数据
LONGTEXT 最大 4GB 长文本数据

二进制类型

用于存储二进制数据（如图片、文件等）

数据类型 存储空间 描述
BINARY(M) M 字节 固定长度二进制数据
VARBINARY(M) 实际长度 + 1 字节 可变长度二进制数据

二进制最大对象（BLOB）

数据类型 存储空间 描述
TINYBLOB 最大 255 字节 短二进制数据
BLOB 最大 65,535 字节 二进制数据
MEDIUMBLOB 最大 16,777,215 字节 中等长度二进制数据
LONGBLOB 最大 4GB 长二进制数据

枚举和集合类型

  1. 枚举类型

    用于存储预定义的值列表中的一个值

    CREATE TABLE users (
    gender ENUM('Male', 'Female', 'Other')
);

  2. 集合类型

    用于存储预定义的值列表中的多个值

    CREATE TABLE users (
    hobbies SET('Reading', 'Sports', 'Music')
);

JSON类型

用于存储 JSON 格式的数据

数据类型 存储空间 描述
JSON 变长 存储 JSON 格式数据 
CREATE TABLE users (
    profile JSON
);

空间数据类型

用于存储地理空间数据（如点、线、多边形等）

数据类型 描述
GEOMETRY 任意空间类型
POINT 点
LINESTRING 线
POLYGON 多边形 
CREATE TABLE locations (
    point POINT
);
posted @ 2025-03-17 21:22  QAQ001  阅读(26)  评论(0)    收藏  举报