MySQL 数据类型介绍 (结合实际公司项目中的案例说明这些数据类型的使用场景)。

1. 数值类型

整数类型

• TINYINT：用于存储较小的整数，如用户评分（0-10），布尔值（0 或 1）。

CREATE TABLE user_ratings (
    user_id INT,
    rating TINYINT
);

• SMALLINT：用于存储较小范围的整数，如产品库存数量（0-32767）。

CREATE TABLE product_inventory (
    product_id INT,
    quantity SMALLINT
);

• MEDIUMINT：用于存储中等范围的整数，如用户数量较多的应用（0-16777215）。

CREATE TABLE user_stats (
    user_id MEDIUMINT UNSIGNED,
    posts_count MEDIUMINT
);

• INT（INTEGER）：用于存储常规整数，如用户ID、订单ID。

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    total_amount DECIMAL(10, 2)
);

• BIGINT：用于存储大范围的整数，如社交平台上的用户点赞数（0-18446744073709551615）。

CREATE TABLE post_likes (
    post_id BIGINT,
    like_count BIGINT
);

浮点数类型

• FLOAT(M, D)：用于存储单精度浮点数，如科学计算、图形处理中的数据。

CREATE TABLE sensor_data (
    sensor_id INT,
    temperature FLOAT(5, 2)
);

• DOUBLE(M, D)：用于存储双精度浮点数，如金融计算中需要高精度的数据。

CREATE TABLE financial_data (
    record_id INT,
    amount DOUBLE(15, 8)
);

• DECIMAL(M, D)：用于存储精确数值，如商品价格、货币金额。

CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    price DECIMAL(10, 2)
);

2. 日期和时间类型

• DATE：存储日期，如生日、注册日期。

CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50),
    birthdate DATE
);

• TIME：存储时间，如工作打卡时间。

CREATE TABLE work_hours (
    employee_id INT,
    check_in TIME,
    check_out TIME
);

• DATETIME：存储日期和时间，如订单创建时间。

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    order_datetime DATETIME
);

• TIMESTAMP：存储时间戳，如记录数据的创建和更新时间。

CREATE TABLE logs (
    log_id INT AUTO_INCREMENT PRIMARY KEY,
    log_message TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

• YEAR：存储年份，如出版年份、年度报告年份。

CREATE TABLE publications (
    publication_id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255),
    publication_year YEAR
);

3. 字符串类型

字符串

• CHAR(M)：用于存储固定长度字符串，如国家代码、邮政编码。

CREATE TABLE countries (
    country_code CHAR(3),
    country_name VARCHAR(255)
);

• VARCHAR(M)：用于存储可变长度字符串，如用户名、电子邮件地址。

CREATE TABLE customers (
    customer_id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) UNIQUE
);

文本

• TINYTEXT：存储短文本，如注释、标签。

CREATE TABLE comments (
    comment_id INT AUTO_INCREMENT PRIMARY KEY,
    comment_text TINYTEXT
);

• TEXT：存储较长文本，如博客文章内容。

CREATE TABLE blog_posts (
    post_id INT AUTO_INCREMENT PRIMARY KEY,
    post_content TEXT
);

• MEDIUMTEXT：存储更长的文本，如产品描述、大型文档。

CREATE TABLE product_descriptions (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    description MEDIUMTEXT
);

• LONGTEXT：存储极长文本，如书籍、百科全书内容。

CREATE TABLE books (
    book_id INT AUTO_INCREMENT PRIMARY KEY,
    content LONGTEXT
);

二进制

• BINARY(M)：用于存储固定长度的二进制数据。

CREATE TABLE binaries (
    binary_id INT AUTO_INCREMENT PRIMARY KEY,
    data BINARY(16)
);

• VARBINARY(M)：用于存储可变长度的二进制数据。

CREATE TABLE files (
    file_id INT AUTO_INCREMENT PRIMARY KEY,
    file_data VARBINARY(255)
);

大对象

• TINYBLOB：存储小的二进制数据，如缩略图。

CREATE TABLE thumbnails (
    thumbnail_id INT AUTO_INCREMENT PRIMARY KEY,
    image TINYBLOB
);

• BLOB：存储中等大小的二进制数据，如文档、图片。

CREATE TABLE documents (
    document_id INT AUTO_INCREMENT PRIMARY KEY,
    content BLOB
);

• MEDIUMBLOB：存储较大的二进制数据，如音频文件、视频文件。

CREATE TABLE media_files (
    media_id INT AUTO_INCREMENT PRIMARY KEY,
    file MEDIUMBLOB
);

• LONGBLOB：存储非常大的二进制数据，如超大视频、数据库备份。

CREATE TABLE large_media (
    media_id INT AUTO_INCREMENT PRIMARY KEY,
    data LONGBLOB
);

4. 枚举和集合类型

• ENUM：用于存储枚举类型的值，如性别、状态。

CREATE TABLE employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    gender ENUM('Male', 'Female', 'Other')
);

• SET：用于存储一组值，如兴趣爱好、技能标签。

CREATE TABLE user_profiles (
    profile_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50),
    hobbies SET('Reading', 'Traveling', 'Cooking', 'Sports')
);

5. 特殊类型

• BIT：用于存储位字段，可以存储 0 和 1 的位值。

CREATE TABLE bit_example (
    id INT AUTO_INCREMENT PRIMARY KEY,
    bit_field BIT(1)
);

