23、数据类型

1、数据类型

MySQL 的数据类型有大概可以分为 5 种，分别是整数类型、浮点数类型和定点数类型、日期和时间类型、字符串类型、二进制类型等。

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	中等整数（主要为MySQL特有）。
INT / INTEGER	4 字节	-2^31 到 2^31-1	0 到 2^32-1	最常用的整数类型，如ID、数量。
BIGINT	8 字节	-2^63 到 2^63-1	0 到 2^64-1	大整数，用于天文数字、科学计算。

注意：PostgreSQL 没有 UNSIGNED 关键字，其 INTEGER（或 INT）即为 4 字节，BIGINT 为 8 字节。SMALLINT 为 2 字节。

浮点数类型包括 FLOAT 和 DOUBLE，定点数类型为 DECIMAL。

类型	语法	描述
DECIMAL / NUMERIC	DECIMAL(M, D)	定点数。M 是总位数（精度），D 是小数点后的位数（标度）。例如 DECIMAL(10, 2) 可以存储 12345678.99。如果未指定，MySQL 默认是 DECIMAL(10, 0)。

类型	占用空间	描述
FLOAT	4 字节	单精度浮点数，精度大约为 7 位有效数字。
DOUBLE	8 字节	双精度浮点数，精度大约为 15 位有效数字。

2) 日期/时间类型

数据类型	范围	时区处理	主要用途
YEAR‌	1901-2155（四位）或1970-2069（两位）	无	仅存储年份
TIME‌	838:59:59 到 838:59:59	无	存储时间间隔或时刻
DATE‌	1000-01-01 到 9999-12-31	无	仅存储日期（年月日）
DATETIME‌	1000-01-01 00:00:00 到 9999-12-31 23:59:59	无	存储完整日期和时间
TIMESTAMP‌	1970-01-01 00:00:01 到 2038-01-19 03:14:07	是（存储为UTC，显示为当前时区）	记录事件发生的时间戳

创建表添加默认值，使得插入数据时，自动获取当前时间：

为了自动获取时间，你必须使用正确的时间类型的字段。在 MySQL 中，主要使用这两种字段类型来实现自动获取时间。

字段类型	说明	推荐使用场景
TIMESTAMP	4字节，带时区转换，范围较小（到2038年）。最常用于自动时间戳。	记录数据的创建时间、最后修改时间等元数据。因为它占用空间小，且自动时间功能支持得最好。
DATETIME	5-8字节，不带时区，范围更大（到9999年）。	记录业务发生的具体时间（如订单支付时间、会议开始时间），或者需要超出2038年的场景。在MySQL 5.6.5之后也支持自动初始化。

• DEFAULT CURRENT_TIMESTAMP: 默认值。当插入新数据如果没有提供这个字段的值，数据库会自动用当前时间填充。

• ON UPDATE CURRENT_TIMESTAMP: 关键所在。当这一行的任何其他字段被更新时，该字段自动刷新为当前时间。

例子：

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100),
    -- 核心：使用 TIMESTAMP 字段
    -- 1. 注册时间：仅默认值为当前时间，更新时不变
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    -- 2. 更新时间：默认值为当前时间，且更新行时自动变为当前时间
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100),
    -- 使用 DATETIME，功能与 TIMESTAMP 一样
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

 

3) 字符串类型

用于存储文本和二进制数据。包括 CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM 和 SET 等。

类型	最大长度	描述	特点与选择建议
CHAR(n)	255 字符	定长字符串。	如果存入的字符串长度小于 n，会用空格填充到指定长度。检索速度快，适合存储长度固定的数据，如MD5哈希值、国家代码。
VARCHAR(n)	65,535 字节（MySQL）	变长字符串。	只占用实际字符串长度+1（或2）字节的空间。节省存储空间，是最常用的文本类型，如用户名、标题、描述。
TEXT	65,535 字节（MySQL）	长文本数据。	用于存储大段文本，如文章内容、日志。不需要指定长度。在MySQL中有 TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT 子类。

CHAR vs VARCHAR：如果长度变化不大且很短，用 CHAR；否则，绝大多数情况用 VARCHAR。

在 SQL（特别是 MySQL、PostgreSQL、SQL Server、Oracle 等主流数据库）中，VARCHAR(n) 或 CHAR VARYING(n) 类型必须指定长度 n，否则会报语法错误。

4) 二进制类型

用于存储图片、文件等二进制数据。包括 BIT、BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。

类型	描述
BINARY(n)	类似 CHAR，但存储的是二进制字节串。
VARBINARY(n)	类似 VARCHAR，但存储的是二进制字节串。
BLOB	大的二进制对象。在MySQL中有 TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB 子类。