Appearance
数据类型
MySQL支持多种数据类型，用于定义表中列可以存储的数据类型。本章将详细介绍MySQL的各种数据类型及其使用方法。
数值类型
整数类型
sql
/*
* MySQL整数类型：
*
* 类型 字节 有符号范围 无符号范围
* TINYINT 1 -128 ~ 127 0 ~ 255
* SMALLINT 2 -32768 ~ 32767 0 ~ 65535
* MEDIUMINT 3 -8388608 ~ 8388607 0 ~ 16777215
* INT 4 -2147483648 ~ 2147483647 0 ~ 4294967295
* BIGINT 8 很大 很大
*/
-- 创建表演示整数类型
CREATE TABLE int_types (
id INT PRIMARY KEY AUTO_INCREMENT,
age TINYINT UNSIGNED, -- 年龄（0-255）
quantity SMALLINT, -- 数量
population INT, -- 人口
big_number BIGINT -- 大数字
);
-- 插入数据
INSERT INTO int_types (age, quantity, population, big_number)
VALUES (25, 1000, 1400000000, 9000000000000000000);
-- 查询数据
SELECT * FROM int_types;
-- 显示宽度（不影响存储范围，只影响显示）
CREATE TABLE display_width (
num INT(5) ZEROFILL -- 不足5位用0填充
);
INSERT INTO display_width VALUES (123);
SELECT * FROM display_width; -- 显示: 00123浮点类型
sql
/*
* MySQL浮点类型：
*
* 类型 字节 说明
* FLOAT 4 单精度浮点数
* DOUBLE 8 双精度浮点数
* DECIMAL 可变 精确小数
*/
-- 创建表演示浮点类型
CREATE TABLE float_types (
id INT PRIMARY KEY AUTO_INCREMENT,
price FLOAT, -- 单精度
score DOUBLE, -- 双精度
amount DECIMAL(10, 2) -- 精确小数（10位，2位小数）
);
-- 插入数据
INSERT INTO float_types (price, score, amount)
VALUES (19.99, 95.5678, 12345.67);
-- 查询数据
SELECT * FROM float_types;
-- 浮点精度问题演示
CREATE TABLE precision_test (
f_float FLOAT,
f_double DOUBLE,
f_decimal DECIMAL(20, 10)
);
INSERT INTO precision_test
VALUES (0.1 + 0.2, 0.1 + 0.2, 0.1 + 0.2);
SELECT * FROM precision_test;
/*
* 输出：
* f_float: 0.3（可能有精度误差）
* f_double: 0.3（更精确但仍有误差）
* f_decimal: 0.3000000000（精确）
*/
-- DECIMAL用于精确计算（如金额）
CREATE TABLE products (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100),
price DECIMAL(10, 2) -- 金额用DECIMAL
);字符串类型
CHAR和VARCHAR
sql
/*
* CHAR和VARCHAR的区别：
*
* CHAR(n)：固定长度，不足补空格，最大255
* VARCHAR(n)：可变长度，按实际存储，最大65535
*/
-- 创建表演示字符串类型
CREATE TABLE string_types (
id INT PRIMARY KEY AUTO_INCREMENT,
code CHAR(10), -- 固定长度（如编码）
name VARCHAR(50), -- 可变长度
description VARCHAR(1000) -- 长文本
);
-- 插入数据
INSERT INTO string_types (code, name, description)
VALUES ('ABC123', '商品A', '这是一个商品描述');
-- CHAR会自动补空格
INSERT INTO string_types (code, name, description)
VALUES ('XY', '商品B', '短编码');
-- 查看存储长度
SELECT
code,
CHAR_LENGTH(code) AS len,
name,
CHAR_LENGTH(name) AS name_len
FROM string_types;
/*
* 使用建议：
* - 固定长度用CHAR（如手机号、身份证号）
* - 可变长度用VARCHAR（如姓名、地址）
*/TEXT类型
sql
/*
* TEXT类型（用于存储大文本）：
*
* 类型 最大长度
* TINYTEXT 255字节
* TEXT 65535字节（64KB）
* MEDIUMTEXT 16MB
* LONGTEXT 4GB
*/
-- 创建表演示TEXT类型
CREATE TABLE text_types (
id INT PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(200),
content TEXT, -- 文章内容
article MEDIUMTEXT -- 长文章
);
-- 插入数据
INSERT INTO text_types (title, content, article)
VALUES (
'文章标题',
'这是一段文章内容...',
REPEAT('很长的文章内容...', 1000)
);
-- 查询数据
SELECT id, title, CHAR_LENGTH(content) AS content_len
FROM text_types;BLOB类型
sql
/*
* BLOB类型（用于存储二进制数据）：
*
* 类型 最大长度
* TINYBLOB 255字节
* BLOB 65535字节（64KB）
* MEDIUMBLOB 16MB
* LONGBLOB 4GB
*/
-- 创建表演示BLOB类型
CREATE TABLE blob_types (
id INT PRIMARY KEY AUTO_INCREMENT,
file_name VARCHAR(100),
file_data MEDIUMBLOB, -- 文件数据
file_size INT -- 文件大小
);
-- 注意：大文件建议存储路径，而非直接存储二进制数据
-- 更好的设计：
CREATE TABLE files (
id INT PRIMARY KEY AUTO_INCREMENT,
file_name VARCHAR(100),
file_path VARCHAR(255), -- 文件路径
file_size INT,
file_type VARCHAR(50)
);ENUM和SET
sql
/*
* ENUM：枚举类型，只能选一个值
* SET：集合类型，可以选择多个值
*/
-- 创建表演示ENUM和SET
CREATE TABLE enum_set_types (
id INT PRIMARY KEY AUTO_INCREMENT,
status ENUM('active', 'inactive', 'pending'), -- 状态
gender ENUM('男', '女', '未知'), -- 性别
hobbies SET('阅读', '运动', '音乐', '旅行') -- 爱好（可多选）
);
-- 插入数据
INSERT INTO enum_set_types (status, gender, hobbies)
VALUES ('active', '男', '阅读,运动');
INSERT INTO enum_set_types (status, gender, hobbies)
VALUES ('pending', '女', '音乐,旅行');
-- 查询数据
SELECT * FROM enum_set_types;
-- 使用数字索引（不推荐）
INSERT INTO enum_set_types (status, gender, hobbies)
VALUES (1, 1, 3); -- 1='active', 1='男', 3='阅读,运动'
-- 查询特定状态
SELECT * FROM enum_set_types WHERE status = 'active';
-- 查询包含特定爱好的记录
SELECT * FROM enum_set_types
WHERE FIND_IN_SET('阅读', hobbies) > 0;日期时间类型
sql
/*
* MySQL日期时间类型：
*
* 类型 格式 范围
* DATE YYYY-MM-DD 1000-01-01 ~ 9999-12-31
* TIME HH:MM:SS -838:59:59 ~ 838:59:59
* DATETIME YYYY-MM-DD HH:MM:SS 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
* TIMESTAMP YYYY-MM-DD HH:MM:SS 1970-01-01 00:00:01 ~ 2038-01-19 03:14:07
* YEAR YYYY 1901 ~ 2155
*/
-- 创建表演示日期时间类型
CREATE TABLE date_types (
id INT PRIMARY KEY AUTO_INCREMENT,
birth_date DATE, -- 出生日期
work_time TIME, -- 工作时间
create_time DATETIME, -- 创建时间
update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
year_value YEAR -- 年份
);
-- 插入数据
INSERT INTO date_types (birth_date, work_time, create_time, year_value)
VALUES ('1990-05-15', '09:00:00', '2024-01-01 10:30:00', 2024);
-- 使用函数插入当前时间
INSERT INTO date_types (birth_date, work_time, create_time, year_value)
VALUES (CURDATE(), CURTIME(), NOW(), YEAR(NOW()));
-- 查询数据
SELECT * FROM date_types;
-- TIMESTAMP自动更新演示
UPDATE date_types SET birth_date = '1991-06-20' WHERE id = 1;
SELECT id, update_time FROM date_types WHERE id = 1;日期时间函数
sql
-- 获取当前日期时间
SELECT NOW(); -- 2024-01-15 10:30:00
SELECT CURDATE(); -- 2024-01-15
SELECT CURTIME(); -- 10:30:00
SELECT SYSDATE(); -- 执行时的时间
-- 日期时间提取
SELECT YEAR(NOW()); -- 2024
SELECT MONTH(NOW()); -- 1
SELECT DAY(NOW()); -- 15
SELECT HOUR(NOW()); -- 10
SELECT MINUTE(NOW()); -- 30
SELECT SECOND(NOW()); -- 0
-- 日期格式化
SELECT DATE_FORMAT(NOW(), '%Y年%m月%d日 %H:%i:%s');
-- 输出: 2024年01月15日 10:30:00
-- 日期计算
SELECT DATE_ADD(NOW(), INTERVAL 7 DAY); -- 加7天
SELECT DATE_SUB(NOW(), INTERVAL 1 MONTH); -- 减1个月
SELECT DATEDIFF('2024-12-31', '2024-01-01'); -- 计算天数差
-- 时间戳转换
SELECT UNIX_TIMESTAMP(); -- 当前时间戳
SELECT FROM_UNIXTIME(1705305000); -- 时间戳转日期
-- 创建带时间戳的表
CREATE TABLE articles (
id INT PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(200),
content TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);JSON类型
sql
/*
* MySQL 5.7+支持JSON类型
* JSON类型可以存储和查询JSON格式数据
*/
-- 创建表演示JSON类型
CREATE TABLE json_types (
id INT PRIMARY KEY AUTO_INCREMENT,
user_info JSON, -- 用户信息
settings JSON -- 设置
);
-- 插入JSON数据
INSERT INTO json_types (user_info, settings)
VALUES (
'{"name": "张三", "age": 25, "city": "北京"}',
'{"theme": "dark", "language": "zh-CN", "notifications": true}'
);
-- 查询JSON数据
SELECT * FROM json_types;
-- 提取JSON值
SELECT
id,
JSON_EXTRACT(user_info, '$.name') AS name,
JSON_EXTRACT(user_info, '$.age') AS age,
user_info->>'$.city' AS city -- 简写方式
FROM json_types;
-- 修改JSON数据
UPDATE json_types
SET user_info = JSON_SET(user_info, '$.age', 26)
WHERE id = 1;
-- 添加JSON属性
UPDATE json_types
SET user_info = JSON_SET(user_info, '$.email', 'zhangsan@example.com')
WHERE id = 1;
-- 删除JSON属性
UPDATE json_types
SET user_info = JSON_REMOVE(user_info, '$.email')
WHERE id = 1;
-- JSON数组操作
INSERT INTO json_types (user_info, settings)
VALUES (
'{"name": "李四", "hobbies": ["阅读", "运动", "音乐"]}',
'{}'
);
-- 查询JSON数组
SELECT user_info->>'$.hobbies[0]' AS first_hobby
FROM json_types WHERE id = 2;
-- JSON搜索
SELECT * FROM json_types
WHERE JSON_CONTAINS(user_info, '"张三"', '$.name');数据类型选择建议
sql
/*
* 数据类型选择建议：
*
* 1. 整数类型
* - 小范围整数：TINYINT（年龄、状态）
* - 普通整数：INT（ID、数量）
* - 大整数：BIGINT（订单号、金额分）
*
* 2. 小数类型
* - 精确计算：DECIMAL（金额、价格）
* - 科学计算：DOUBLE
*
* 3. 字符串类型
* - 固定长度：CHAR（手机号、编码）
* - 可变长度：VARCHAR（姓名、地址）
* - 长文本：TEXT（文章内容）
*
* 4. 日期时间类型
* - 日期：DATE（生日）
* - 时间：TIME（营业时间）
* - 日期时间：DATETIME（创建时间）
* - 自动更新：TIMESTAMP
*
* 5. 特殊类型
* - 单选：ENUM（状态、性别）
* - 多选：SET（标签、爱好）
* - JSON数据：JSON（配置、扩展信息）
*/
-- 综合示例：用户表
CREATE TABLE users (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50) NOT NULL,
password CHAR(64) NOT NULL, -- SHA256哈希
email VARCHAR(100),
phone CHAR(11), -- 手机号
age TINYINT UNSIGNED, -- 年龄
gender ENUM('男', '女', '未知') DEFAULT '未知',
balance DECIMAL(10, 2) DEFAULT 0.00, -- 余额
avatar VARCHAR(255), -- 头像URL
status TINYINT DEFAULT 1, -- 状态：1正常 0禁用
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);本章小结
本章学习了：
- 数值类型：INT、BIGINT、DECIMAL、FLOAT、DOUBLE
- 字符串类型：CHAR、VARCHAR、TEXT、BLOB
- 枚举集合：ENUM、SET
- 日期时间：DATE、TIME、DATETIME、TIMESTAMP
- JSON类型：JSON数据存储和查询
- 类型选择：根据业务需求选择合适的数据类型
下一章，我们将学习表操作，了解如何创建和管理数据表。