• BOOLEAN：MySQL 中 BOOLEAN 是 TINYINT(1) 的同义词，用于存储布尔值。

CREATE TABLE boolean_example (
    id INT AUTO_INCREMENT PRIMARY KEY,
    is_active BOOLEAN
);

常见数据类型对比

1. CHAR vs. VARCHAR

CHAR

• 特点：

• 固定长度字符类型。
• 长度不足会使用空格填充。
• 最大长度为 255 个字符。
• 优点：

• 速度快，因为存储和检索时长度固定。
• 适用于长度固定的数据。
• 缺点：
• 存储空间利用率低，对于长度变化较大的数据会浪费空间。

VARCHAR

• 特点：

• 可变长度字符类型。
• 只存储实际字符的长度，外加 1 个字节记录长度（如果长度小于 255），或 2 个字节（如果长度超过 255）。
• 最大长度为 65535 个字符。
• 优点：
• 存储空间利用率高，适用于长度不固定的数据。
• 缺点：
• 存储和检索稍慢，因为长度可变。

实际应用

• CHAR：

CREATE TABLE country_codes (
    country_code CHAR(3),
    country_name VARCHAR(50)
);

• VARCHAR：

CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(255)
);

2. TEXT vs. BLOB

TEXT

• 特点：

• 用于存储大文本数据。
• 分为 TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT。
• 优点：
• 适用于存储大文本数据，如文章内容、评论。
• 缺点：
• 无法存储二进制数据。

BLOB

• 特点：

• 用于存储大二进制数据。
• 分为 TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB。
• 优点：
• 适用于存储二进制数据，如图片、音频、视频。
• 缺点：
• 无法存储文本数据，需要转换为二进制格式。

实际应用

• TEXT：

CREATE TABLE blog_posts (
    post_id INT AUTO_INCREMENT PRIMARY KEY,
    post_content TEXT
);

• BLOB：

CREATE TABLE images (
    image_id INT AUTO_INCREMENT PRIMARY KEY,
    image_data BLOB
);

3. FLOAT vs. DOUBLE vs. DECIMAL

FLOAT

• 特点：

• 单精度浮点数。
• 通常用于科学计算。
• 优点：

• 占用空间小（4 字节）。
• 适用于精度要求不高的场景。
• 缺点：
• 精度低，可能会出现舍入误差。

DOUBLE

• 特点：

• 双精度浮点数。
• 通常用于金融计算。
• 优点：

• 精度高于 FLOAT。
• 适用于需要较高精度的计算。
• 缺点：
• 占用空间较大（8 字节）。

DECIMAL

• 特点：

• 定点数。
• 通常用于存储精确数值，如货币金额。
• 优点：

• 精度高，没有舍入误差。
• 适用于需要精确数值的场景。
• 缺点：
• 占用空间根据定义的精度和小数位数变化。

实际应用

• FLOAT：

CREATE TABLE sensor_data (
    sensor_id INT,
    temperature FLOAT(5, 2)
);

• DOUBLE：

CREATE TABLE scientific_calculations (
    calculation_id INT AUTO_INCREMENT PRIMARY KEY,
    value DOUBLE(15, 8)
);

• DECIMAL：

CREATE TABLE financial_records (
    record_id INT AUTO_INCREMENT PRIMARY KEY,
    amount DECIMAL(10, 2)
);

4. ENUM vs. SET

ENUM

• 特点：

• 枚举类型，定义一组预设的字符串值。
• 只能存储其中一个值。
• 优点：

• 存储空间小，效率高。
• 适用于字段值较少且固定的情况。
• 缺点：
• 修改预设值需要修改表结构。

SET

• 特点：

• 集合类型，定义一组预设的字符串值。
• 可以存储其中一个或多个值。
• 优点：
• 适用于多选字段。
• 缺点：
• 存储和操作相对复杂。

实际应用

• ENUM：

CREATE TABLE employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    gender ENUM('Male', 'Female', 'Other')
);

• SET：

CREATE TABLE user_profiles (
    profile_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50),
    hobbies SET('Reading', 'Traveling', 'Cooking', 'Sports')
);

5. DATE vs. DATETIME vs. TIMESTAMP

DATE

• 特点：

• 存储日期，格式为 ‘YYYY-MM-DD’。
• 占用 3 字节。
• 优点：
• 适用于只需要日期的场景。
• 缺点：
• 不存储时间部分。

DATETIME

• 特点：

• 存储日期和时间，格式为 ‘YYYY-MM-DD HH:MM:SS’。
• 占用 8 字节。
• 优点：
• 适用于需要完整日期和时间的场景。
• 缺点：
• 无时区信息。

TIMESTAMP

• 特点：

• 存储时间戳，格式为 ‘YYYY-MM-DD HH:MM:SS’。
• 占用 4 字节。
• 自动初始化和更新当前时间。
• 优点：

• 适用于记录修改时间和创建时间。
• 支持时区转换。
• 缺点：
• 范围有限（1970-2038 年）。

实际应用

• DATE：

CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    birthdate DATE
);

• DATETIME：

CREATE TABLE appointments (
    appointment_id INT AUTO_INCREMENT PRIMARY KEY,
    appointment_time DATETIME
);

• TIMESTAMP：

CREATE TABLE logs (
    log_id INT AUTO_INCREMENT PRIMARY KEY,
    log_message TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);