异常教程

SQL 通用数据类型（长文解析）

更新时间:

💡一则或许对你有用的小广告

欢迎加入小哈的星球 ，你将获得：专属的项目实战 / 1v1 提问 / Java 学习路线 / 学习打卡 / 每月赠书 / 社群讨论

  • 新项目:《从零手撸：仿小红书（微服务架构）》 正在持续爆肝中，基于 Spring Cloud Alibaba + Spring Boot 3.x + JDK 17...，点击查看项目介绍 ;
  • 《从零手撸：前后端分离博客项目（全栈开发）》 2 期已完结，演示链接： http://116.62.199.48/ ;

截止目前， 星球 内专栏累计输出 82w+ 字，讲解图 3441+ 张，还在持续爆肝中.. 后续还会上新更多项目，目标是将 Java 领域典型的项目都整一波，如秒杀系统, 在线商城, IM 即时通讯，权限管理，Spring Cloud Alibaba 微服务等等，已有 2900+ 小伙伴加入学习 ，欢迎点击围观

在数据库开发中，数据类型是构建表结构、定义字段特性的核心工具。就像快递包裹需要按尺寸分类运输一样，不同的数据类型帮助 SQL 系统高效管理存储空间、提升查询性能。对于编程初学者而言，理解 SQL 通用数据类型不仅是编写正确表结构的基础，更是避免数据溢出、类型转换错误的关键。本文将从基础到进阶，结合实际案例，系统解析如何为不同场景选择合适的 SQL 数据类型。

数据类型的核心作用：存储与计算的“隐形规则”

数据类型决定了字段能存储的数据范围、占用的存储空间，甚至影响计算逻辑。例如：

  • INT 类型的字段无法存储小数，若尝试插入 100.5 会导致错误
  • VARCHAR(10) 限制字符串长度为 10 字符，超出则截断或报错
  • DATETIME 类型能自动处理日期计算，如 DATE_ADD('2023-01-01', INTERVAL 1 MONTH)

比喻：数据类型就像快递公司的分类规则——纸箱（数值型）、信封（字符串）、冷藏箱（日期型），每种容器都有明确的容量限制和用途。

基础数值型数据类型：整数与浮点数的精准选择

整数类型（Integer Types）

类型描述存储大小典型应用场景
TINYINT1 字节，-128~1271 byte状态标志（0/1）
SMALLINT2 字节，-32768~327672 bytes短代码、小范围计数
INT4 字节，-2¹⁰⁹~2¹⁰⁹-14 bytes主键、常规计数
BIGINT8 字节，支持 19 位整数8 bytes高并发 ID、大数值计算

案例：

CREATE TABLE products (  
    id INT PRIMARY KEY,  
    stock INT CHECK (stock >= 0) -- 约束库存非负  
);

浮点与定点数（Floating & Fixed）

类型特点典型使用场景
FLOAT单精度，误差约 1e-7科学计算（如温度）
DOUBLE双精度，误差约 1e-15精密测量（如金融计算）
DECIMAL定点数，精确到指定小数位货币、比例值

误区提醒：

-- 错误：用FLOAT存储金额可能导致精度丢失  
INSERT INTO orders (price) VALUES (199.99);  

-- 正确：DECIMAL(10,2) 保留两位小数  
ALTER TABLE orders MODIFY price DECIMAL(10,2);

字符串型数据类型：文本存储的灵活性与约束

可变长度 vs 固定长度

类型特点存储优化策略
VARCHAR(N)可变长，存储实际长度+1 字节适合不固定长度的文本（如用户名）
CHAR(N)固定长度，不足补空格高频短字段（如省份代码）

性能对比：

-- VARCHAR(50) 占用空间 = 实际字符数 + 2 字节  
CREATE TABLE users (  
    name VARCHAR(50), -- 存储 "Alice" 实际占 5+2=7 字节  
    code CHAR(2)      -- 固定占 2 字节，存储 "BJ" 不变  
);

大文本类型：超越常规的存储需求

类型存储限制典型用途
TEXT~64KB短文本（如评论）
MEDIUMTEXT~16MB长文档（如文章）
LONGTEXT~4GB大文件（如日志）

注意事项：

