Spring Boot重构SMS网关：MySQL tinyint(3)映射异常求助

Let’s break this down and fix it step by step—your error boils down to a core mismatch between your enum values and MySQL’s tinyint limits, and we’ll resolve it with practical, sustainable solutions.

1. Why the "Out of Range" Error Happens #

First, let’s clarify MySQL’s tinyint constraints:

• A signed tinyint(3) only holds values from -128 to 127
• An unsigned tinyint(3) extends this to 0 to 255

Your SmsMessageStatusType.FAILED has a value of 400, which is way outside both ranges. That’s why switching to unsigned didn’t help—255 is still far smaller than 400. The 100 value worked because it fits within 0-255, but higher values like 300 and 400 will always fail with tinyint.

2. Fix the Database Column Type #

The most straightforward and long-term fix is to update your delivery_status column to a type that can accommodate all your enum values. The best options are:

• smallint: Covers -32768 to 32767 (more than enough for your 0-400 range)
• int: A larger range, but smallint is more efficient for your use case

Run this SQL command to alter the column:

ALTER TABLE m_outbound_messages MODIFY COLUMN delivery_status SMALLINT NOT NULL DEFAULT 0;

3. Proper Java Entity Mapping #

Once the database column is fixed, you can map it cleanly in your JPA entity. The most elegant approach is to use a JPA Attribute Converter, which lets you directly use the SmsMessageStatusType enum in your entity instead of manually handling Integer values.

Step 1: Create the Converter Class #

This class handles bidirectional conversion between the enum and the database integer:

import javax.persistence.AttributeConverter;
import javax.persistence.Converter;

@Converter(autoApply = true) // Auto-apply to all uses of SmsMessageStatusType
public class SmsMessageStatusTypeConverter implements AttributeConverter<SmsMessageStatusType, Integer> {

    @Override
    public Integer convertToDatabaseColumn(SmsMessageStatusType status) {
        if (status == null) {
            return SmsMessageStatusType.INVALID.getValue(); // Fallback to default
        }
        return status.getValue();
    }

    @Override
    public SmsMessageStatusType convertToEntityAttribute(Integer dbValue) {
        if (dbValue == null) {
            return SmsMessageStatusType.INVALID;
        }
        // Match database value to enum
        for (SmsMessageStatusType status : SmsMessageStatusType.values()) {
            if (status.getValue().equals(dbValue)) {
                return status;
            }
        }
        // Return invalid if no match is found
        return SmsMessageStatusType.INVALID;
    }
}

Step 2: Update Your Entity #

Replace the Integer field with the enum directly in your entity:

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Table;

@Entity
@Table(name = "m_outbound_messages")
public class OutboundMessage {

    // Other entity fields...

    @Column(name = "delivery_status", nullable = false)
    private SmsMessageStatusType deliveryStatus;

    // Getters and setters...
}

4. If You Can’t Alter the Database (Not Recommended) #

If for legacy reasons you can’t change the column type, you’d have to adjust your enum values to fit within 0-255 (for unsigned tinyint)—for example, changing FAILED from 400 to 250. However, this is a band-aid that risks breaking existing logic or future expansions, so it’s strongly discouraged.

内容的提问来源于stack exchange，提问作者Usman Khaliq