Spring JPA 2.1存储HTML至MySQL：TEXT/BLOB选型与DisplaySize疑问

Question 1: What's the Origin of DisplaySize, and Why Doesn't @Column(length) Affect It? #

Let’s break this down clearly:

1. What is DisplaySize?
   DisplaySize is a metadata value pulled from your MySQL JDBC driver via ResultSetMetaData.getColumnDisplaySize(). It reflects the driver’s understanding of the column type’s maximum possible length (characters for text types, bytes for binary types). This is not a physical storage allocation—it’s just the driver reporting the inherent capacity of the column type.

2. Why TEXT has a huge value, BLOB is -1?

   • For your TEXT column (mapped with @Lob + String): JPA 2.1’s @Lob for String defaults to a CLOB type, which in MySQL maps to LONGTEXT (max 4.2 billion characters). The large number you’re seeing (1431655765) is likely a driver-specific quirk—possibly an older driver’s way of reporting the LONGTEXT limit within 32-bit signed integer constraints.
   • For BLOB: Binary types don’t have a "display size" in the text sense. Since BLOB stores raw bytes, the driver returns -1 to indicate there’s no fixed character length associated with the column.

3. Why does @Column(length=5000) not work?
   When you use @Lob, JPA ignores the length attribute of @Column. The @Lob annotation takes priority, telling Hibernate to use a large object type (CLOB for String, BLOB for byte[]) with MySQL’s default LOB sizes. If you want to use MySQL’s smaller TEXT type instead of LONGTEXT, explicitly define the column type:

   @Basic(fetch = FetchType.LAZY)
   @Column(columnDefinition = "TEXT", length = 5000)
   private String projectDescription = "";
   

   Note: Even with length=5000, the driver may still report TEXT’s inherent max (65535) as DisplaySize—this metadata doesn’t reflect your custom length constraint, which only validates the data you persist.

Question 2: Is Storing HTML as String (TEXT) Feasible Instead of BLOB? #

Absolutely—this is the right approach for HTML content. Here’s why:

• HTML is text data: HTML is plain text with markup, so storing it as TEXT/CLOB aligns with its nature. Using BLOB would require encoding (like Base64) which adds unnecessary overhead (increasing storage by ~33%) and complicates reading/writing the data.
• DisplaySize isn’t a waste of space: The large DisplaySize value is just metadata, not allocated storage. MySQL uses dynamic storage for TEXT types—only the actual length of your HTML content is stored on disk, plus minimal overhead.
• Practical advantages: Storing as String makes it easier to query (e.g., search for specific markup), edit, and render directly without decoding. BLOBs are intended for binary data like images, PDFs, or non-text files.

If your HTML exceeds 65535 characters, switch to MEDIUMTEXT (max 16MB) or LONGTEXT (max 4GB) by updating the columnDefinition.

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