MySQL 数据类型详解

在 MySQL 中，数据类型用于定义数据库表中每个字段存储的数据类型。合理使用数据类型不仅能提高数据的存储效率，还能增强数据的完整性和安全性。本文将详细介绍 MySQL 中的各种数据类型，包括字符串、数值、日期与时间类型，以及自定义数据类型（ENUM 和 SET）。

一、字符串数据类型

字符串类型用于存储文本数据。MySQL 提供了多种字符串数据类型，主要包括以下几种：

1. CHAR 和 VARCHAR

• CHAR(n)：定长字符串，长度为 n。存储时如果字符少于 n，会在后面填充空格。
• VARCHAR(n)：变长字符串，最大长度为 n。存储时只占用实际字符的长度，不会填充空格。

CREATE TABLE example_strings (
    name CHAR(10),
    email VARCHAR(100)
);

INSERT INTO example_strings (name, email) VALUES ('Alice', 'alice@example.com');

2. TEXT 和 BLOB

• TEXT：用于存储大文本数据，最大长度为 65,535 字符。
• BLOB（Binary Large Object）：用于存储二进制数据，如图片或文件，最大长度为 65,535 字节。

CREATE TABLE example_blob (
    description TEXT,
    image BLOB
);

3. ENUM 和 SET

1. ENUM

ENUM 是一个字符串对象，可以在预定义的值中选择一个。

CREATE TABLE example_enum (
    status ENUM('active', 'inactive', 'pending')
);

INSERT INTO example_enum (status) VALUES ('active');

使用 ENUM 的好处是可以限制字段的值，从而增强数据的完整性。

2. SET

SET 是一个字符串对象，可以在预定义的值中选择零个或多个值。

CREATE TABLE example_set (
    hobbies SET('reading', 'travelling', 'cooking')
);

INSERT INTO example_set (hobbies) VALUES ('reading,cooking');

二、数值数据类型

数值类型用于存储整数和浮点数。MySQL 支持多种数值数据类型，主要包括：

1. 整数类型

• TINYINT：占用 1 字节，范围从 -128 到 127（有符号），0 到 255（无符号）。
• SMALLINT：占用 2 字节，范围从 -32,768 到 32,767（有符号），0 到 65,535（无符号）。
• MEDIUMINT：占用 3 字节，范围从 -8,388,608 到 8,388,607（有符号），0 到 16,777,215（无符号）。
• INT：占用 4 字节，范围从 -2,147,483,648 到 2,147,483,647（有符号），0 到 4,294,967,295（无符号）。
• BIGINT：占用 8 字节，范围从 -9,223,372,036,854,775,808 到 9,223,372,036,854,775,807（有符号），0 到 18,446,744,073,709,551,615（无符号）。

CREATE TABLE example_integers (
    small_number SMALLINT,
    large_number BIGINT
);

2. 浮点数类型

• FLOAT：占用 4 字节，用于存储单精度浮点数。
• DOUBLE：占用 8 字节，用于存储双精度浮点数。
• DECIMAL(p, s)：精确的数值类型，p 为总位数，s 为小数位数，适合存储货币等需要精确值的情况。

CREATE TABLE example_floats (
    price DECIMAL(10, 2),
    rating FLOAT
);

三、日期与时间数据类型

日期和时间类型用于存储时间信息。MySQL 支持以下几种日期与时间数据类型：

1. DATE、TIME 和 DATETIME

• DATE：存储日期，格式为 'YYYY-MM-DD'。
• TIME：存储时间，格式为 'HH:MM:SS'。
• DATETIME：存储日期和时间，格式为 'YYYY-MM-DD HH:MM:SS'。

CREATE TABLE example_datetime (
    event_date DATE,
    event_time TIME,
    event_timestamp DATETIME
);

INSERT INTO example_datetime (event_date, event_time, event_timestamp)
VALUES ('2024-01-01', '12:00:00', '2024-01-01 12:00:00');

2. TIMESTAMP 和 YEAR

• TIMESTAMP：用于存储时间戳，自动记录数据的创建和修改时间。
• YEAR：用于存储年份，格式为 'YYYY'。

CREATE TABLE example_timestamp (
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

四、自定义数据类型与 ENUM、SET 示例

1. ENUM 示例

假设我们有一个用户表，使用 ENUM 类型来表示用户状态。

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    status ENUM('active', 'inactive', 'suspended') NOT NULL
);

INSERT INTO users (name, status) VALUES ('Alice', 'active'), ('Bob', 'inactive');

2. SET 示例

假设我们有一个表来存储用户的兴趣爱好，使用 SET 类型。

CREATE TABLE user_interests (
    user_id INT,
    interests SET('sports', 'music', 'reading', 'traveling')
);

INSERT INTO user_interests (user_id, interests) VALUES (1, 'sports,music'), (2, 'reading,traveling');

总结

通过本文，你学习了 MySQL 中的各种数据类型，包括字符串、数值、日期与时间类型，以及自定义数据类型 ENUM 和 SET。选择合适的数据类型可以提升数据库性能和数据的完整性。在设计数据库时，请根据具体需求合理选择数据类型，以确保最佳的存储和查询效率。