MYSQL中的数据类型

MySQL的数据类型是构建表结构的基础，不同类型直接影响存储效率、查询性能和数据处理方式。以下是详细分类和使用建议，覆盖高频场景和避坑指南：

一、数值类型（存储数字）

1. 整数类型

注意点：

• 优先用INT（性价比高），除非明确需要更大范围（如BIGINT存时间戳）。
• 无符号（UNSIGNED）可使正数范围翻倍，但字段不能存负数（如年龄用TINYINT UNSIGNED）。

2. 浮点类型

避坑指南：

• 不要用FLOAT/DOUBLE存金额（如FLOAT存0.1+0.2可能得到0.30000000000000004），必须用DECIMAL。
• DECIMAL的M是总位数，D是小数位数（如DECIMAL(5,2)最大存999.99）。

二、字符串类型（存储文本）

1. 定长与变长字符串

性能对比：

• CHAR适合短且长度固定的数据（如性别'男'/'女'），读写更快（无需计算长度）。
• VARCHAR节省空间（如VARCHAR(100)存5个字符只占7字节），但频繁更新可能产生碎片。

2. 大文本类型

注意点：

• 避免在TEXT字段上建索引（性能差），如需检索可考虑FULLTEXT索引。
• TEXT不能有默认值（但VARCHAR可以）。

3. 特殊字符串

• ENUM（枚举）：
  存储固定值列表（如性别ENUM('男','女','未知')），用数字（1,2,3）内部存储，节省空间。
  缺点：修改枚举值需ALTER TABLE，不灵活。

• SET（集合）：
  存储多选值（如爱好SET('读书','运动','音乐')），每个选项占1位（最多64个选项）。
  示例：INSERT INTO t VALUES ('读书,音乐');

三、日期与时间类型

核心区别：

• DATETIME vs TIMESTAMP：
  • DATETIME存绝对值（如存2023-01-01 12:00:00，全球看到的都是这个时间）。
  • TIMESTAMP存相对值（实际存的是UTC时间戳，查询时自动转换为当前时区时间）。
  • 建议：存用户无关的时间（如订单创建时间）用DATETIME，存与服务器时区相关的时间（如登录时间）用TIMESTAMP。

自动更新特性：

-- 创建表时设置自动更新时间戳
CREATE TABLE t (
  id INT,
  create_time DATETIME DEFAULT CURRENT_TIMESTAMP,  -- 插入时自动填充当前时间
  update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP  -- 更新时自动刷新
);

四、二进制类型（存储非文本数据）

避坑指南：

• 不建议直接存大文件（如图片、视频）到数据库（影响性能），建议存文件路径，文件放服务器磁盘或对象存储（如阿里云OSS）。
• 二进制类型与字符串类型的区别：
  • 二进制类型按字节存储（如BINARY(3)存'abc'占3字节），字符串类型按字符存储（如CHAR(3)在UTF-8下存'中国'占6字节）。

五、JSON类型（MySQL 5.7+新增）

• 特点：

  • 存储JSON格式数据（如{"name":"张三","age":20}）。
  • 支持索引（通过JSON_EXTRACT()函数）。
  • 查询效率高于TEXT（无需解析整个JSON）。

• 示例：

  CREATE TABLE user (
    id INT,
    info JSON
  );
  
  -- 插入JSON数据
  INSERT INTO user VALUES (1, '{"name":"张三","age":20,"hobbies":["读书","运动"]}');
  
  -- 查询JSON字段（用->或->>操作符）
  SELECT info->'$.name' AS name FROM user;  -- 返回带引号的字符串
  SELECT info->>'$.name' AS name FROM user;  -- 返回不带引号的字符串
  
  -- 对JSON字段建索引
  ALTER TABLE user ADD INDEX idx_info_age ((info->>'$.age'));
  

• 适用场景：

  • 半结构化数据（如配置项、用户偏好）。
  • 数据结构不固定（避免频繁改表结构）。

六、选择数据类型的核心原则

1. 够用就好，越小越好：

   • 能用TINYINT就不用INT（如状态码），节省存储空间。
   • 示例：用户积分用INT（最大21亿），但论坛帖子数可能需要BIGINT。

2. 避免NULL：

   • 字段尽量加NOT NULL（否则可能导致索引失效、查询效率降低）。
   • 示例：age INT DEFAULT 0比age INT NULL更优。

3. 优先用内置类型：

   • 日期用DATE/DATETIME，而非字符串（字符串无法用日期函数，如YEAR()）。
   • 示例：存IP地址用INT UNSIGNED（通过INET_ATON()/INET_NTOA()转换），而非VARCHAR(15)。

4. 权衡存储与性能：

   • 频繁更新的字段避免用VARCHAR（可能产生碎片），但也别过度用CHAR（浪费空间）。
   • 示例：用户昵称用VARCHAR(50)，而非CHAR(50)。

七、常见场景的数据类型选择

掌握这些类型的特性和适用场景，你设计的表结构会更合理，查询性能也会更好。实际开发中，建议结合业务需求（如数据量、查询模式）选择最优类型。

在Java开发中，与MySQL数据类型的对应关系需要从JDBC映射、ORM框架适配、数据处理逻辑等多个维度综合考虑。以下是系统性的对应方案和避坑指南：

一、基础数据类型映射（JDBC标准）

Java与MySQL的基础数据类型映射遵循JDBC规范，这是最底层的映射关系，所有ORM框架都基于此：

二、ORM框架的优化映射（以MyBatis和Hibernate为例）

1. MyBatis映射

MyBatis通过typeHandler处理特殊类型，以下是常用配置：

