【MySQL】数据库的数据类型

个人主页：Guiat
归属专栏：MySQL

正文

1. 数据类型概述

MySQL提供了多种数据类型，用于存储不同种类的数据。选择正确的数据类型对于数据库性能、存储空间优化和数据完整性至关重要。

1.1 数据类型的重要性

• 存储空间优化
• 查询性能提升
• 数据完整性保证
• 内存使用效率提高

1.2 数据类型选择原则

• 选择能够满足需求的最小数据类型
• 考虑未来数据增长的可能性
• 考虑查询操作的效率
• 避免使用过大的字符串类型
• 使用专门的类型存储特殊数据（如日期、时间）

2. 数值类型

2.1 整数类型

MySQL提供了多种整数类型，不同类型支持不同范围的数值：

类型	存储空间	有符号范围	无符号范围
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^31 ~ 2^31-1	0 ~ 2^32-1
BIGINT	8字节	-2^63 ~ 2^63-1	0 ~ 2^64-1

-- 整数类型示例
CREATE TABLE number_examples (tiny_col TINYINT,tiny_col_unsigned TINYINT UNSIGNED,small_col SMALLINT,int_col INT,int_col_zerofill INT ZEROFILL,  -- 显示时用0填充big_col BIGINT UNSIGNED
);

2.2 浮点数和定点数

浮点数和定点数用于存储小数：

类型	存储空间	精度范围	特点
FLOAT	4字节	单精度	近似值，存储空间小
DOUBLE	8字节	双精度	近似值，精度更高
DECIMAL(M,D)	变长	精确存储	精确值，适合金融计算

其中，DECIMAL(M,D)中M表示总位数，D表示小数位数。

-- 小数类型示例
CREATE TABLE decimal_examples (float_col FLOAT(10,2),        -- 总共10位，其中2位小数double_col DOUBLE(16,4),      -- 总共16位，其中4位小数decimal_col DECIMAL(20,6),    -- 精确存储，20位数字，6位小数price DECIMAL(10,2)           -- 适合存储金额
);

2.3 BIT类型

BIT类型用于存储位值：

-- BIT类型示例
CREATE TABLE bit_examples (bit_col1 BIT(1),    -- 可以存储1位，值为0或1bit_col8 BIT(8)     -- 可以存储8位，值范围0-255
);INSERT INTO bit_examples VALUES (b'1', b'10101010');

3. 字符串类型

3.1 CHAR和VARCHAR

类型	最大长度	存储特点	适用场景
CHAR(n)	255字符	固定长度	长度基本固定的数据
VARCHAR(n)	65,535字节	可变长度	长度变化的数据

-- CHAR和VARCHAR示例
CREATE TABLE string_examples (char_col CHAR(10),         -- 固定存储10个字符，不足补空格varchar_col VARCHAR(255),  -- 可变长度，最多255个字符code CHAR(6),              -- 适合存储固定长度的代码name VARCHAR(100)          -- 适合存储名称等可变长度信息
);

3.2 TEXT类型

TEXT类型用于存储大量的文本数据：

类型	最大长度	存储空间
TINYTEXT	255字节	1字节长度前缀
TEXT	65,535字节	2字节长度前缀
MEDIUMTEXT	16,777,215字节	3字节长度前缀
LONGTEXT	4GB	4字节长度前缀

-- TEXT类型示例
CREATE TABLE text_examples (tiny_text_col TINYTEXT,      -- 适合存储小型文本text_col TEXT,               -- 适合一般文章内容medium_text_col MEDIUMTEXT,  -- 适合较长文章long_text_col LONGTEXT       -- 适合非常长的内容
);

3.3 ENUM和SET

ENUM和SET类型允许从预定义的值列表中选择：

-- ENUM和SET示例
CREATE TABLE enum_set_examples (enum_col ENUM('small', 'medium', 'large'),   -- 只能选择其中一个值set_col SET('red', 'green', 'blue', 'yellow')  -- 可以选择多个值
);INSERT INTO enum_set_examples VALUES ('medium', 'red,blue');

3.4 BINARY和VARBINARY

BINARY和VARBINARY类型用于存储二进制数据：

-- 二进制数据类型示例
CREATE TABLE binary_examples (binary_col BINARY(10),        -- 固定长度二进制数据varbinary_col VARBINARY(100)  -- 可变长度二进制数据
);

4. 日期和时间类型

4.1 日期类型概览

