This topic lists the system functions that can be directly or conditionally used for function-based indexes.
System functions without requirements on the data type
The following table lists the functions whose expressions can be used for function-based indexes.
| ACOS | ADDTIME | ASCII | ATAN |
| ATAN2 | BIT_COUNT | BIT_LENGTH | CEIL |
| CEILING | CHAR | CHAR_LENGTH | CHARSET |
| COALESCE | COLLATION | CONCAT_WS | CONVERT |
| CONVERT_TZ | COS | CRC32 | DATE_FORMAT |
| DECODE | DEGREES | ELT | ENCODE |
| EXP | EXPORT_SET | FIELD | FIND_IN_SET |
| FLOOR | FORMAT | FORMAT_BYTES | FORMAT_PICO_TIME |
| FROM_BASE64 | FROM_DAYS | GEOMCOLLECTION | GET_FORMAT |
| GREATEST | HEX | HOUR | IFNULL |
| INET6_ATON | INET6_NTOA | INET_ATON | INSERT |
| INSTR | INSTRC | INTERVAL | IP2INT |
| IS_IPV4 | IS_IPV4_COMPAT | IS_IPV4_MAPPED | IS_IPV6 |
| IS_UUID | LEAST | LEFT | LENGTH |
| LENGTHC | LINESTRING | LN | LOCATE |
| LOG | LOG10 | LOG2 | LOWER |
| LPAD | LTRIM | MAKEDATE | MAKETIME |
| MID | MINUTE | MONTH | MULTIPOINT |
| MULTILINESTRING | MULTIPOLYGON | NVL | NULLIF |
| ORD | PERIOD_ADD | PERIOD_DIFF | PI |
| POINT | POLYGON | POSITION | POWER |
| RADIANS | REPLACE | REPEAT | REVERSE |
| RIGHT | ROUND | RPAD | RTRIM |
| SECOND | SEC_TO_TIME | SHA | SHA2 |
| SIN | SOUNDEX | SPACE | ST_AREA |
| ST_ASBINARY | ST_ASWKB | ST_ASWKT | ST_ASTEXT |
| ST_BUFFER | ST_BUFFER_STRATEGY | ST_CONTAINS | ST_DISTANCE |
| ST_DISTANCE_SPHERE | ST_INTERSECTS | ST_ISVALID | ST_LATITUDE |
| ST_LONGITUDE | ST_TRANSFORM | ST_WITHIN | ST_X |
| ST_Y | STRCMP | STR_TO_DATE | SUBSTR |
| SUBSTRING_INDEX | SUBTIME | SYSDATE | SYSTEM_USER |
| TAN | TIME | TIME_FORMAT | TIME_TO_SEC |
| TIMEDIFF | TO_BASE64 | TO_SECONDS | TRIM |
| TRUNCATE | UNCOMPRESS | UNCOMPRESSED_LENGTH | UNHEX |
| UPPER | UUID_TO_BIN | VALIDATE_PASSWORD_STRENGTH |
System functions with requirements on the data type
The following system functions are prohibited in function-based indexes when the data type is TIME. For example, the DATE function is prohibited in a function-based index when the value of the first parameter is of the TIME data type. The DATEDIFF function is prohibited in a function-based index when the value of any parameter is of the TIME data type.
| ADDDATE | DAY | DAYNAME | DAYOFMONTH |
| DAYOFWEEK | DAYOFYEAR | DATEDIFF | DATE |
| DATE_SUB | LAST_DAY | MONTH | MONTHNAME |
| QUARTER | TIMESTAMP | TIMESTAMPADD | TIMESTAMPDIFF |
| TIMESTAMP_NVL | TO_DAYS | WEEK | WEEKDAY |
| WEEKOFYEAR | YEAR | YEARWEEK |
JSON functions
Expressions of the following JSON functions can be used in function-based indexes.
| 1 MEMBER OF | JSON_ARRAY | JSON_ARRAY_APPEND | JSON_ARRAY_INSERT |
| JSON_CONTAINS | JSON_CONTAINS_PATH | JSON_DEPTH | JSON_EXTRACT |
| JSON_INSERT | JSON_KEYS | JSON_LENGTH | JSON_MERGE |
| JSON_MERGE_PATCH | JSON_MERGE_PRESERVE | JSON_OBJECT | JSON_OVERLAPS |
| JSON_PRETTY | JSON_QUERY | JSON_QUOTE | JSON_REMOVE |
| JSON_REPLACE | JSON_SEARCH | JSON_SET | JSON_STORAGE_FREE |
| JSON_STORAGE_SIZE | JSON_TYPE | JSON_UNQUOTE | JSON_VALID |
| JSON_VALUE | - | - | - |
At present, the following functions can be used only in predicates.
| JSON_EQUAL | JSON_EXISTS |
CAST expressions
When the target or source type of a CAST expression is a time type, the CAST expression cannot be used in function-based indexes. The following table lists the detailed scenarios.
| Source type | Target type | Description |
|---|---|---|
| TIME | DATETIME, TIMESTAMP, or DATE | Prohibited in function-based indexes |
| DATETIME, TIMESTAMP, or DATE | TIMESTAMP WITH TIME ZONE or TIMESTAMP WITH LOCAL TIME ZONE | Prohibited in function-based indexes |