Search code examples
mysqlhibernatejpaindexinghql

How to create an index for a 'TEXT' column in a MySQL database by means of JPA/Hibernate

I need to create an index for a 'TEXT' column in a MySQL database. I can't do it using this annotation

@Table(name = "table1", indexes = @Index(name = "ix_table1_column1", columnList = "column1"))

as MySQL can only index the first N characters of the 'TEXT' column, so I get this kind of exception:

BLOB/TEXT column 'column1' used in key specification without a key length

(obviously I don't get this exception if I change the type of the column from 'TEXT' to 'VARCHAR(255)')

I've tried to create an index via a custom HQL query by adding a method to the repository:

@Query("create index ix_table1_column1 on Table1Entity(column1(255))") 
void createIndex();

but with no success, as I get this kind of exceptions:

unexpected token: create near line 1, column 1 [create index create index ix_table1_column1 on Table1Entity(column1(255))]

(looks like there's no 'create index' command in HQL)

However, with the 'TEXT' type of column1, I'm able to create an index via this statement in the database where I just add a character limit to the name of the column:

create index ix_table1_column1 on schema1.table1(column1(255));

Is there a way to do this via an annotation of via an HQL custom query?

Solution

  • Just place the command:

    create index ix_table1_column1 on schema1.table1(column1(255))

    in a .sql file, and then use the standard JPA property jakarta.persistence.schema-generation.create-script-source to tell Hibernate where to find it.