1、数据类型
MitSeek 发布于 阅读：36
MySQL支持的数据类型
1数值类型
·TINYINT 1字节
很小的整数。带符号的范围是-128到127。无符号的范围是0到255。
·SMALLINT 2字节
小的整数。带符号的范围是-32768到32767。无符号的范围是0到65535。
·MEDIUMINT 3字节
中等大小的整数。带符号的范围是-8388608到8388607。无符号的范围是0到16777215。
·INT 4字节
普通大小的整数。带符号的范围是-2147483648到2147483647。无符号的范围是0到4294967295。
·INTEGER 4字节
这是INT的同义词。
·BIGINT 8字节
大整数。带符号的范围是-9223372036854775808到9223372036854775807。无符号的范围是0到18446744073709551615。
有3种浮点类型是 FLOAT DOUBLE DECIMAC类型。
FLOAT数值类型用于表示单精度浮点数值
DOUBLE数值类型用于表示双精度浮点数值字符串类型
1. 字符串类型概览
MySQL 提供了多种字符串类型，主要分为三大类：
文本字符串：CHAR, VARCHAR, TEXT 系列
二进制字符串：BINARY, VARBINARY, BLOB 系列
特殊类型：ENUM, SET
2. CHAR 和 VARCHAR
CHAR - 定长字符串
sql
-- 语法
CHAR(M)
-- M 表示字符数，范围 0-255，默认 1
-- 示例
CREATE TABLE users (
id INT,
gender CHAR(1), -- 存储 'M' 或 'F'
country_code CHAR(2), -- 存储 'US', 'CN' 等
fixed_code CHAR(5) -- 总是占用5字符空间
);
-- 特点
- 固定长度，不足部分用空格填充
- 检索时会去掉尾部空格
- 适合存储长度固定的数据（MD5、邮编、状态码）VARCHAR - 变长字符串
-- 语法
VARCHAR(M)
-- M 表示最大字符数，范围 0-65535
-- 示例
CREATE TABLE articles (
id INT,
title VARCHAR(100), -- 最大100字符
excerpt VARCHAR(255), -- 最大255字符
author_name VARCHAR(50) -- 最大50字符
);
-- 特点
- 可变长度，只占用实际数据长度+1或2字节（长度信息）
- 保留尾部空格
- 适合存储长度变化大的数据
CHAR vs VARCHAR 对比
特性 CHAR VARCHAR
存储方式 固定长度 可变长度
尾部空格 自动去除 保留
存储效率 定长数据高效 变长数据高效
最大长度 255字符 65535字符
适用场景 邮编、代码、标识 姓名、标题、描述
3. TEXT 系列
各种TEXT类型
-- 创建示例表
CREATE TABLE blog_posts (
id INT,
title VARCHAR(255),
summary TINYTEXT, -- 255字符
content TEXT, -- 65,535字符
long_content MEDIUMTEXT, -- 16,777,215字符
book_content LONGTEXT -- 4,294,967,295字符
);
-- 插入数据
INSERT INTO blog_posts VALUES
(1, '文章标题', '简短摘要', '正文内容...', '很长的内容...', '非常非常长的内容...');
TEXT类型比较
类型 最大长度 存储需求 适用场景
TINYTEXT 255字节 L+1字节 简短描述、摘要
TEXT 65,535字节 L+2字节 文章内容、评论
MEDIUMTEXT 16,777,215字节 L+3字节 长篇内容、日志
LONGTEXT 4,294,967,295字节 L+4字节 书籍、大型文档
4. BINARY 和 VARBINARY
二进制字符串类型
sql
-- BINARY - 定长二进制
CREATE TABLE binary_data (
id INT,
token BINARY(16), -- 固定16字节，如UUID
hash BINARY(32) -- 固定32字节，如SHA256
);
-- VARBINARY - 变长二进制
CREATE TABLE variable_binary (
id INT,
image_data VARBINARY(8000), -- 最大8000字节
file_data VARBINARY(16000) -- 最大16000字节
);
5. BLOB 系列
二进制大对象
sql
-- 创建BLOB表
CREATE TABLE file_storage (
id INT,
small_file TINYBLOB, -- 最大255字节
normal_file BLOB, -- 最大65KB
medium_file MEDIUMBLOB, -- 最大16MB
large_file LONGBLOB -- 最大4GB
);
-- 使用示例（在应用程序中处理）
-- 通常用于存储图片、文件、序列化数据等
BLOB类型比较
类型 最大长度 存储需求 适用场景
TINYBLOB 255字节 L+1字节 小图标、缩略图
BLOB 65,535字节 L+2字节 普通图片、文档
MEDIUMBLOB 16,777,215字节 L+3字节 高清图片、音频
LONGBLOB 4,294,967,295字节 L+4字节 视频、大型文件
6. ENUM 和 SET
ENUM - 枚举类型
sql
-- 创建ENUM字段
CREATE TABLE products (
id INT,
name VARCHAR(100),
size ENUM('small', 'medium', 'large'), -- 三选一
status ENUM('draft', 'published', 'archived')
);
-- 插入数据
INSERT INTO products VALUES
(1, 'T-Shirt', 'medium', 'published'),
(2, 'Jacket', 'large', 'draft');
-- 查询
SELECT * FROM products WHERE size = 'medium';
SELECT * FROM products WHERE status IN ('draft', 'published');
SET - 集合类型
sql
-- 创建SET字段
CREATE TABLE users (
id INT,
username VARCHAR(50),
interests SET('reading', 'sports', 'music', 'travel', 'coding')
);
-- 插入数据（可多选）
INSERT INTO users VALUES
(1, '张三', 'reading,music'),
(2, '李四', 'sports,travel,coding'),
(3, '王五', ''); -- 空集合
-- 查询包含特定值的记录
SELECT * FROM users WHERE FIND_IN_SET('music', interests) > 0;
SELECT * FROM users WHERE interests LIKE '%sports%';
7. 实际应用示例
博客系统表设计
sql
CREATE TABLE blog_system (
-- 用户表
users (
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50) UNIQUE NOT NULL,
password CHAR(60), -- bcrypt哈希固定60字符
email VARCHAR(100) NOT NULL,
role ENUM('admin', 'editor', 'author') DEFAULT 'author',
status ENUM('active', 'inactive', 'banned') DEFAULT 'active'
),
-- 文章表
posts (
id INT PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(255) NOT NULL,
slug VARCHAR(200) UNIQUE, -- URL友好标识
content LONGTEXT,
excerpt TEXT,
status ENUM('draft', 'published', 'private') DEFAULT 'draft',
meta_keywords VARCHAR(500), -- 关键词集合
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
),
-- 分类表
categories (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
slug VARCHAR(100) UNIQUE,
description TINYTEXT
),
-- 标签表（使用SET或关联表）
posts_tags (
post_id INT,
tag_name VARCHAR(50),
PRIMARY KEY(post_id, tag_name)
)
);
8. 选择指南和最佳实践
如何选择合适的类型
sql
-- 1. 固定长度数据 → CHAR
country_code CHAR(2),
postal_code CHAR(6),
user_status CHAR(1),
-- 2. 变长文本数据 → VARCHAR
username VARCHAR(50),
email VARCHAR(100),
title VARCHAR(255),
-- 3. 中等长度文本 → TEXT
content TEXT, -- 文章内容
description TEXT, -- 产品描述
-- 4. 很长文本 → LONGTEXT
book_content LONGTEXT, -- 书籍内容
log_data LONGTEXT, -- 日志数据
-- 5. 预定义选项 → ENUM
status ENUM('active', 'inactive'),
priority ENUM('low', 'medium', 'high'),
-- 6. 多选项 → SET 或 关联表
tags SET('tech', 'science', 'art'), -- 选项少时
-- 或者用关联表（更规范）
性能优化建议
sql
-- 1. VARCHAR长度不要过度分配
-- 不好
title VARCHAR(1000) -- 过度分配
-- 好
title VARCHAR(255) -- 合适长度
-- 2. 为频繁查询的字段创建索引
CREATE INDEX idx_username ON users(username);
CREATE INDEX idx_email ON users(email(100)); -- 前缀索引
-- 3. 大文本字段单独存放（垂直分表）
-- 主表
CREATE TABLE posts (
id INT PRIMARY KEY,
title VARCHAR(255),
excerpt TEXT,
-- 其他元数据
);
-- 内容表
CREATE TABLE post_content (
post_id INT PRIMARY KEY,
content LONGTEXT
);
-- 4. 避免在WHERE条件中使用TEXT/BLOB
-- 不好
SELECT * FROM posts WHERE content LIKE '%keyword%';
-- 好（添加摘要字段）
SELECT * FROM posts WHERE excerpt LIKE '%keyword%';
9. 注意事项
字符集和排序规则
sql
-- 指定字符集
CREATE TABLE multilingual (
content VARCHAR(1000) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci
);
-- 查看字符集
SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';
存储限制
行大小限制：65,535字节（不包括TEXT/BLOB）
VARCHAR最大长度：受行限制和字符集影响
实际可用长度：utf8mb4字符集下，VARCHAR(21845) 是理论最大值日期和时间类型
1. 日期时间类型概览
MySQL 提供了5种主要的日期时间类型：
类型 格式 范围 存储大小 用途
DATE YYYY-MM-DD 1000-01-01 到 9999-12-31 3字节 存储日期
TIME HH:MM:SS[.fraction] -838:59:59 到 838:59:59 3字节 存储时间
DATETIME YYYY-MM-DD HH:MM:SS[.fraction] 1000-01-01 00:00:00 到 9999-12-31 23:59:59 5-8字节 日期+时间
TIMESTAMP YYYY-MM-DD HH:MM:SS[.fraction] 1970-01-01 00:00:01 到 2038-01-19 03:14:07 4字节 时间戳
YEAR YYYY 1901 到 2155 1字节 存储年份
2. DATE 类型
基本用法
sql
-- 创建表
CREATE TABLE events (
id INT PRIMARY KEY AUTO_INCREMENT,
event_name VARCHAR(100),
event_date DATE, -- 只存储日期
created_at DATE
);
-- 插入数据
INSERT INTO events (event_name, event_date, created_at)
VALUES
('产品发布会', '2024-03-15', CURDATE()),
('技术研讨会', '2024-04-20', '2024-01-10'),
('年度总结会', '2024-12-31', '2024-01-10');
-- 查询
SELECT * FROM events WHERE event_date = '2024-03-15';
SELECT * FROM events WHERE event_date > '2024-03-01';
DATE 函数操作
sql
-- 获取当前日期
SELECT CURDATE(); -- 2024-01-15
SELECT CURRENT_DATE(); -- 2024-01-15
-- 日期计算
SELECT DATE_ADD('2024-01-15', INTERVAL 7 DAY); -- 2024-01-22
SELECT DATE_SUB('2024-01-15', INTERVAL 1 MONTH); -- 2023-12-15
-- 提取日期部分
SELECT YEAR('2024-01-15'); -- 2024
SELECT MONTH('2024-01-15'); -- 1
SELECT DAY('2024-01-15'); -- 15
SELECT DAYNAME('2024-01-15'); -- Monday
-- 日期格式化
SELECT DATE_FORMAT('2024-01-15', '%Y年%m月%d日'); -- 2024年01月15日
3. TIME 类型
基本用法
sql
-- 创建表
CREATE TABLE schedules (
id INT PRIMARY KEY,
task_name VARCHAR(100),
start_time TIME, -- 只存储时间
duration TIME, -- 持续时间
end_time TIME
);
-- 插入数据
INSERT INTO schedules VALUES
(1, '晨会', '09:00:00', '01:00:00', '10:00:00'),
(2, '午休', '12:00:00', '01:30:00', '13:30:00'),
(3, '项目讨论', '14:30:00', '02:15:00', '16:45:00');
-- 查询
SELECT * FROM schedules WHERE start_time BETWEEN '09:00:00' AND '12:00:00';
TIME 函数操作
sql
-- 获取当前时间
SELECT CURTIME(); -- 14:30:25
SELECT CURRENT_TIME(); -- 14:30:25
-- 时间计算
SELECT ADDTIME('14:30:00', '02:15:00'); -- 16:45:00
SELECT SUBTIME('14:30:00', '01:00:00'); -- 13:30:00
-- 提取时间部分
SELECT HOUR('14:30:25'); -- 14
SELECT MINUTE('14:30:25'); -- 30
SELECT SECOND('14:30:25'); -- 25
-- 时间格式化
SELECT TIME_FORMAT('14:30:25', '%h:%i %p'); -- 02:30 PM
4. DATETIME 类型
基本用法
sql
-- 创建表（博客系统示例）
CREATE TABLE articles (
id INT PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(255),
content TEXT,
created_at DATETIME, -- 创建时间
updated_at DATETIME, -- 更新时间
published_at DATETIME -- 发布时间
);
-- 插入数据
INSERT INTO articles (title, content, created_at, updated_at, published_at)
VALUES
('MySQL教程', '内容...', NOW(), NOW(), '2024-01-15 10:00:00'),
('PHP基础', '内容...', '2024-01-14 15:30:00', '2024-01-14 16:45:00', '2024-01-14 17:00:00');
-- 查询
SELECT * FROM articles WHERE published_at >= '2024-01-15 00:00:00';
SELECT * FROM articles WHERE created_at BETWEEN '2024-01-14 00:00:00' AND '2024-01-15 23:59:59';
DATETIME 函数操作
sql
-- 获取当前日期时间
SELECT NOW(); -- 2024-01-15 14:30:25
SELECT CURRENT_TIMESTAMP(); -- 2024-01-15 14:30:25
-- 日期时间计算
SELECT DATE_ADD(NOW(), INTERVAL 1 HOUR); -- 增加1小时
SELECT DATE_SUB(NOW(), INTERVAL 30 MINUTE); -- 减少30分钟
-- 提取部分
SELECT DATE(NOW()); -- 2024-01-15（日期部分）
SELECT TIME(NOW()); -- 14:30:25（时间部分）
-- 格式化
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s'); -- 2024-01-15 14:30:25
SELECT DATE_FORMAT(NOW(), '%W, %M %d, %Y'); -- Monday, January 15, 2024
5. TIMESTAMP 类型
基本用法
sql
-- 创建表（自动时间戳）
CREATE TABLE user_actions (
id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT,
action VARCHAR(50),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- 自动设置创建时间
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP -- 自动更新
);
-- 插入数据（created_at 会自动填充）
INSERT INTO user_actions (user_id, action)
VALUES
(1, 'login'),
(2, 'view_page'),
(3, 'logout');
-- 更新数据（updated_at 会自动更新）
UPDATE user_actions SET action = 'view_profile' WHERE id = 2;
TIMESTAMP 特性
sql
-- TIMESTAMP 与时区
SET time_zone = '+08:00'; -- 设置为东八区
SELECT @@time_zone; -- 查看当前时区
-- TIMESTAMP 范围限制
INSERT INTO user_actions (user_id, action, created_at)
VALUES (4, 'test', '2038-01-19 03:14:07'); -- 最大支持时间
-- 超出范围会报错
INSERT INTO user_actions (user_id, action, created_at)
VALUES (5, 'test', '2038-01-20 00:00:00'); -- 错误！
6. YEAR 类型
基本用法
sql
-- 创建表
CREATE TABLE vehicles (
id INT PRIMARY KEY,
model VARCHAR(50),
manufacture_year YEAR, -- 4位年份
purchase_year YEAR(4) -- 明确指定4位
);
-- 插入数据
INSERT INTO vehicles VALUES
(1, 'Toyota Camry', 2020, 2021),
(2, 'Honda Civic', 2019, 2020),
(3, 'Ford Focus', 2022, 2023);
-- 查询
SELECT * FROM vehicles WHERE manufacture_year >= 2020;
7. 实际应用示例
博客系统完整设计
sql
CREATE TABLE blog_system (
-- 用户表
users (
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50) UNIQUE,
email VARCHAR(100),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
last_login DATETIME,
birth_date DATE, -- 生日
membership_expiry DATE -- 会员到期日
),
-- 文章表
articles (
id INT PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(255),
content TEXT,
status ENUM('draft', 'published', 'archived'),
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
published_at DATETIME, -- 计划发布时间
reading_time TIME -- 预计阅读时间
),
-- 评论表
comments (
id INT PRIMARY KEY AUTO_INCREMENT,
article_id INT,
user_id INT,
content TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
is_approved BOOLEAN DEFAULT FALSE
),
-- 系统日志表
system_logs (
id INT PRIMARY KEY AUTO_INCREMENT,
log_level ENUM('info', 'warning', 'error'),
message TEXT,
log_time DATETIME(6) DEFAULT CURRENT_TIMESTAMP(6), -- 微秒精度
source VARCHAR(100)
)
);
8. 日期时间函数详解
常用函数示例
sql
-- 1. 当前时间相关
SELECT NOW(), CURDATE(), CURTIME(), UNIX_TIMESTAMP();
-- 2. 日期时间提取
SELECT
YEAR(NOW()) as year,
MONTH(NOW()) as month,
DAY(NOW()) as day,
HOUR(NOW()) as hour,
MINUTE(NOW()) as minute,
SECOND(NOW()) as second;
-- 3. 日期时间计算
SELECT
DATE_ADD(NOW(), INTERVAL 1 DAY) as tomorrow,
DATE_SUB(NOW(), INTERVAL 1 WEEK) as last_week,
DATE_ADD(NOW(), INTERVAL 3 MONTH) as in_3_months;
-- 4. 日期差计算
SELECT
DATEDIFF('2024-01-20', '2024-01-15') as day_diff, -- 5
TIMEDIFF('14:30:00', '12:00:00') as time_diff; -- 02:30:00
-- 5. 日期格式化
SELECT
DATE_FORMAT(NOW(), '%Y-%m-%d') as simple_date,
DATE_FORMAT(NOW(), '%W, %M %e, %Y') as full_date,
DATE_FORMAT(NOW(), '%h:%i %p') as am_pm_time;
时间间隔类型
sql
-- INTERVAL 支持的单位
SELECT
NOW() + INTERVAL 1 MICROSECOND,
NOW() + INTERVAL 1 SECOND,
NOW() + INTERVAL 1 MINUTE,
NOW() + INTERVAL 1 HOUR,
NOW() + INTERVAL 1 DAY,
NOW() + INTERVAL 1 WEEK,
NOW() + INTERVAL 1 MONTH,
NOW() + INTERVAL 1 QUARTER,
NOW() + INTERVAL 1 YEAR;
9. 选择指南和最佳实践
如何选择合适的类型
sql
-- 1. 只需要日期 → DATE
birth_date DATE,
event_date DATE,
-- 2. 只需要时间 → TIME
start_time TIME,
duration TIME,
-- 3. 完整的日期时间（大范围） → DATETIME
created_at DATETIME,
published_at DATETIME,
-- 4. 自动时间戳、需要时区转换 → TIMESTAMP
last_modified TIMESTAMP,
login_time TIMESTAMP,
-- 5. 只需要年份 → YEAR
manufacture_year YEAR,
graduation_year YEAR
DATETIME vs TIMESTAMP 对比
特性 DATETIME TIMESTAMP
范围 1000-9999年 1970-2038年
时区 不受时区影响 自动时区转换
存储 5-8字节 4字节
默认值 不支持函数默认值 支持CURRENT_TIMESTAMP
自动更新 不支持 支持ON UPDATE
适用场景 生日、历史日期 创建时间、修改时间
性能优化建议
sql
-- 1. 为日期时间字段创建索引
CREATE INDEX idx_created_at ON articles(created_at);
CREATE INDEX idx_published_date ON articles(DATE(published_at));
-- 2. 避免在WHERE条件中使用函数（使用范围查询）
-- 不好
SELECT * FROM articles WHERE YEAR(created_at) = 2024;
-- 好
SELECT * FROM articles WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31';
-- 3. 使用合适的精度
-- 不需要微秒精度
created_at DATETIME, -- 秒级精度
-- 需要高精度
log_time DATETIME(6), -- 微秒精度
-- 4. 分区表按时间分区
CREATE TABLE logs (
id INT,
log_time DATETIME,
message TEXT
) PARTITION BY RANGE (YEAR(log_time)) (
PARTITION p2023 VALUES LESS THAN (2024),
PARTITION p2024 VALUES LESS THAN (2025)
);
10. 高级用法
时区处理
sql
-- 设置时区
SET time_zone = '+08:00'; -- 东八区（北京时间）
SET time_zone = 'Asia/Shanghai';
-- 转换时区
SELECT
NOW() as local_time,
CONVERT_TZ(NOW(), 'SYSTEM', 'UTC') as utc_time,
CONVERT_TZ(NOW(), 'SYSTEM', 'America/New_York') as ny_time;
微秒精度
sql
-- 创建支持微秒的表
CREATE TABLE high_precision_logs (
id INT PRIMARY KEY,
event_time DATETIME(6) DEFAULT CURRENT_TIMESTAMP(6), -- 6位小数
description TEXT
);
-- 插入数据
INSERT INTO high_precision_logs (description) VALUES ('精确时间记录');
生成日期序列
sql
-- 生成最近7天的日期
SELECT CURDATE() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as date
FROM
(SELECT 0 as a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) as a
CROSS JOIN
(SELECT 0 as a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) as b
CROSS JOIN
(SELECT 0 as a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) as c
WHERE (a.a + (10 * b.a) + (100 * c.a)) <= 6
ORDER BY date DESC;