<!-- mybatis-config.xml 配置 -->
<typeHandlers>
  <!-- 注册Java 8日期时间类型处理器 -->
  <typeHandler handler="org.apache.ibatis.type.LocalDateTimeTypeHandler"/>
  <typeHandler handler="org.apache.ibatis.type.LocalDateTypeHandler"/>
  <!-- 自定义JSON类型处理器 -->
  <typeHandler handler="com.example.JsonTypeHandler"/>
</typeHandlers>

实体类示例：

public class User {
    private Long id;
    private String username;
    private BigDecimal balance;  // 对应DECIMAL
    private LocalDateTime createTime;  // 对应DATETIME
    private Map<String, Object> config;  // 对应JSON字段（需自定义TypeHandler）
    // getters/setters
}

2. Hibernate映射

Hibernate通过JPA注解和自定义转换器处理类型映射：

@Entity
@Table(name = "user")
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;  // 对应BIGINT
    
    @Column(name = "username")
    private String username;
    
    @Column(name = "balance", precision = 10, scale = 2)
    private BigDecimal balance;  // 对应DECIMAL(10,2)
    
    @Column(name = "create_time")
    private LocalDateTime createTime;  // 对应DATETIME
    
    @Convert(converter = JsonConverter.class)  // 自定义JSON转换器
    @Column(name = "config", columnDefinition = "JSON")
    private Map<String, Object> config;
    // getters/setters
}

自定义JSON转换器示例：

@Converter(autoApply = true)
public class JsonConverter implements AttributeConverter<Map<String, Object>, String> {
    private final ObjectMapper objectMapper = new ObjectMapper();
    
    @Override
    public String convertToDatabaseColumn(Map<String, Object> attribute) {
        try {
            return objectMapper.writeValueAsString(attribute);
        } catch (JsonProcessingException e) {
            throw new RuntimeException("JSON序列化失败", e);
        }
    }
    
    @Override
    public Map<String, Object> convertToEntityAttribute(String dbData) {
        try {
            return objectMapper.readValue(dbData, new TypeReference<Map<String, Object>>() {});
        } catch (JsonProcessingException e) {
            throw new RuntimeException("JSON反序列化失败", e);
        }
    }
}

三、特殊场景处理方案

1. 无符号整数（如BIGINT UNSIGNED）

MySQL的BIGINT UNSIGNED范围超过Java的long，需特殊处理：

// 方案1：映射为String（最简单，但无法直接做数学运算）
ResultSet rs = statement.executeQuery("SELECT unsigned_col FROM table");
if (rs.next()) {
    String unsignedValue = rs.getString("unsigned_col");  // 直接取字符串
}

// 方案2：映射为BigInteger（可做数学运算）
ResultSet rs = statement.executeQuery("SELECT unsigned_col FROM table");
if (rs.next()) {
    BigInteger unsignedValue = rs.getBigDecimal("unsigned_col").toBigInteger();
}

2. JSON字段处理

从MySQL 5.7+开始支持原生JSON类型，Java中可通过以下方式处理：

// 方案1：手动解析（适用于简单场景）
ResultSet rs = statement.executeQuery("SELECT json_col FROM table");
if (rs.next()) {
    String jsonStr = rs.getString("json_col");
    JSONObject jsonObj = new JSONObject(jsonStr);  // 使用JSON库解析
    String value = jsonObj.getString("key");
}

// 方案2：ORM框架自动映射（如Hibernate+Jackson）
@Entity
public class Product {
    @Convert(converter = JsonConverter.class)
    private Map<String, Object> attributes;  // 自动映射JSON字段
}

3. 日期时间时区问题

MySQL的TIMESTAMP会自动转换为服务器时区，而DATETIME不会。建议：

// 方案1：统一使用UTC时区（推荐）
// 数据库连接URL添加时区参数
jdbc:mysql://localhost:3306/dbname?serverTimezone=UTC

// 方案2：手动处理时区转换
LocalDateTime localDateTime = resultSet.getObject("create_time", LocalDateTime.class);
ZonedDateTime zonedDateTime = localDateTime.atZone(ZoneId.of("UTC"));

四、性能与安全注意事项

1. 避免NULL值

   • MySQL字段设置NOT NULL DEFAULT ...，Java实体类用Optional<T>或合理的默认值。

2. 大文本/二进制字段

   • 避免直接用TEXT/BLOB存储大文件，建议存文件路径到VARCHAR，文件存OSS等存储服务。

3. DECIMAL精度问题

   • 初始化BigDecimal必须用new BigDecimal("10.00")，而非new BigDecimal(10.00)（后者会有精度误差）。

4. 索引字段类型匹配

   • 若MySQL索引字段为VARCHAR，Java查询时应保证参数类型为String（避免类型转换导致索引失效）。

五、总结：最佳实践清单

1. 优先使用Java 8+日期时间API

   • LocalDate → DATE
   • LocalDateTime → DATETIME/TIMESTAMP
   • 避免使用java.util.Date和java.sql.Timestamp（设计缺陷）。

2. 财务计算用BigDecimal

   • 精度和舍入模式明确设置：

     BigDecimal value = new BigDecimal("10.25").setScale(2, RoundingMode.HALF_UP);
     

3. 自定义类型转换器

   • 对特殊类型（如ENUM、JSON），通过ORM框架的转换器机制统一处理。

4. 数据库连接配置

   # 关键连接参数（MySQL 8+）
   spring.datasource.url=jdbc:mysql://localhost:3306/dbname?useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true
   spring.datasource.hikari.connection-timeout=30000
   spring.datasource.hikari.maximum-pool-size=10
   

通过以上方案，你可以在Java开发中与MySQL数据类型实现高效、安全的交互，同时避免常见的坑点。