数据类型完整参考

MySQL 数据类型完整参考指南

理解MySQL数据类型是数据库设计的基础。选择正确的数据类型会影响存储、性能、内存使用和查询执行。本完整指南涵盖所有MySQL数据类型的示例、性能考虑和最佳实践。

1. 数值数据类型

1.1 整数类型

整数类型存储没有小数点的整数。

      **示例：**

-- 定义具有整数类型的表
CREATE TABLE user_metrics (
  id BIGINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
  user_id INT UNSIGNED NOT NULL,
  age TINYINT UNSIGNED,
  status TINYINT DEFAULT 1,
  post_count INT UNSIGNED DEFAULT 0,
  total_views BIGINT UNSIGNED DEFAULT 0,
  rank_position SMALLINT UNSIGNED,
  file_size_bytes MEDIUMINT UNSIGNED
);

-- 插入示例
INSERT INTO user_metrics (user_id, age, post_count)
VALUES (1, 25, 100);

-- 使用范围检查查询
SELECT * FROM user_metrics
WHERE age BETWEEN 18 AND 65 AND post_count > 10;

-- 使用数值类型的聚合
SELECT
  age,
  COUNT(*) as count,
  AVG(post_count) as avg_posts,
  MAX(total_views) as max_views
FROM user_metrics
GROUP BY age
ORDER BY avg_posts DESC;

      **性能考虑：**

• 使用最小的适当类型以最小化存储并改进缓存效率
• 对于非负值使用UNSIGNED以翻倍范围
• INT通常对一般目的是最优的（InnoDB使用4字节页面）
• BIGINT用于分布式系统或非常大的计数（时间戳、ID）
• 避免MEDIUMINT —— 节省1个字节但复杂化解析
• 对标志或小枚举使用TINYINT（年龄、状态、优先级）

1.2 小数/固定点类型

-- DECIMAL(precision, scale)
-- 存储精确值，对财务数据很重要
CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  price DECIMAL(10, 2),           -- 小数点前8位，小数点后2位
  tax_rate DECIMAL(5, 4),         -- 小数点前1位，小数点后4位（例如 0.1999）
  cost DECIMAL(8, 2)
);

-- 插入示例
INSERT INTO products VALUES
  (1, 'Laptop', 999.99, 0.1999, 500.00),
  (2, 'Mouse', 25.50, 0.1999, 5.00);

-- 算术精度
SELECT
  id,
  name,
  price,
  tax_rate,
  ROUND(price * tax_rate, 2) as tax,
  ROUND(price * (1 + tax_rate), 2) as total
FROM products;

-- 比较：DECIMAL vs FLOAT
CREATE TABLE precision_test (
  id INT,
  decimal_val DECIMAL(10, 8),
  float_val FLOAT,
  double_val DOUBLE
);

INSERT INTO precision_test VALUES (1, 0.12345678, 0.12345678, 0.12345678);

SELECT
  id,
  decimal_val,
  float_val,
  double_val,
  decimal_val = 0.12345678 as decimal_match,
  float_val = 0.12345678 as float_match,
  double_val = 0.12345678 as double_match
FROM precision_test;
-- 输出：decimal_match=true，float_match=false，double_match=false

      **指导原则：**