-- 避免在索引中使用TEXT类型  
CREATE TABLE articles (  
    content TEXT,  
    -- 错误：TEXT不能作为主键  
    -- PRIMARY KEY (content),  
    id INT PRIMARY KEY  
);

日期与时间型：时间戳的精确管理

核心类型对比

类型格式示例典型用途
DATE'2023-12-25'纯日期（如生日）
DATETIME'2023-12-25 14:30'精确到秒的时间点
TIMESTAMP'2023-12-25 14:30'支持时区转换（如创建时间）
TIME'14:30:00'仅时间部分（如营业时间）

案例：

CREATE TABLE orders (  
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,  
    updated_at TIMESTAMP ON UPDATE CURRENT_TIMESTAMP  
);

时间计算的实用技巧

-- 计算用户注册天数  
SELECT DATEDIFF(NOW(), signup_date) AS days_since_signup  
FROM users  
WHERE id = 123;  

-- 获取未来一周的订单  
SELECT * FROM orders  
WHERE order_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 7 DAY);

特殊数据类型：布尔值与二进制

布尔类型（BOOLEAN）

虽然 SQL 标准定义了 BOOLEAN，但多数数据库使用 TINYINT(1) 模拟（1=真，0=假）。例如：

CREATE TABLE settings (  
    is_active BOOLEAN DEFAULT FALSE  
);

二进制类型（BLOB）

类型存储限制典型用途
BLOB~64KB小型图片或文件
MEDIUMBLOB~16MB视频缩略图
LONGBLOB~4GB大文件存储

使用场景：

-- 存储用户头像（需注意性能影响）  
CREATE TABLE profiles (  
    avatar LONGBLOB  
);

现代数据库的扩展类型：JSON与枚举

JSON类型

MySQL 5.7+ 和 PostgreSQL 等支持 JSON 类型，适合存储结构化但非固定格式的数据：

CREATE TABLE logs (  
    data JSON  
);  

INSERT INTO logs (data) VALUES (  
    '{"error": "Invalid input", "timestamp": "2023-01-01"}'  
);

ENUM类型（枚举）

限制字段只能从预设值中选择，例如：

CREATE TABLE users (  
    role ENUM('admin', 'editor', 'guest') NOT NULL  
);

数据类型选择的最佳实践

原则一：最小必要原则

  • 优先选择最小的合适类型（如用 TINYINT 替代 INT 存储布尔值）
  • 避免过度使用 VARCHAR(255)，根据实际需求调整长度

原则二：避免隐式类型转换

-- 错误：字符串与整数比较可能导致索引失效  
SELECT * FROM products WHERE price = '100';  

-- 正确：保持类型一致  
SELECT * FROM products WHERE price = 100;

原则三：预留扩展空间

  • 预估未来数据增长（如 VARCHAR(255) 可能需升级到 TEXT）
  • 对于 INT 主键，考虑是否需要 BIGINT 应对亿级数据

常见问题与解决方案

Q1：数值类型溢出如何处理？

-- 当INT(11)超出范围时  
INSERT INTO sales (revenue) VALUES (2147483648); -- 报错  

-- 解决方案：改用BIGINT  
ALTER TABLE sales MODIFY revenue BIGINT;

Q2：如何高效存储地理坐标？

-- 使用DECIMAL(10,8)存储纬度，DECIMAL(11,8)存储经度  
CREATE TABLE locations (  
    latitude DECIMAL(10,8),  
    longitude DECIMAL(11,8)  
);

Q3：字符串转时间戳失败

-- 错误输入格式  
INSERT INTO events (event_date) VALUES ('25/12/2023');  

-- 正确：使用YYYY-MM-DD格式  
INSERT INTO events (event_date) VALUES ('2023-12-25');

结论

掌握 SQL 通用数据类型如同获得一把精准的数据库设计钥匙。通过理解每种类型的空间效率、精度限制和适用场景，开发者能构建出高性能、低错误率的数据库结构。从基础的整数类型到现代的JSON存储，合理选择数据类型不仅能优化存储成本，更能为复杂查询奠定基础。建议读者通过实际创建表、插入数据并观察执行计划，逐步深化对数据类型的实践认知。

最新发布

查看更多文章