MySQL 数据类型详解

MySQL中定义数据字段的类型对数据库的优化至关重要。正确选择数据类型可以提高查询性能、减少存储空间并确保数据完整性。MySQL支持多种数据类型，主要分为三类：数值类型、日期/时间类型和字符串类型。

数值类型

MySQL支持所有标准SQL数值数据类型，包括严格数值类型和近似数值类型。

整数类型

浮点数类型

定点数类型

数值类型使用示例

-- 创建包含各种数值类型的表
CREATE TABLE number_examples (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    age TINYINT UNSIGNED,
    population BIGINT,
    price DECIMAL(10, 2),
    temperature FLOAT,
    weight DOUBLE
);

-- 插入数据
INSERT INTO number_examples (age, population, price, temperature, weight)
VALUES (25, 1400000000, 99.99, 36.5, 65.75);

日期和时间类型

MySQL提供了多种日期和时间类型，用于存储时间相关的数据。

日期时间类型使用示例

-- 创建包含日期时间类型的表
CREATE TABLE datetime_examples (
    id INT AUTO_INCREMENT PRIMARY KEY,
    birth_date DATE,
    meeting_time TIME,
    created_at DATETIME,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    graduation_year YEAR
);

-- 插入数据
INSERT INTO datetime_examples (birth_date, meeting_time, created_at, graduation_year)
VALUES ('1990-05-15', '14:30:00', NOW(), 2024);

-- 查询示例
SELECT * FROM datetime_examples
WHERE birth_date > '1990-01-01';

字符串类型

MySQL提供了多种字符串类型，用于存储文本和二进制数据。

字符类型

二进制类型

枚举和集合类型

-- 枚举类型：只能选择预定义的值之一
CREATE TABLE enum_example (
    id INT AUTO_INCREMENT PRIMARY KEY,
    status ENUM('active', 'inactive', 'pending'),
    priority ENUM('low', 'medium', 'high') DEFAULT 'medium'
);

-- 集合类型：可以选择多个预定义的值
CREATE TABLE set_example (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tags SET('red', 'green', 'blue', 'yellow')
);

-- 插入数据
INSERT INTO enum_example (status, priority) VALUES ('active', 'high');
INSERT INTO set_example (tags) VALUES ('red,blue');

字符串类型使用示例

-- 创建包含字符串类型的表
CREATE TABLE string_examples (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    description TEXT,
    avatar BLOB,
    gender ENUM('male', 'female', 'other'),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 插入数据
INSERT INTO string_examples (username, email, description, gender)
VALUES ('john_doe', 'john@example.com', '这是一个用户描述文本。', 'male');

字符集和排序规则

常用字符集

设置字符集和排序规则

-- 数据库级别
CREATE DATABASE myapp CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 表级别
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 列级别
CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
    description TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
);

查看字符集和排序规则

-- 查看支持的字符集
SHOW CHARACTER SET;

-- 查看支持的排序规则
SHOW COLLATION WHERE Charset = 'utf8mb4';

-- 查看数据库的字符集
SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
FROM information_schema.SCHEMATA;

-- 查看表的字符集
SELECT TABLE_NAME, TABLE_COLLATION
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_database';

数据类型选择最佳实践

数值类型选择原则

• 整数类型：根据数据范围选择最小的合适类型
• 浮点数：科学计算使用FLOAT或DOUBLE
• 精确计算：货币等精确计算使用DECIMAL
• 自增ID：使用INT UNSIGNED AUTO_INCREMENT

字符串类型选择原则

• 定长数据：使用CHAR（如国家代码、MD5哈希）
• 变长数据：使用VARCHAR（如用户名、邮件地址）
• 大文本：使用TEXT类型
• 二进制数据：使用BLOB类型

日期时间类型选择原则

• 只需要日期：使用DATE
• 只需要时间：使用TIME
• 日期和时间：使用DATETIME或TIMESTAMP
• 自动时间戳：使用TIMESTAMP

通用最佳实践

• 选择能满足需求的最小数据类型
• 避免使用ENUM和SET，除非有明确需求
• 为所有字符串列指定合适的长度
• 统一使用utf8mb4字符集
• 考虑未来扩展性

数据类型与性能优化

索引优化

• 整数类型比字符串类型更适合索引
• 定长数据类型比变长数据类型索引效率更高
• 避免在索引列上使用过长的数据类型

存储优化

• 使用合适的数值类型减少存储空间
• 为VARCHAR列指定合适的最大长度
• 将大文本字段分离到单独的表中

查询优化

• 使用相同数据类型进行比较操作
• 避免在WHERE子句中对字段进行类型转换
• 为经常查询的字段选择高效的数据类型

常见问题与解决方案

1. 数据类型溢出

-- 错误：TINYINT溢出
INSERT INTO users (age) VALUES (300); -- TINYINT最大127

解决方案：选择合适范围的数值类型。

2. 字符集不兼容

-- 错误：插入emoji字符
INSERT INTO users (name) VALUES ('😀'); -- 如果字符集不是utf8mb4

解决方案：使用utf8mb4字符集。

3. 日期格式错误

-- 错误：无效的日期格式
INSERT INTO events (event_date) VALUES ('2024-02-30');

解决方案：使用有效的日期值，MySQL会进行验证。

4. 字符串截断

-- 警告：字符串被截断
INSERT INTO users (username) VALUES ('这是一个很长的用户名超过了定义的长度');

解决方案：为VARCHAR列指定足够的长度。

实用示例：完整表设计

-- 用户表设计示例
CREATE TABLE users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password_hash CHAR(60) NOT NULL, -- bcrypt哈希固定60字符
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    birth_date DATE,
    gender ENUM('male', 'female', 'other'),
    phone_number VARCHAR(20),
    avatar_url VARCHAR(255),
    bio TEXT,
    status ENUM('active', 'inactive', 'suspended') DEFAULT 'active',
    credits DECIMAL(10, 2) UNSIGNED DEFAULT 0.00,
    login_count INT UNSIGNED DEFAULT 0,
    last_login_at DATETIME,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_email (email),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;