在MySQL中，合理选择字段类型是确保系统高效运行的关键之一。正确的字段类型定义不仅能节省存储空间，还能提升数据查询和处理速度。相反，若定义不当，可能导致数据超限、系统报错，甚至影响整个系统的可靠性。
整数类型
MySQL提供了五种整数类型，分别是TINYINT、SMALLINT、MEDIUMINT、INT(INTEGER)和BIGINT。这些类型有不同的取值范围和占用字节数，适用于不同场景。在选择时，需权衡存储空间和可靠性，避免范围过小导致数据超限。
| 类型 | 有符号数取值范围 | 无符号数取值范围 | 占用字节数 | 适用场景 |
|---|---|---|---|---|
| TINYINT | -128 ~ 127 | 0 ~ 255 | 1 | 枚举数据、小范围固定场景 |
| SMALLINT | -32768 ~ 32767 | 0 ~ 65535 | 2 | 较小范围统计数据 |
| MEDIUMINT | -8388608 ~ 8388607 | 0 ~ 16777215 | 3 | 较大整数计算 |
| INT(INTEGER) | -2147483648 ~ 2147483647 | 0 ~ 4294967295 | 4 | 取值范围足够大 |
| BIGINT | -9223372036854775808 ~ 9223372036854775807 | 0 ~ 18446744073709551615 | 8 | 特别巨大整数处理 |
在实际工作中，确保数据不超出取值范围是首要任务，然后再考虑如何优化存储空间。
浮点数类型和定点数类型
浮点数类型包括FLOAT、DOUBLE、REAL，而定点数类型则以DECIMAL为代表。浮点数适用于科学计算等场景，但存在精度问题。DECIMAL则是高精度的定点数类型，适合对精度要求较高的场景。
- FLOAT和DOUBLE的区别：FLOAT占用字节数少，取值范围小；DOUBLE占用字节数多，取值范围大。
例子：
CREATE TABLE test.goods_master (
price DOUBLE
);
-- 数据插入
INSERT INTO test.goods_master (price) VALUES (0.47), (0.44), (0.19);
-- 查询价格合计
SELECT SUM(price) FROM test.goods_master;
文本类型
文本类型用于存储字符串数据，包括CHAR、VARCHAR、TEXT、ENUM和SET等。其中，TEXT类型最为灵活，根据实际长度存储，但不能作为主键。
文本类型详细解释：
CHAR(M): 固定长度字符串
- 特点： CHAR是一种固定长度的字符串类型，其中M表示字符的最大长度。无论实际存储的字符串长度是多少，CHAR始终占用M个字符的存储空间。
- 适用场景： 适用于存储长度基本相同时的字符串，比如存储国家代码或固定长度的标识符。
例子：
CREATE TABLE test.products (
product_id INT PRIMARY KEY,
product_code CHAR(10),
product_name VARCHAR(255)
);
VARCHAR(M): 可变长度字符串
- 特点： VARCHAR是一种可变长度的字符串类型，其中M表示字符的最大长度。实际占用的存储空间根据存储的具体字符串长度动态变化。
- 适用场景： 适用于存储长度变化较大的字符串，能够灵活调整存储空间，节省存储空间。
例子：
CREATE TABLE test.products (
product_id INT PRIMARY KEY,
product_description VARCHAR(1000),
product_price DECIMAL(10, 2)
);
TEXT: 字符串，按照实际长度存储
- 特点： TEXT用于存储较长的字符串，其长度可以非常大，不像CHAR或VARCHAR有长度限制。TEXT类型的数据按照实际长度存储，但不能用作主键。
- 适用场景： 适用于存储较长的文本内容，比如文章内容或评论。
例子：
CREATE TABLE test.product_reviews (
review_id INT PRIMARY KEY,
product_id INT,
user_id INT,
review_text TEXT,
FOREIGN KEY (product_id) REFERENCES products(product_id),
FOREIGN KEY (user_id) REFERENCES users(user_id)
);
ENUM: 枚举类型，取值预先设定
- 特点： ENUM用于存储一组预先设定的取值，只能选择其中的一个值。ENUM提供了枚举类型的功能，确保存储的值在预定的范围内。
- 适用场景： 适用于某个字段只能取特定值中的一个情况，比如性别字段。
例子：
CREATE TABLE test.users (
user_id INT PRIMARY KEY,
username VARCHAR(50),
gender ENUM('Male', 'Female', 'Other')
);
SET: 字符串对象，取值预先设定
- 特点： SET也用于存储一组预先设定的取值，但不同于ENUM的是，SET允许存储多个取值，这些值以逗号分隔。
- 适用场景： 适用于某个字段可以取多个预定值的情况，比如用户可能有多个角色。
例子：
CREATE TABLE test.users (
user_id INT PRIMARY KEY,
username VARCHAR(50),
roles SET('Admin', 'Editor', 'Viewer')
);
日期与时间类型
日期与时间类型包括YEAR、TIME、DATE、DATETIME和TIMESTAMP。推荐使用DATETIME类型，因为它包括完整的日期和时间信息，使用方便。
例子：
CREATE TABLE test.sales (
sale_datetime DATETIME
);
在实际选择时，考虑数据的完整性和系统稳定性，优先选择DATETIME类型。
枚举与集合类型
MySQL提供了ENUM和SET类型，它们适用于在一组预定义的取值中选择。ENUM用于存储单个值，而SET可存储多个取值。这两种类型在某些场景下能够有效减少存储空间，并提高数据的可读性。
例子：
CREATE TABLE test.user (
gender ENUM('Male', 'Female', 'Other')
);
二进制类型
BINARY和VARBINARY类型用于存储二进制数据，如图像、音频和视频文件。这些类型可以节省存储空间，但在查询时需要谨慎处理，因为它们可能影响索引效率。
例子：
CREATE TABLE test.images (
image_data BINARY(100)
);
自增主键
对于需要唯一标识每一行记录的情况，使用自增主键是一种有效的方式。通过AUTO_INCREMENT关键字，MySQL会自动为每条插入的记录分配一个唯一的主键值，简化了数据插入和管理的操作。
例子：
CREATE TABLE test.products (
product_id INT AUTO_INCREMENT PRIMARY KEY,
product_name VARCHAR(50)
);
空间数据类型
MySQL还提供了一些空间数据类型，如POINT、LINESTRING和POLYGON，用于存储地理空间数据。这些类型可以在地理信息系统（GIS）应用中发挥重要作用。
例子：
CREATE TABLE test.locations (
location POINT
);
多值类型
JSON和ARRAY类型允许存储多个值的结构化数据。它们在处理复杂的数据结构时非常有用，并且能够提高灵活性。
例子：
CREATE TABLE test.orders (
order_details JSON
);
小结与最佳实践
在选择字段类型时，需根据实际业务需求和数据特性进行权衡。一些小技巧包括：
通过正确选择字段类型，可以优化数据库存储结构，提升系统效率，同时确保数据的一致性和可靠性。
