Hibernate 6.6与SQL Server 2019 Unicode配置及存量库迁移问题

UTF-8 with VARCHAR (SQL Server 2019+) #

This is the modern, storage-efficient option since SQL Server 2019 supports UTF-8 in VARCHAR columns when using a UTF-8 collation.

Setup Steps #

1. Collation Configuration: Set your database or individual columns to use a UTF-8 collation (e.g., Latin1_General_100_CI_AS_SC_UTF8). You can set this at database creation time or alter existing columns:

   -- Alter database collation
   ALTER DATABASE your_db COLLATE Latin1_General_100_CI_AS_SC_UTF8;
   -- Or alter a specific column
   ALTER TABLE your_table ALTER COLUMN content VARCHAR(255) COLLATE Latin1_General_100_CI_AS_SC_UTF8;
   

2. Hibernate Dialect: Use the 2019 dialect to enable UTF-8 support:

   hibernate.dialect=org.hibernate.dialect.SQLServer2019Dialect
   

3. JDBC Connection: Disable sending strings as Unicode to ensure Hibernate uses VARCHAR for UTF-8 data. Add this to your JDBC URL or connection properties:

   jdbc.url=jdbc:sqlserver://your-server:1433;databaseName=your-db;sendStringParametersAsUnicode=false
   

   Or via Hibernate properties:

   hibernate.connection.sendStringParametersAsUnicode=false
   

4. Entity Mapping: No special annotations are needed for String fields—@Column(length=X) will map to VARCHAR(X), which now supports UTF-8:

   @Column(length = 255)
   private String content;
   

UTF-16 with NVARCHAR (Backward-Compatible) #

If you need compatibility with older SQL Server versions or prefer the traditional Unicode approach, use NVARCHAR (UTF-16).

Setup Steps #

1. Hibernate Dialect: Use the appropriate dialect for your SQL Server version (e.g., SQLServer2019Dialect):

   hibernate.dialect=org.hibernate.dialect.SQLServer2019Dialect
   

2. JDBC Connection: Keep the default sendStringParametersAsUnicode=true (you don't need to set this explicitly, as it's the default for SQL Server JDBC driver).
3. Entity Mapping: To ensure Hibernate generates NVARCHAR columns, you have two options:
   • Per-column: Explicitly specify columnDefinition:

     @Column(columnDefinition = "NVARCHAR(255)")
     private String content;
     

   • Global: Set a Hibernate property to make all String fields default to NVARCHAR (Hibernate 6+):

     hibernate.hbm2ddl.default_nchar=true
     

Your primary key is a UUID stored as VARCHAR(36)—since UUIDs only use ASCII characters (0-9, a-f, hyphens), converting this column to a Unicode-safe type won't break existing data. Here's a practical migration plan:

Choose Your Unicode Strategy #

Pick one based on your needs:

• UTF-8: Update column/database collation to UTF-8 (keep VARCHAR columns). Better for storage if most data is ASCII.
• UTF-16: Convert VARCHAR columns to NVARCHAR. More compatible with older SQL Server versions.

Primary Key Migration #

For UTF-8 #

• Alter the id column's collation to UTF-8:

  ALTER TABLE your_table ALTER COLUMN id VARCHAR(36) COLLATE Latin1_General_100_CI_AS_SC_UTF8 NOT NULL;
  

• Update your Hibernate config to set sendStringParametersAsUnicode=false (as in the UTF-8 setup above). Your existing entity mapping (@Column(length=36)) works as-is.

For UTF-16 #

• Convert the id column to NVARCHAR(36):

  ALTER TABLE your_table ALTER COLUMN id NVARCHAR(36) NOT NULL;
  

• No changes needed to your entity mapping—Hibernate's @Column(length=36) works with NVARCHAR (length refers to character count, not bytes). Keep sendStringParametersAsUnicode=true (default).

Migrate Other Columns #

• UTF-8: Alter text columns to use UTF-8 collation (ensure column length can handle multilingual text, as UTF-8 uses 1-4 bytes per character).
• UTF-16: Convert VARCHAR columns to NVARCHAR (e.g., VARCHAR(255) → NVARCHAR(255)).

Critical Pre/Post Migration Steps #

1. Backup: Always back up your database before making schema changes.
2. Test: Validate that all existing records (especially UUIDs) are intact after conversion.
3. Application Testing: Insert and retrieve multilingual text to confirm Hibernate handles Unicode correctly.

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