跳到主要内容

SQL 数据类型

数据类型定义了列可以存储的数据种类。本章介绍 SQL 中常用的数据类型。

数值类型​

整数类型​

类型存储范围说明
TINYINT1字节-128 ~ 127小整数
SMALLINT2字节-32768 ~ 32767短整数
MEDIUMINT3字节-8388608 ~ 8388607中等整数
INT/INTEGER4字节-21亿 ~ 21亿标准整数
BIGINT8字节非常大大整数
CREATE TABLE numbers (
    tiny_col TINYINT,
    small_col SMALLINT,
    int_col INT,
    big_col BIGINT
);

INSERT INTO numbers VALUES (127, 32767, 2147483647, 9223372036854775807);

无符号整数​

MySQL 支持无符号整数（UNSIGNED），范围从 0 开始：

CREATE TABLE unsigned_example (
    id INT UNSIGNED,          -- 0 ~ 4294967295
    tiny TINYINT UNSIGNED     -- 0 ~ 255
);

-- 错误：超出范围
INSERT INTO unsigned_example VALUES (-1, 100);  -- Error

浮点类型​

类型说明
FLOAT单精度浮点数，4字节
DOUBLE双精度浮点数，8字节
DECIMAL/NUMERIC精确小数
CREATE TABLE floats (
    float_col FLOAT,
    double_col DOUBLE,
    decimal_col DECIMAL(10, 2)  -- 10位总长度，2位小数
);

INSERT INTO floats VALUES (3.14159, 3.14159265358979, 12345.67);

DECIMAL 精确计算​

对于金额等需要精确计算的场景，使用 DECIMAL：

-- 使用 DECIMAL 存储金额
CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10, 2)    -- 最大 99999999.99
);

-- FLOAT/DOUBLE 可能有精度问题
SELECT 0.1 + 0.2;          -- 可能返回 0.30000000000000004
SELECT CAST(0.1 AS DECIMAL(10,2)) + CAST(0.2 AS DECIMAL(10,2));  -- 0.30

字符串类型​

定长字符串​

CHAR(n) 固定长度，不足补空格：

CREATE TABLE char_example (
    code CHAR(10),      -- 固定10字符
    status CHAR(1)      -- 固定1字符
);

INSERT INTO char_example VALUES ('ABC', 'A');
-- 实际存储: 'ABC       ' 和 'A'

变长字符串​

VARCHAR(n) 可变长度，按实际存储：

CREATE TABLE varchar_example (
    name VARCHAR(100),      -- 最多100字符
    email VARCHAR(255)      -- 最多255字符
);

INSERT INTO varchar_example VALUES ('张三', '[email protected]');
-- 实际存储: '张三' 和 '[email protected]'

CHAR vs VARCHAR​

类型存储适用场景
CHAR固定长度手机号、身份证、状态码
VARCHAR变长长度姓名、邮箱、地址
-- 手机号：固定11位，用 CHAR
phone CHAR(11)

-- 姓名：长度不固定，用 VARCHAR
name VARCHAR(50)

-- 状态码：固定1位，用 CHAR
status CHAR(1)

大文本类型​

类型最大长度说明
TINYTEXT255字节小文本
TEXT65KB普通文本
MEDIUMTEXT16MB中等文本
LONGTEXT4GB大文本
CREATE TABLE articles (
    id INT PRIMARY KEY,
    title VARCHAR(200),
    content TEXT,            -- 文章内容
    extra LONGTEXT           -- 额外信息
);

日期时间类型​

日期类型​

