This topic describes the built-in functions of OceanBase Database’s MySQL mode.
Single-row functions
Single-row functions are functions that return only a single row of results. OceanBase Database supports the following single-row functions: datetime functions, string functions, conversion functions, mathematical functions, comparison functions, and process control functions.
Aggregate functions
Aggregate functions perform calculations on a set of values and return a single value. Aggregate functions ignore NULL values. Aggregate functions are often used together with the GROUP BY clause of the SELECT statement.
All aggregate functions are deterministic. Whenever an aggregation function is called with a set of given input values, the same value is returned.
Each aggregate function of OceanBase Database can have only one value expression. For example, COUNT(c1, c2) is unsupported, and COUNT(c1) is supported.
Analytic functions
Analytic functions, also known as window functions in some databases, are similar to aggregate functions in that the computation is always based on a set of rows. The difference is that an aggregate function returns only one row per group, while an analytic function can return multiple rows per group, and each row in the group is the result of the window-based logical computation. Analytic functions can significantly optimize queries that require self joins.
A window is also called a frame. OceanBase Database supports two types of frame semantics: ROWS and RANGE. The former is based on a physical row offset, and the latter is based on a logical value offset.
Encryption functions
Encryption functions are used to encrypt and decrypt data.
Information functions
Information functions return a specified piece of system information.
JSON functions
JSON functions allow you to create, manage, and search for JSON values.
Spatial functions
Spatial functions allow you to process and analyze spatial data. You can use these functions together with geospatial data.
Other functions
These functions are not easy to classify.