MySQL 数据类型详解

在MySQL数据库中，数据类型的选择对性能和存储效率有着至关重要的影响。选择正确的数据类型可以最大限度地利用资源，并提高查询速度。

一、数值类型详解

数值类型用于存储数字数据，根据数据范围和精度需求，MySQL提供多种整数和浮点数类型。

1. 整数类型

类型	大小(字节)	范围	性能	用途	示例
TINYINT	1	有符号: -128 to 127 <br> 无符号: 0 to 255	最优	存储非常小的整数值，如状态码、性别	gender TINYINT(1) 存储性别，0-女，1-男
SMALLINT	2	有符号: -32768 to 32767 <br> 无符号: 0 to 65535	较优	存储较小的整数值，如端口号、小型计数器	port SMALLINT UNSIGNED 存储网页端口号，例如 80
MEDIUMINT	3	有符号: -8388608 to 8388607 <br> 无符号: 0 to 16777215		存储中等范围的整数值	city_id MEDIUMINT UNSIGNED 存储城市ID
INT	4	有符号: -2147483648 to 2147483647 <br> 无符号: 0 to 4294967295	良好	存储常规整数值，如用户ID、文章ID	user_id INT UNSIGNED AUTO_INCREMENT 存储自增用户ID
BIGINT	8	有符号: -9223372036854775808 to 9223372036854775807 <br> 无符号: 0 to 18446744073709551615		存储超大整数值，如高精度时间戳、大文件大小	timestamp BIGINT UNSIGNED 存储高精度时间戳（以纳秒为单位）

示例：

CREATE TABLE users (
    user_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255) NOT NULL,
    age TINYINT UNSIGNED
);

选择整数类型的最佳实践:

• 优先选择能够存储数据的最小整数类型: 这可以节省存储空间，提高查询性能。

• 使用 UNSIGNED 关键字: 如果确定列中只存储非负数，可以使用 UNSIGNED 关键字，这可以将正数范围扩大一倍。

• 避免使用 ZEROFILL 属性: ZEROFILL 属性会在数字前面填充零，但这会降低插入和更新性能，并且增加存储空间。

2. 浮点数类型

类型	大小(字节)	范围	精度	用途	示例
FLOAT	4	单精度浮点数	约7位有效数字	存储需要小数部分但精度要求不高的数值，如温度	temperature FLOAT(4,2) 存储温度，例如 25.5 ℃
DOUBLE	8	双精度浮点数	约15位有效数字	存储需要更高精度的浮点数值，如经纬度坐标、科学计算数据	longitude DOUBLE 存储经度，例如 121.4737
DECIMAL	可变	精确数值	用户指定	适用于需要精确存储和计算的数值，如货币、金融数据	price DECIMAL(10,2) 存储商品价格，例如 19.99 元

示例：

CREATE TABLE products (
    product_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(255) NOT NULL,
    price DECIMAL(10,2) NOT NULL
);

选择浮点数类型的最佳实践:

• 避免使用浮点数进行精确比较: 由于浮点数存储方式的限制，直接比较两个浮点数可能会导致意外结果。应该使用 ROUND() 函数或其他方法进行比较。

• 使用 DECIMAL 类型存储精确数值: DECIMAL 类型以字符串形式存储数值，可以保证精度，适用于金融等对精度要求高的应用场景。

• 根据实际需求选择精度: FLOAT 和 DOUBLE 类型提供了默认精度，但 DECIMAL 类型需要指定精度。选择合适的精度可以节省存储空间。

二、日期和时间类型详解

MySQL 提供多种数据类型来存储日期和时间信息，每种类型都具有特定的格式和范围。

类型	大小(字节)	范围	格式	用途	示例
DATE	3	'1000-01-01' to '9999-12-31'	YYYY-MM-DD	存储日期值，不包含时间部分	birthday DATE 存储生日，例如 '1990-01-01'
TIME	3	'-838:59:59' to '838:59:59'	HH:MM:SS	存储时间值，不包含日期部分	duration TIME 存储会议持续时间，例如 '01:30:00'
YEAR	1	1901 to 2155	YYYY	存储年份值	release_year YEAR 存储产品发布年份，例如 '2023'
DATETIME	8	'1000-01-01 00:00:00' to '9999-12-31 23:59:59'	YYYY-MM-DD HH:MM:SS	存储日期和时间值，但与时区无关	registration_time DATETIME 存储用户注册时间，例如 '2023-04-15 14:30:00'
TIMESTAMP	4	'1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC	YYYY-MM-DD HH:MM:SS	存储时间戳值，与时区相关，自动更新	last_updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP 记录数据最后更新时间

示例：

