likes
comments
collection
share

轻松入门MySQL：MySQL字段类型精解，优化存储结构，助力系统高效运行（2）

作者站长头像
站长
· 阅读数 8

在MySQL中，合理选择字段类型是确保系统高效运行的关键之一。正确的字段类型定义不仅能节省存储空间，还能提升数据查询和处理速度。相反，若定义不当，可能导致数据超限、系统报错，甚至影响整个系统的可靠性。

整数类型

MySQL提供了五种整数类型，分别是TINYINT、SMALLINT、MEDIUMINT、INT(INTEGER)和BIGINT。这些类型有不同的取值范围和占用字节数，适用于不同场景。在选择时，需权衡存储空间和可靠性，避免范围过小导致数据超限。

类型有符号数取值范围无符号数取值范围占用字节数适用场景
TINYINT-128 ~ 1270 ~ 2551枚举数据、小范围固定场景
SMALLINT-32768 ~ 327670 ~ 655352较小范围统计数据
MEDIUMINT-8388608 ~ 83886070 ~ 167772153较大整数计算
INT(INTEGER)-2147483648 ~ 21474836470 ~ 42949672954取值范围足够大
BIGINT-9223372036854775808 ~ 92233720368547758070 ~ 184467440737095516158特别巨大整数处理

在实际工作中，确保数据不超出取值范围是首要任务，然后再考虑如何优化存储空间。

浮点数类型和定点数类型

浮点数类型包括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类型最为灵活，根据实际长度存储，但不能作为主键。

文本类型详细解释：

  1. CHAR(M): 固定长度字符串

    • 特点： CHAR是一种固定长度的字符串类型，其中M表示字符的最大长度。无论实际存储的字符串长度是多少，CHAR始终占用M个字符的存储空间。
    • 适用场景： 适用于存储长度基本相同时的字符串，比如存储国家代码或固定长度的标识符。

    例子：

    CREATE TABLE test.products (
  product_id INT PRIMARY KEY,
  product_code CHAR(10),
  product_name VARCHAR(255)
);

  2. VARCHAR(M): 可变长度字符串

    • 特点： VARCHAR是一种可变长度的字符串类型，其中M表示字符的最大长度。实际占用的存储空间根据存储的具体字符串长度动态变化。
    • 适用场景： 适用于存储长度变化较大的字符串，能够灵活调整存储空间，节省存储空间。

    例子：

    CREATE TABLE test.products (
  product_id INT PRIMARY KEY,
  product_description VARCHAR(1000),
  product_price DECIMAL(10, 2)
);

  3. 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)
);

  4. ENUM: 枚举类型，取值预先设定

    • 特点： ENUM用于存储一组预先设定的取值，只能选择其中的一个值。ENUM提供了枚举类型的功能，确保存储的值在预定的范围内。
    • 适用场景： 适用于某个字段只能取特定值中的一个情况，比如性别字段。

    例子：

    CREATE TABLE test.users (
  user_id INT PRIMARY KEY,
  username VARCHAR(50),
  gender ENUM('Male', 'Female', 'Other')
);

  5. 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
);

小结与最佳实践

在选择字段类型时，需根据实际业务需求和数据特性进行权衡。一些小技巧包括：

  1. 精准选择整数类型： 根据数据范围选择合适的整数类型，避免范围过小或过大。
  2. 注意浮点数精度： 对于精度要求高的场景，使用DECIMAL类型；对于一般应用，FLOAT或DOUBLE足够。
  3. 灵活使用文本类型： 根据实际需求选择CHAR、VARCHAR、TEXT等文本类型，确保存储和查询效率。
  4. 善用日期与时间类型： 使用DATETIME类型存储完整的日期和时间信息，提高数据的可读性。
  5. 结合业务需求选择其他类型： 枚举、集合、二进制、自增主键、空间数据类型等都应根据具体业务场景选用。

通过正确选择字段类型，可以优化数据库存储结构，提升系统效率，同时确保数据的一致性和可靠性。

转载自:https://juejin.cn/post/7333761832472346624
评论
请登录