1. 数值类型
1.1 整数类型
-- TINYINT: 1字节
CREATE TABLE example (
tiny_unsigned TINYINT UNSIGNED, -- 0 to 255
tiny_signed TINYINT -- -128 to 127
);
-- SMALLINT: 2字节
-- MEDIUMINT: 3字节
-- INT/INTEGER: 4字节
-- BIGINT: 8字节
CREATE TABLE numbers (
id INT AUTO_INCREMENT PRIMARY KEY,
small_num SMALLINT, -- -32768 to 32767
medium_num MEDIUMINT, -- -8388608 to 8388607
big_num BIGINT -- -2^63 to 2^63-1
);
1.2 浮点数类型
-- FLOAT: 4字节
-- DOUBLE: 8字节
-- DECIMAL: 精确小数
CREATE TABLE prices (
product_id INT,
price_float FLOAT(10,2), -- 最多10位，2位小数
price_double DOUBLE(10,2), -- 同上，更精确
price_decimal DECIMAL(10,2) -- 精确存储
);
2. 字符串类型
2.1 CHAR 和 VARCHAR
CREATE TABLE strings (
char_col CHAR(10), -- 固定长度，最多255字符
varchar_col VARCHAR(255), -- 可变长度，最多65535字符
name VARCHAR(50), -- 常用于用户名等
email VARCHAR(100) -- 常用于邮箱
);
2.2 TEXT 类型
CREATE TABLE contents (
id INT PRIMARY KEY,
title VARCHAR(200),
content TEXT, -- 最大65535字符
long_content MEDIUMTEXT, -- 最大16MB
huge_content LONGTEXT -- 最大4GB
);
3. 日期和时间类型
CREATE TABLE timestamps (
id INT PRIMARY KEY,
created_date DATE, -- YYYY-MM-DD
created_time TIME, -- HH:MM:SS
created_datetime DATETIME, -- YYYY-MM-DD HH:MM:SS
created_timestamp TIMESTAMP -- 自动更新时间戳
);
-- 使用示例
INSERT INTO timestamps (created_date, created_datetime)
VALUES (CURDATE(), NOW());
4. 二进制类型
CREATE TABLE binary_data (
id INT PRIMARY KEY,
binary_col BINARY(50), -- 固定长度
varbinary_col VARBINARY(100), -- 可变长度
blob_col BLOB, -- 二进制大对象
image MEDIUMBLOB -- 用于存储图片
);
5. 枚举和集合类型
-- ENUM
CREATE TABLE users (
id INT PRIMARY KEY,
name VARCHAR(100),
gender ENUM('M', 'F', 'Other'),
status ENUM('active', 'inactive', 'banned')
);
-- SET
CREATE TABLE products (
id INT PRIMARY KEY,
name VARCHAR(100),
sizes SET('XS', 'S', 'M', 'L', 'XL'),
colors SET('red', 'blue', 'green')
);
6. JSON 类型（MySQL 5.7+）
CREATE TABLE json_data (
id INT PRIMARY KEY,
data JSON
);
-- 插入 JSON 数据
INSERT INTO json_data (data) VALUES (
'{"name": "John", "age": 30, "city": "New York"}'
);
-- 查询 JSON 数据
SELECT JSON_EXTRACT(data, '$.name') FROM json_data;
7. 空间数据类型
CREATE TABLE spatial_data (
id INT PRIMARY KEY,
location POINT,
area POLYGON,
route LINESTRING
);
-- 插入空间数据
INSERT INTO spatial_data (location)
VALUES (ST_GeomFromText('POINT(1 1)'));
8. 实际应用示例
8.1 用户表设计
CREATE TABLE users (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL UNIQUE,
password_hash CHAR(60) NOT NULL, -- 用于存储BCrypt哈希
email VARCHAR(100) NOT NULL UNIQUE,
status ENUM('active', 'inactive', 'banned') DEFAULT 'active',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
INDEX idx_email (email),
INDEX idx_username (username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
8.2 订单表设计
CREATE TABLE orders (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
user_id BIGINT UNSIGNED NOT NULL,
total_amount DECIMAL(10,2) NOT NULL,
status ENUM('pending', 'paid', 'shipped', 'completed', 'cancelled'),
shipping_address JSON,
order_date DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id),
INDEX idx_user (user_id),
INDEX idx_date (order_date)
) ENGINE=InnoDB;
9. 选择建议
- 字符串选择：
-- 固定长度用 CHAR
CREATE TABLE codes (
country_code CHAR(2)
);
-- 可变长度用 VARCHAR
CREATE TABLE names (
first_name VARCHAR(50)
);
- 数值类型选择：
-- 金额使用 DECIMAL
CREATE TABLE transactions (
amount DECIMAL(10,2) -- 精确到分
);
-- 计数使用 INT
CREATE TABLE statistics (
view_count INT UNSIGNED
);
- 日期类型选择：
-- 需要时区支持用 TIMESTAMP
CREATE TABLE logs (
created_at TIMESTAMP
);
-- 不需要时区用 DATETIME
CREATE TABLE events (
event_time DATETIME
);
10. 性能考虑
- 索引优化：
-- 为经常查询的字段创建索引
CREATE INDEX idx_email ON users(email);
-- 复合索引
CREATE INDEX idx_name_status ON users(name, status);
- 存储优化：
-- 使用适当的字段长度
CREATE TABLE optimized (
id INT UNSIGNED, -- 而不是 BIGINT
small_text VARCHAR(50) -- 而不是 TEXT
);
选择合适的数据类型对于：
-
数据库性能
-
存储空间
-
数据完整性
-
查询效率
都有重要影响。需要根据具体业务需求来选择合适的数据类型。