• DECIMAL用于财务数据、精确值、价格、数量
• 始终明确指定（precision, scale）
• 存储：9位数字约4个字节，更多位数会增加
• 操作比浮点数慢，但数学上是精确的
• 用于货币：DECIMAL(19, 2)或DECIMAL(19, 4）用于小数
• FLOAT/DOUBLE仅用于科学计算或近似值

1.3 浮点类型

-- FLOAT（4字节，约7位有效数字）
-- DOUBLE（8字节，约15位有效数字）
CREATE TABLE scientific_data (
  id INT PRIMARY KEY,
  sensor_reading FLOAT,
  temperature DOUBLE,
  confidence FLOAT
);

-- 使用科学记数法插入
INSERT INTO scientific_data VALUES
  (1, 1.23456789, 1.234567890123456, 0.95),
  (2, 9.87654321, 9.876543210123456, 0.87);

-- 注意：浮点有舍入错误
SELECT
  sensor_reading,
  sensor_reading * 10 as multiplied,
  ROUND(temperature, 5) as rounded_temp
FROM scientific_data;

-- 避免与浮点数相等比较
SELECT * FROM scientific_data
WHERE ABS(confidence - 0.95) < 0.001;  -- 使用误差范围

2. 字符串数据类型

2.1 VARCHAR vs CHAR

VARCHAR存储可变长度的字符串；CHAR存储固定长度的字符串。

      **性能比较：**

-- CHAR(50) —— 始终分配50字节（用空格填充）
-- VARCHAR(50) —— 仅分配需要的空间 + 1-2个长度字节
CREATE TABLE user_profiles (
  id INT PRIMARY KEY,
  username VARCHAR(30),           -- 最适合名字/用户名
  gender CHAR(1),                 -- 最适合固定单字符
  country CHAR(2),                -- 最适合ISO国家代码
  bio VARCHAR(500),               -- 可变长度内容
  email VARCHAR(100),
  phone CHAR(15)                  -- 国际电话格式
);

-- 存储示例：
-- username 'alice'（5个字符）：存储为 5 + 2字节 = 7字节（VARCHAR）
-- gender 'M'：存储为 1字节 + 填充（CHAR）= 1字节
-- bio 'Hello'：存储为 5 + 2字节 = 7字节（VARCHAR）

INSERT INTO user_profiles VALUES
  (1, 'alice', 'F', 'US', 'Software engineer from Silicon Valley', '[email protected]', '+1-408-555-0123'),
  (2, 'bob123', 'M', 'UK', 'Data scientist', '[email protected]', '+44-20-7946-0958');

-- 使用字符串函数查询
SELECT
  id,
  username,
  UPPER(username) as username_upper,
  LENGTH(username) as username_length,
  SUBSTRING(email, 1, POSITION('@' IN email) - 1) as email_user
FROM user_profiles;

      **何时使用每种：**

• CHAR：固定长度数据（国家代码、状态、布尔标志）
• VARCHAR：可变长度数据（名字、电子邮件、描述）
• 对于大多数情况，VARCHAR存储效率更高
• CHAR对于短的固定长度字段可能略微快一些
• 默认使用VARCHAR，除非您知道长度始终是固定的

2.2 文本类型

-- TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT
-- 存储在主表之外（页外存储）
CREATE TABLE articles (
  id INT PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  summary VARCHAR(1000),
  content LONGTEXT,               -- 对于非常长的内容（4GB最大）
  tags TEXT,                      -- 对于逗号分隔或JSON
  metadata MEDIUMTEXT
);

-- 按类型存储：
-- TINYTEXT：2^8 - 1 = 255字节
-- TEXT：2^16 - 1 = 64KB
-- MEDIUMTEXT：2^24 - 1 = 16MB
-- LONGTEXT：2^32 - 1 = 4GB

INSERT INTO articles VALUES
  (1,
   'Database Performance',
   'Tips for optimizing MySQL performance',
   'Long article content here...',
   'mysql,performance,optimization',
   '{"category":"database","difficulty":"intermediate"}');

-- 在TEXT字段中搜索
SELECT id, title
FROM articles
WHERE content LIKE '%index%'
OR FIND_IN_SET('performance', tags) > 0;

-- 从TEXT提取JSON
SELECT
  id,
  title,
  JSON_EXTRACT(metadata, '$.category') as category,
  JSON_EXTRACT(metadata, '$.difficulty') as difficulty
FROM articles;

2.3 二进制字符串类型

-- BINARY、VARBINARY、BLOB
-- 用于存储二进制数据（图像、文件、加密值）
CREATE TABLE file_uploads (
  id INT PRIMARY KEY,
  filename VARCHAR(255),
  file_hash BINARY(32),           -- SHA-256哈希（256位= 32字节）
  thumbnail VARBINARY(100000),    -- 压缩图像
  file_data LONGBLOB,             -- 大型二进制数据
  uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 插入二进制数据
INSERT INTO file_uploads (filename, file_hash, thumbnail)
VALUES
  ('document.pdf', 0xA1B2C3D4E5F6..., 0xFFD8FFE000...),
  ('image.jpg', 0x1A2B3C4D5E6F..., 0x89504E470D...);

-- 使用十六进制读取二进制
SELECT
  filename,
  HEX(file_hash) as hash_hex,
  LENGTH(thumbnail) as thumbnail_size
FROM file_uploads;

-- 比较二进制值
SELECT * FROM file_uploads
WHERE file_hash = UNHEX('A1B2C3D4E5F6...');

3. 日期和时间类型

3.1 DATE、TIME、DATETIME

CREATE TABLE events (
  id INT PRIMARY KEY,
  event_name VARCHAR(100),
  event_date DATE,                -- 仅日期（YYYY-MM-DD）
  event_time TIME,                -- 仅时间（HH:MM:SS）
  event_datetime DATETIME,        -- 日期+时间（YYYY-MM-DD HH:MM:SS）
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 使用各种格式插入
INSERT INTO events VALUES
  (1, 'Conference', '2024-06-15', '09:00:00', '2024-06-15 09:00:00', NOW(), NOW()),
  (2, 'Meeting', '2024-06-16', '14:30:00', '2024-06-16 14:30:00', NOW(), NOW());

-- 日期算术
SELECT
  id,
  event_name,
  event_date,
  DATE_ADD(event_date, INTERVAL 7 DAY) as next_week,
  DATE_SUB(event_date, INTERVAL 1 MONTH) as one_month_ago,
  DATEDIFF(event_date, CURDATE()) as days_until_event,
  DAY(event_date) as day_of_month,
  MONTH(event_date) as month,
  YEAR(event_date) as year,
  QUARTER(event_date) as quarter,
  DAYNAME(event_date) as day_name,
  MONTHNAME(event_date) as month_name
FROM events;

-- 时间计算
SELECT
  event_time,
  TIME_ADD(event_time, INTERVAL 30 MINUTE) as thirty_mins_later,
  SEC_TO_TIME(TIME_TO_SEC(event_time) + 3600) as one_hour_later,
  TIMEDIFF('17:00:00', event_time) as time_difference
FROM events;

-- Datetime范围查询
SELECT * FROM events
WHERE event_datetime BETWEEN '2024-06-01' AND '2024-06-30'
AND HOUR(event_datetime) BETWEEN 8 AND 18;  -- 营业时间

      **类型比较：**

3.2 TIMESTAMP 特殊功能

-- TIMESTAMP：以UTC内部存储，以时区显示
-- 在修改时自动更新
CREATE TABLE user_activity (
  id INT PRIMARY KEY,
  user_id INT,
  action VARCHAR(50),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  last_login TIMESTAMP DEFAULT '0000-00-00 00:00:00'
);

INSERT INTO user_activity (user_id, action) VALUES (1, 'login');
INSERT INTO user_activity (user_id, action) VALUES (2, 'post_created');

-- 时间戳自动更新
UPDATE user_activity SET action = 'updated' WHERE id = 1;
-- updated_at自动更改为当前时间

SELECT * FROM user_activity;

-- 时区考虑
SET time_zone = '+05:00';
SELECT created_at, @@session.time_zone FROM user_activity;

-- DATETIME不随时区改变
-- 对自动current_timestamp跟踪使用TIMESTAMP
-- 对固定值使用DATETIME

4. 特殊数据类型

4.1 JSON类型

-- JSON：存储带验证和函数的JSON文档
CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  attributes JSON,                -- 验证的JSON存储
  metadata JSON
);

-- 插入JSON数据
INSERT INTO products VALUES
  (1, 'Laptop',
   '{"brand":"Dell","cpu":"Intel i7","ram":"16GB","storage":"512GB SSD"}',
   '{"weight":1.8,"color":"Silver","warranty_months":24}'),
  (2, 'Monitor',
   '{"brand":"LG","resolution":"4K","refresh_rate":"60Hz","size":"27\""}',
   '{"power_consumption":"50W","warranty_months":36}');

-- 查询JSON字段
SELECT
  id,
  name,
  JSON_EXTRACT(attributes, '$.brand') as brand,
  JSON_EXTRACT(attributes, '$.cpu') as cpu,
  JSON_EXTRACT(attributes, '$.ram') as ram,
  JSON_UNQUOTE(JSON_EXTRACT(attributes, '$.cpu')) as cpu_unquoted
FROM products;

-- JSON搜索和比较
SELECT * FROM products
WHERE JSON_EXTRACT(attributes, '$.ram') = '"16GB"';

-- 更新JSON字段
UPDATE products
SET attributes = JSON_SET(attributes, '$.warranty', '2 years')
WHERE id = 1;

-- 提取数组元素
INSERT INTO products VALUES
  (3, 'Phone',
   '{"brand":"Apple","models":["iPhone 14","iPhone 14 Pro","iPhone 14 Max"]}',
   '{}');

SELECT
  id,
  JSON_ARRAY_LENGTH(JSON_EXTRACT(attributes, '$.models')) as model_count
FROM products WHERE id = 3;

-- JSON聚合
SELECT
  JSON_OBJECTAGG(name, JSON_EXTRACT(attributes, '$.brand')) as products_by_brand
FROM products;

4.2 ENUM类型

-- ENUM：有效存储预定义值
CREATE TABLE orders (
  id INT PRIMARY KEY,
  order_number VARCHAR(20),
  status ENUM('pending','processing','shipped','delivered','cancelled'),
  priority ENUM('low','medium','high','urgent'),
  payment_method ENUM('credit_card','debit_card','paypal','bank_transfer'),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 使用ENUM值插入
INSERT INTO orders VALUES
  (1, 'ORD-001', 'pending', 'high', 'credit_card', NOW()),
  (2, 'ORD-002', 'shipped', 'medium', 'paypal', NOW()),
  (3, 'ORD-003', 'delivered', 'low', 'bank_transfer', NOW());

-- ENUM在内部存储为小整数（1、2、3...）
-- 存储：对于最多255个值使用1字节，对于最多65535个值使用2字节

SELECT * FROM orders WHERE status = 'shipped';

-- 获取枚举值
SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'orders' AND COLUMN_NAME = 'status';

-- 枚举值（1='pending'，2='processing'，3='shipped'...）
SELECT
  id,
  status,
  CAST(status AS UNSIGNED) as status_code
FROM orders;

-- 更改ENUM值（需要ALTER TABLE）
ALTER TABLE orders MODIFY status ENUM('pending','processing','shipped','delivered','cancelled','refunded');

      **何时使用ENUM：**

• 固定的值集，很少更改
• 数量很少的值（最多65,535）
• 存储效率很重要
• 快速比较和排序
• 避免值频繁更改（需要ALTER TABLE）

4.3 SET类型

-- SET：从预定义列表中存储零个或多个值
CREATE TABLE user_permissions (
  id INT PRIMARY KEY,
  username VARCHAR(50),
  permissions SET('read','write','delete','admin','user_manage')
);

-- 使用多个值插入
INSERT INTO user_permissions VALUES
  (1, 'alice', 'read,write'),
  (2, 'bob', 'read,write,delete,admin'),
  (3, 'charlie', 'read'),
  (4, 'diana', '');  -- 无权限

-- 使用SET成员资格查询
SELECT * FROM user_permissions
WHERE FIND_IN_SET('admin', permissions) > 0;

-- 添加权限
UPDATE user_permissions
SET permissions = CONCAT(permissions, IF(permissions='','',',' ), 'write')
WHERE username = 'charlie';

-- 或使用SET操作
UPDATE user_permissions
SET permissions = CONCAT_WS(',', permissions, 'write')
WHERE username = 'charlie';

-- 删除权限
UPDATE user_permissions
SET permissions = TRIM(BOTH ',' FROM REPLACE(CONCAT(',',permissions,','), ',write,', ','))
WHERE username = 'bob' AND FIND_IN_SET('write', permissions) > 0;

-- 将所有权限作为数组获取
SELECT
  username,
  permissions,
  IF(FIND_IN_SET('read', permissions) > 0, 'YES', 'NO') as can_read,
  IF(FIND_IN_SET('write', permissions) > 0, 'YES', 'NO') as can_write,
  IF(FIND_IN_SET('delete', permissions) > 0, 'YES', 'NO') as can_delete,
  IF(FIND_IN_SET('admin', permissions) > 0, 'YES', 'NO') as is_admin
FROM user_permissions;

5. 数据类型选择指南

6. 性能优化技巧

6.1 索引考虑

-- 数值列的索引比字符串列快
CREATE TABLE users (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  email VARCHAR(255) UNIQUE,      -- 可以被索引
  age TINYINT UNSIGNED,           -- 小尺寸有利于缓存
  user_type ENUM('free','premium','admin') -- 非常有效的过滤
);

-- INT列的索引为4字节，VARCHAR(255)的索引是可变的
CREATE INDEX idx_age ON users(age);      -- 非常小的索引
CREATE INDEX idx_email ON users(email);  -- 大得多的索引

-- 复合索引策略
CREATE INDEX idx_type_age ON users(user_type, age);

-- 使用索引有效查询
SELECT * FROM users WHERE user_type = 'premium' AND age > 18;  -- 使用复合索引

-- NULL处理
CREATE TABLE orders (
  id INT PRIMARY KEY,
  user_id INT,
  deleted_at DATETIME,  -- 如果未删除则为NULL
  cancelled_at DATETIME  -- 如果未取消则为NULL
);

-- 使用索引查询NULL
SELECT * FROM orders WHERE deleted_at IS NULL;
SELECT * FROM orders WHERE cancelled_at IS NOT NULL;

6.2 存储效率

-- 好：优化的类型减少存储并改进性能
CREATE TABLE page_visits (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  page_id INT UNSIGNED NOT NULL,
  user_id INT UNSIGNED,
  visit_date DATE NOT NULL,
  visit_time TIME,
  referer VARCHAR(2083),
  is_bot TINYINT(1) DEFAULT 0,  -- 1字节用于布尔值
  session_id CHAR(32)             -- 固定长度哈希
) ENGINE=InnoDB ROW_FORMAT=COMPACT;

-- 检查表大小
SELECT
  TABLE_NAME,
  ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) as size_mb
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'myapp';

-- 分析每列的存储
SELECT
  SUM(STAT_VALUE) as total_rows,
  AVG(STAT_VALUE) as avg_value
FROM mysql.innodb_table_stats
WHERE TABLE_NAME = 'page_visits';

7. 常见错误和解决方案

错误1：对所有内容使用VARCHAR(255)

-- 不好：浪费空间用于短字符串
CREATE TABLE users (
  id INT,
  title VARCHAR(255),       -- 通常<50个字符
  status VARCHAR(255),      -- 通常<20个字符
  name VARCHAR(255)         -- 通常<100个字符
);

-- 更好：使用适当的大小
CREATE TABLE users (
  id INT,
  title VARCHAR(50),
  status VARCHAR(20),
  name VARCHAR(100)
);

错误2：使用FLOAT表示货币

-- 不好：浮点舍入错误
CREATE TABLE transactions (
  amount FLOAT  -- 错误！损失精度
);

INSERT INTO transactions VALUES (0.1), (0.2), (0.3);
SELECT SUM(amount) FROM transactions;  -- 可能显示 0.5999999...而不是 0.6

-- 更好：使用DECIMAL
CREATE TABLE transactions (
  amount DECIMAL(10, 2)  -- 精确的小数存储
);

错误3：选择错误的文本类型

-- 不好：对短值使用TEXT
CREATE TABLE articles (
  id INT,
  content TEXT  -- 对短内容来说过度了
);

-- 更好：对大多数情况使用VARCHAR
CREATE TABLE articles (
  id INT,
  title VARCHAR(200),
  excerpt VARCHAR(500),      -- 简短描述
  content LONGTEXT           -- 仅用于非常长的内容
);

8. 常见问题与最佳实践

Q：我应该使用TIMESTAMP还是DATETIME？

**A：**当您想要自动跟踪时使用TIMESTAMP：

-- TIMESTAMP：自动更新，存储UTC，使用4个字节
CREATE TABLE audit_log (
  id INT,
  action VARCHAR(50),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- DATETIME：固定值，使用8个字节，按原样存储
CREATE TABLE events (
  event_date DATETIME,  -- 不自动更新
  scheduled_for DATETIME
);

Q：JSON类型与存储为VARCHAR相比值得吗？

**A：**是的，如果您经常查询JSON结构：

-- JSON类型优点：
-- 1.验证：无效JSON被拒绝
SELECT JSON_EXTRACT(data, '$.name') -- 仅当有效JSON时工作

-- 2.函数：JSON特定的操作
SELECT JSON_KEYS(data) as all_keys FROM table;

-- 3.索引支持（MySQL 5.7.9+）
CREATE INDEX idx_brand ON products((JSON_EXTRACT(attrs, '$.brand')));

-- 对于您将始终完全查询的简单键值数据，TEXT很好
-- 对于具有部分查询的复杂嵌套结构，使用JSON

Q：INT和INTEGER之间的区别是什么？

**A：**没有区别。INTEGER是INT的别名。

Q：我可以存储非常大的数字吗？

**A：**使用DECIMAL获得任意精度：

-- BIGINT最多为9,223,372,036,854,775,807
-- 对于更大的数字，使用DECIMAL或VARCHAR
CREATE TABLE high_precision (
  big_number DECIMAL(65, 0),  -- 65位数字，无小数
  scientific VARCHAR(100)      -- 字符串表示
);

总结

选择正确的MySQL数据类型对于最优的数据库性能至关重要。请记住这些关键原则：

• 使用适应您的数据的最小数据类型，以最小化存储并改进缓存效率
• 精确：DECIMAL用于金钱，DATE/DATETIME用于时间，JSON用于结构化数据
• 考虑数据将如何被查询和索引
• 对非负数字使用UNSIGNED以翻倍范围
• 避免对所有内容使用过度通用的选择，如VARCHAR(255)
• 对固定类别使用ENUM/SET以节省空间
• TIMESTAMP用于自动跟踪，DATETIME用于固定值
• 始终在应用程序级别验证数据，与类型无关