类型	存储空间	格式	范围
DATE	3字节	YYYY-MM-DD	1000-01-01 到 9999-12-31
TIME	3-6字节	HH:MM:SS	-838:59:59 到 838:59:59
DATETIME	5-8字节	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00 到 9999-12-31 23:59:59
TIMESTAMP	4字节	YYYY-MM-DD HH:MM:SS	1970-01-01 00:00:01 UTC 到 2038-01-19 03:14:07 UTC
YEAR	1字节	YYYY	1901 到 2155

4.2 日期时间类型特点

-- 日期时间类型示例
CREATE TABLE datetime_examples (date_col DATE,                     -- 仅存储日期time_col TIME,                     -- 仅存储时间datetime_col DATETIME,             -- 存储日期和时间timestamp_col TIMESTAMP,           -- 存储时间戳，自动更新year_col YEAR,                     -- 仅存储年份created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,              -- 创建时间updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP -- 更新时间
);

4.3 TIMESTAMP vs DATETIME

TIMESTAMP和DATETIME的主要区别：

5. JSON类型

5.1 JSON类型介绍

MySQL 5.7.8及更高版本支持原生JSON数据类型，可以高效地存储和访问JSON格式数据。

-- JSON类型示例
CREATE TABLE json_examples (id INT PRIMARY KEY,json_data JSON
);INSERT INTO json_examples VALUES (1, '{"name": "John", "age": 30, "address": {"city": "New York", "zip": "10001"}, "skills": ["PHP", "MySQL", "JavaScript"]}'
);

5.2 JSON操作函数

MySQL提供了一系列函数来操作JSON数据：

-- 提取JSON数据
SELECT json_data->'$.name' AS name,json_data->'$.age' AS age,json_data->'$.address.city' AS city,json_data->'$.skills[0]' AS first_skill
FROM json_examples;-- 修改JSON数据
UPDATE json_examples 
SET json_data = JSON_SET(json_data, '$.age', 31, '$.skills[3]', 'Python')
WHERE id = 1;

6. 空间数据类型

6.1 几何数据类型

MySQL支持OpenGIS几何类型，用于存储地理空间数据：

类型	描述
GEOMETRY	任何几何体
POINT	点
LINESTRING	线
POLYGON	多边形
MULTIPOINT	点集合
MULTILINESTRING	线集合
MULTIPOLYGON	多边形集合
GEOMETRYCOLLECTION	几何体集合

-- 空间数据类型示例
CREATE TABLE spatial_examples (id INT PRIMARY KEY,location POINT,area POLYGON
);-- 插入点数据
INSERT INTO spatial_examples(id, location) VALUES (1, ST_GeomFromText('POINT(40.7128 -74.0060)'));-- 插入多边形数据
INSERT INTO spatial_examples(id, area) VALUES (2, ST_GeomFromText('POLYGON((0 0, 0 10, 10 10, 10 0, 0 0))')
);

6.2 空间索引

MySQL支持对空间数据创建索引以加速查询：

-- 创建空间索引
CREATE SPATIAL INDEX idx_location ON spatial_examples(location);

7. 特殊数据类型

7.1 BLOB类型

BLOB (Binary Large Object) 类型用于存储二进制大对象数据，如图片、视频等：

类型	最大长度	存储空间
TINYBLOB	255字节	1字节长度前缀
BLOB	65,535字节	2字节长度前缀
MEDIUMBLOB	16,777,215字节	3字节长度前缀
LONGBLOB	4GB	4字节长度前缀

-- BLOB类型示例
CREATE TABLE blob_examples (id INT PRIMARY KEY,tiny_blob_col TINYBLOB,blob_col BLOB,medium_blob_col MEDIUMBLOB,long_blob_col LONGBLOB,image MEDIUMBLOB,      -- 适合存储图片document LONGBLOB      -- 适合存储PDF文档等
);

7.2 INET6类型

MySQL 8.0引入了专门用于存储IPv6地址的函数：

-- IPv6处理函数
SELECT INET6_ATON('2001:db8::1') AS ipv6_binary;
SELECT INET6_NTOA(INET6_ATON('2001:db8::1')) AS ipv6_text;

8. 数据类型转换

8.1 隐式转换

MySQL会在必要时进行隐式数据类型转换：