类型格式范围
DATEYYYY-MM-DD1000-01-01 ~ 9999-12-31
TIMEHH:MM:SS-838:59:59 ~ 838:59:59
DATETIMEYYYY-MM-DD HH:MM:SS1000-01-01 ~ 9999-12-31
TIMESTAMPYYYY-MM-DD HH:MM:SS1970-01-01 ~ 2038-01-19
YEARYYYY1901 ~ 2155
CREATE TABLE events (
    id INT PRIMARY KEY,
    event_date DATE,
    event_time TIME,
    created_at DATETIME,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO events (id, event_date, event_time, created_at)
VALUES (1, '2024-01-15', '14:30:00', '2024-01-15 14:30:00');

DATETIME vs TIMESTAMP​

特性DATETIMETIMESTAMP
范围1000-9999年1970-2038年
存储8字节4字节
时区不转换自动转换
默认值可设置可自动更新
CREATE TABLE time_test (
    id INT PRIMARY KEY,
    dt DATETIME,
    ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- TIMESTAMP 会根据时区自动转换
-- DATETIME 存储什么就显示什么

日期函数​

-- 当前日期时间
SELECT NOW();           -- 2024-01-15 14:30:00
SELECT CURDATE();       -- 2024-01-15
SELECT CURTIME();       -- 14:30:00

-- 日期计算
SELECT DATE_ADD(NOW(), INTERVAL 7 DAY);     -- 加7天
SELECT DATE_SUB(NOW(), INTERVAL 1 MONTH);   -- 减1月
SELECT DATEDIFF('2024-01-15', '2024-01-01'); -- 相差天数

-- 日期格式化
SELECT DATE_FORMAT(NOW(), '%Y年%m月%d日');   -- 2024年01月15日

布尔类型​

MySQL 没有真正的布尔类型，使用 TINYINT(1) 模拟：

CREATE TABLE flags (
    id INT PRIMARY KEY,
    is_active BOOLEAN,      -- 实际是 TINYINT(1)
    is_deleted BOOL         -- 同上
);

INSERT INTO flags VALUES (1, TRUE, FALSE);
INSERT INTO flags VALUES (2, 1, 0);          -- 等价写法

SELECT * FROM flags WHERE is_active = TRUE;
SELECT * FROM flags WHERE is_active = 1;     -- 等价写法

二进制类型​

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

类型最大长度
BLOB65KB
MEDIUMBLOB16MB
LONGBLOB4GB
CREATE TABLE files (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    data LONGBLOB           -- 存储文件数据
);
注意

不建议在数据库中存储大文件，建议存储文件路径。

JSON 类型​

MySQL 5.7+ 和 PostgreSQL 支持 JSON 类型：

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    settings JSON,
    profile JSON
);

INSERT INTO users VALUES (
    1, 
    '张三',
    '{"theme": "dark", "language": "zh"}',
    '{"age": 25, "city": "北京"}'
);

-- 查询 JSON 字段
SELECT name, settings->>'$.theme' AS theme FROM users;

-- JSON 函数
SELECT JSON_EXTRACT(profile, '$.age') FROM users;
SELECT JSON_SET(settings, '$.theme', 'light') FROM users;

枚举和集合​

ENUM 枚举​

从预定义值中选择一个：

CREATE TABLE orders (
    id INT PRIMARY KEY,
    status ENUM('pending', 'processing', 'shipped', 'completed', 'cancelled')
);

INSERT INTO orders VALUES (1, 'pending');
INSERT INTO orders VALUES (2, 'processing');

-- 错误：不在枚举值中
INSERT INTO orders VALUES (3, 'unknown');  -- Error 或空值

SET 集合​

从预定义值中选择多个：

CREATE TABLE articles (
    id INT PRIMARY KEY,
    tags SET('tech', 'life', 'travel', 'food')
);

INSERT INTO articles VALUES (1, 'tech,life');
INSERT INTO articles VALUES (2, 'travel,food');

-- 查询
SELECT * FROM articles WHERE FIND_IN_SET('tech', tags);

类型转换​

使用 CAST 或 CONVERT 函数：

-- 字符串转数字
SELECT CAST('123' AS UNSIGNED);
SELECT CONVERT('123', UNSIGNED);

-- 数字转字符串
SELECT CAST(123 AS CHAR);

-- 日期转换
SELECT CAST('2024-01-15' AS DATE);
SELECT STR_TO_DATE('2024-01-15', '%Y-%m-%d');

PostgreSQL 特有类型​

PostgreSQL 提供更丰富的类型：

-- 数组类型
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    tags TEXT[]              -- 文本数组
);

INSERT INTO posts (tags) VALUES (ARRAY['tech', 'sql']);
INSERT INTO posts (tags) VALUES ('{tech,sql}');

-- UUID 类型
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid()
);

-- 网络地址类型
CREATE TABLE network (
    ip INET,                 -- IP地址
    mac MACADDR              -- MAC地址
);

数据类型选择建议​

场景推荐类型
主键INT AUTO_INCREMENT 或 BIGINT
金额DECIMAL(10, 2)
手机号CHAR(11)
邮箱VARCHAR(255)
姓名VARCHAR(50)
文章内容TEXT
创建时间DATETIME 或 TIMESTAMP
状态ENUM 或 TINYINT
开关BOOLEAN
JSON 数据JSON

小结​

本章我们学习了：

  1. 数值类型（整数、浮点、DECIMAL）
  2. 字符串类型（CHAR、VARCHAR、TEXT）
  3. 日期时间类型（DATE、DATETIME、TIMESTAMP）
  4. 布尔类型
  5. JSON 类型
  6. 枚举和集合
  7. 类型转换

练习​

  1. 创建一个产品表，选择合适的数据类型
  2. 设计一个订单表，包含金额、日期、状态字段
  3. 练习日期函数的使用
  4. 使用 JSON 类型存储用户配置