When the "length of field value is longer than 32 for the [WILDCARD_QUERY] query" exception occurs during wildcard query, you can shorten the query string or use match phrase query.
Problem description
The following exception occurs when I query data by using wildcard query:
length of field value is longer than 32 for the [WILDCARD_QUERY] query
Cause
The string that contains wildcard characters exceeds the limit of 32 characters.
Solutions
Shorten the query string
Shorten the query string to ensure that its length does not exceed 32 characters.
Tokenization-based wildcard query
Search index supports tokenization-based wildcard query to implement more efficient fuzzy queries. When you use tokenization-based wildcard query, the query string length is not limited. However, the original content is limited to a maximum of 1,024 characters. Content that exceeds this limit is automatically truncated, and only the first 1,024 characters are retained.
Dynamically modify the search index schema.
Based on the evaluation of business impact, modify the target column or add a virtual column, set the type to Text, and set the tokenization method to fuzzy tokenization. For more information, see Dynamically modify the schema of a search index and Virtual columns.
Use match phrase query (MatchPhraseQuery).
For more information, see Tokenization-based wildcard query.
SQL wildcard query scenario
When you use SQL statements to query data, if the data table has a search index that includes all data columns involved in the SQL statement, the SQL engine automatically selects this search index for data queries. In this case, when executing SQL wildcard queries (such as the LIKE
operator combined with the wildcard character %
), the query string length is also limited (maximum 32 characters).
You can resolve this issue through the following operations.
Dynamically modify the search index schema.
Based on the evaluation of business impact, modify the target column or add a virtual column, set the type to Text, and set the tokenization method to fuzzy tokenization. For more information, see Dynamically modify the schema of a search index and Virtual columns.
Create a mapping table for the search index.
The following example creates a mapping table named example_table__example_table_index
for the search index example_table_index
of the data table example_table
.
CREATE TABLE `example_table__example_table_index`(
`pk` MEDIUMTEXT,
`attr` MEDIUMTEXT,
`attr_virtual` MEDIUMTEXT #virtual column
)
ENGINE='searchindex',
ENGINE_ATTRIBUTE='{"index_name":"example_table_index","table_name":"example_table"}';
Use match phrase query (TEXT_MATCH_PHRASE) in SQL statements.
The following example queries data in the search index mapping table example_table__example_table_index where the value of the virtual column attr_virtual matches the string "tablestore is super cool!".
select * from example_table__example_table_index where TEXT_MATCH_PHRASE(attr_virtual, "tablestore is super cool!");