-- 隐式转换示例
CREATE TABLE conversion_test (id INT PRIMARY KEY,str_val VARCHAR(20)
);INSERT INTO conversion_test VALUES (1, '123');-- 字符串会被隐式转换为数字
SELECT * FROM conversion_test WHERE str_val = 123;

8.2 显式转换

可以使用CAST()或CONVERT()函数进行显式类型转换：

-- 显式转换示例
SELECT CAST('2023-10-15' AS DATE) AS date_value,CONVERT('123', SIGNED INTEGER) AS int_value,CAST(123.45 AS CHAR) AS string_value;

9. 数据类型性能考量

9.1 存储空间比较

9.2 性能优化建议

• 整数列优先选择 INT
• 精确计算（如金额）使用 DECIMAL
• 固定长度字符使用 CHAR，变长字符使用 VARCHAR
• 避免使用 TEXT/BLOB 作为经常查询的列
• TIMESTAMP 比 DATETIME 更节省空间
• 使用 ENUM 代替字符串可大幅节省空间
• 考虑每列是否需要允许 NULL 值

10. 实践应用

10.1 电商数据库案例

CREATE TABLE products (product_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,  -- 无符号整数，自增name VARCHAR(255) NOT NULL,                          -- 产品名称description TEXT,                                    -- 产品描述，可能很长price DECIMAL(10, 2) NOT NULL,                       -- 精确存储价格stock SMALLINT UNSIGNED DEFAULT 0,                   -- 库存数量，不会太大category ENUM('electronics', 'clothing', 'food', 'books', 'other'), -- 固定类别选择tags SET('new', 'sale', 'popular', 'recommended'),   -- 多个标签组合attributes JSON,                                     -- 灵活存储产品属性image MEDIUMBLOB,                                    -- 产品图片created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,      -- 创建时间updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP -- 更新时间
);CREATE TABLE customers (customer_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,name VARCHAR(100) NOT NULL,email VARCHAR(100) UNIQUE NOT NULL,password CHAR(60) NOT NULL,                          -- 存储哈希密码，固定长度phone VARCHAR(20),birth_date DATE,                                     -- 仅需存储日期address JSON,                                        -- 灵活存储地址信息last_login DATETIME,                                 -- 需要精确的登录时间created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);CREATE TABLE orders (order_id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, -- 订单ID可能会很大customer_id INT UNSIGNED,order_date DATETIME NOT NULL,status ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled'),total_amount DECIMAL(12, 2) NOT NULL,                -- 支持更大金额shipping_address TEXT NOT NULL,payment_method ENUM('credit_card', 'debit_card', 'paypal', 'bank_transfer'),tracking_number VARCHAR(50),notes TEXT,FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);CREATE TABLE order_items (order_id BIGINT UNSIGNED,product_id INT UNSIGNED,quantity SMALLINT UNSIGNED NOT NULL,unit_price DECIMAL(10, 2) NOT NULL,PRIMARY KEY (order_id, product_id),FOREIGN KEY (order_id) REFERENCES orders(order_id),FOREIGN KEY (product_id) REFERENCES products(product_id)
);

10.2 数据类型选择分析

数据	选择的类型	原因
用户ID	INT UNSIGNED	节省空间，不需要负值
订单ID	BIGINT UNSIGNED	预期订单量巨大
产品名称	VARCHAR(255)	长度可变，但有上限
产品描述	TEXT	可能非常长，不常用于查询条件
价格	DECIMAL(10,2)	需要精确计算
类别	ENUM	固定几个选项，节省空间
创建时间	TIMESTAMP	自动设置，占用空间小
地址信息	JSON	灵活存储结构化数据
产品图片	MEDIUMBLOB	二进制数据，中等大小

10.3 存储空间估算

每1000万产品记录估算:
- product_id (INT): 4字节 × 1000万 = 40MB
- name (VARCHAR): 平均50字节 × 1000万 = 500MB
- price (DECIMAL): 5字节 × 1000万 = 50MB
- stock (SMALLINT): 2字节 × 1000万 = 20MB
- category (ENUM): 1字节 × 1000万 = 10MB
- timestamps: 8字节 × 1000万 = 80MB
（不含TEXT、BLOB和JSON字段）总计: 约700MB（基本字段）

选择合适的数据类型不仅影响性能，还能显著影响存储空间需求和查询效率。在设计数据库时，应根据实际需求和数据特性，合理选择最适合的数据类型。

结语
感谢您的阅读！期待您的一键三连！欢迎指正！