CREATE TABLE articles (
    article_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    publish_date DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

选择日期和时间类型的最佳实践:

• 根据实际需求选择合适的类型: 如果只需要存储日期信息，使用 DATE 类型即可，如果需要存储时间信息，则选择 TIME 或 TIMESTAMP 类型。

• 使用 TIMESTAMP 类型记录创建时间和更新时间: TIMESTAMP 类型可以自动更新，并且与时区相关，方便进行时区转换。

• 避免使用 YEAR(2): YEAR(2) 只存储两位数年份，容易造成歧义。建议使用 YEAR(4) 存储四位数年份。

三、字符串类型详解

MySQL提供了丰富的字符串类型来存储文本数据，每种类型都有其特定的长度限制和存储方式。

1. 字符串类型

类型	大小	描述	性能	用途	示例
CHAR	0-255 bytes	定长字符串，不足长度会用空格填充	较优	存储固定长度的字符串，如邮编、MD5值	zip_code CHAR(6) 存储邮编，例如 '100000'
VARCHAR	0-65,535 bytes	变长字符串，只存储实际字符，节省空间	良好	存储可变长度的字符串，如用户名、文章标题	username VARCHAR(255) 存储用户名，例如 'johndoe'
TINYTEXT	0-255 bytes	短文本数据		存储简短的文本内容，如评论标签	tags TINYTEXT 存储商品标签，例如 '电子产品,手机,智能手机'
TEXT	0-65,535 bytes	长文本数据		存储较长的文本内容，如文章内容	content TEXT 存储博客文章内容
MEDIUMTEXT	0-16,777,215 bytes	中等长度文本数据			summary MEDIUMTEXT 存储中等长度的文章摘要
LONGTEXT	0-4,294,967,295 bytes	超长文本数据	较差	存储超长的文本内容，如大型文档	document LONGTEXT 存储用户上传的文件内容

示例：

CREATE TABLE comments (
    comment_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    article_id INT UNSIGNED NOT NULL,
    author VARCHAR(255) NOT NULL,
    content TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

选择字符串类型的最佳实践:

• 优先选择 VARCHAR: VARCHAR 类型可以根据实际内容动态调整长度，比 CHAR 类型更加灵活，节省存储空间。

• 避免过度使用 TEXT 和 BLOB: TEXT 和 BLOB 类型存储的数据量较大，查询效率较低，应该尽量避免使用。

• 为字符串列指定合适的字符集和排序规则: 这可以确保数据正确存储和排序，避免出现乱码等问题。

• 使用 TEXT 类型存储长文本: 如果需要存储超过 VARCHAR 类型长度限制的文本，可以使用 TEXT 类型。

2. 枚举和集合类型

类型	大小	描述	性能	用途	示例
ENUM	1-255 bytes	枚举类型，只能存储预定义的值	良好	存储有限的选项，如订单状态、性别	order_status ENUM('pending', 'processing', 'shipped', 'delivered') 存储订单状态
SET	0-64 bytes	集合类型，可以存储多个预定义的值		存储多个选项，如用户爱好	hobbies SET('music', 'sports', 'reading', 'travel', 'cooking') 存储用户爱好，例如 'music,reading'

示例：

CREATE TABLE orders (
    order_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    order_status ENUM('pending', 'processing', 'shipped', 'delivered') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

使用枚举和集合类型的最佳实践:

• 使用枚举类型代替字符串类型: 如果一个字段只有几种固定的可选值，使用枚举类型可以提高数据完整性，并且节省存储空间。

• 避免在枚举类型中存储可变数据: 枚举类型的值应该在定义时就确定，并且不应该轻易修改。

• 谨慎使用集合类型: 集合类型虽然可以存储多个值，但查询效率较低，应该谨慎使用。

四、二进制类型详解

MySQL 提供了多种二进制类型用于存储图片、视频、音频等二进制数据。

类型	大小	描述	用途	示例
BIT	1-8 bytes	位字段，存储0或1	存储布尔值、标志位	is_active BIT(1) 存储用户是否激活，例如 b'1'
BINARY	0-255 bytes	定长二进制数据		uuid BINARY(16) 存储 UUID，例如 0x00112233445566778899aabbccddeeff
VARBINARY	0-65,535 bytes	变长二进制数据	存储图片、视频等二进制文件	avatar VARBINARY(65535) 存储用户头像
TINYBLOB	0-255 bytes	短二进制数据		thumbnail TINYBLOB 存储图片缩略图
BLOB	0-65,535 bytes	二进制数据		image BLOB 存储图片
MEDIUMBLOB	0-16,777,215 bytes	中等长度二进制数据		video MEDIUMBLOB 存储视频
LONGBLOB	0-4,294,967,295 bytes	超长二进制数据		document LONGBLOB 存储大型二进制文件

示例：

CREATE TABLE images (
    image_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    filename VARCHAR(255) NOT NULL,
    image_data LONGBLOB,
    uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

使用二进制类型的最佳实践:

• 避免存储过大的二进制数据: 存储过大的二进制数据会影响查询性能，建议将大文件存储在文件系统中，并在数据库中存储文件路径。

• 使用合适的二进制类型: 根据数据大小选择合适的二进制类型，避免浪费存储空间。

五、JSON类型详解

MySQL 5.7 版本开始支持 JSON 数据类型，用于存储 JSON 格式的数据。

类型	描述	用途
JSON	存储 JSON 格式数据	存储灵活的、半结构化的数据，如产品属性、用户配置

示例：

CREATE TABLE products (
    product_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(255) NOT NULL,
    specifications JSON
);

INSERT INTO products (product_name, specifications) VALUES 
('Smartphone', '{ "color": "black", "memory": "128GB", "camera": "50MP" }');

使用 JSON 类型的最佳实践:

• 使用 JSON 类型存储半结构化数据: JSON 类型非常适合存储半结构化数据，例如包含多个属性的对象。

• 不要过度使用 JSON 类型: JSON 类型虽然灵活，但查询效率低于传统的关系型数据表。应该根据实际需求选择合适的存储方式。

• 使用 JSON 函数进行数据操作: MySQL 提供了丰富的 JSON 函数，可以方便地对 JSON 数据进行查询、修改等操作。

本文详细介绍了 MySQL 数据类型，包括其特性、适用场景。希望各位看官通过本文的学习，能够更好地理解和应用 MySQL 数据类型，构建高效的数据库。感谢各位看官的观看，下期见，谢谢~