Chapter 12 Functions and Operators
Table of Contents
- 12.1 Function and Operator Reference
- 12.2 Type Conversion in Expression Evaluation
- 12.3 Operators
- 12.4 Control Flow Functions
- 12.5 Numeric Functions and Operators
- 12.6 Date and Time Functions
- 12.7 String Functions and Operators
- 12.8 What Calendar Is Used By MySQL?
- 12.9 Full-Text Search Functions
-
- 12.9.1 Natural Language Full-Text Searches
- 12.9.2 Boolean Full-Text Searches
- 12.9.3 Full-Text Searches with Query Expansion
- 12.9.4 Full-Text Stopwords
- 12.9.5 Full-Text Restrictions
- 12.9.6 Fine-Tuning MySQL Full-Text Search
- 12.9.7 Adding a Collation for Full-Text Indexing
- 12.9.8 ngram Full-Text Parser
- 12.9.9 MeCab Full-Text Parser Plugin
- 12.10 Cast Functions and Operators
- 12.11 XML Functions
- 12.12 Bit Functions and Operators
- 12.13 Encryption and Compression Functions
- 12.14 Locking Functions
- 12.15 Information Functions
- 12.16 Spatial Analysis Functions
-
- 12.16.1 Spatial Function Reference
- 12.16.2 Argument Handling by Spatial Functions
- 12.16.3 Functions That Create Geometry Values from WKT Values
- 12.16.4 Functions That Create Geometry Values from WKB Values
- 12.16.5 MySQL-Specific Functions That Create Geometry Values
- 12.16.6 Geometry Format Conversion Functions
- 12.16.7 Geometry Property Functions
- 12.16.8 Spatial Operator Functions
- 12.16.9 Functions That Test Spatial Relations Between Geometry Objects
- 12.16.10 Spatial Geohash Functions
- 12.16.11 Spatial GeoJSON Functions
- 12.16.12 Spatial Convenience Functions
- 12.17 JSON Functions
- 12.18 Functions Used with Global Transaction Identifiers (GTIDs)
- 12.19 MySQL Enterprise Encryption Functions
- 12.20 Aggregate Functions
- 12.21 Miscellaneous Functions
- 12.22 Precision Math
Expressions can be used at several points in SQL statements, such as in the ORDER BY
or HAVING
clauses of SELECT
statements, in the WHERE
clause of a SELECT
, DELETE
, or UPDATE
statement, or in SET
statements. Expressions can be written using literal values, column values, NULL
, built-in functions, stored functions, user-defined functions, and operators. This chapter describes the SQL functions and operators that are permitted for writing expressions in MySQL. Instructions for writing stored functions and user-defined functions are given in Section 23.2, “Using Stored Routines”, and Section 28.4, “Adding Functions to MySQL”. See Section 9.2.5, “Function Name Parsing and Resolution”, for the rules describing how the server interprets references to different kinds of functions.
An expression that contains NULL
always produces a NULL
value unless otherwise indicated in the documentation for a particular function or operator.
By default, there must be no whitespace between a function name and the parenthesis following it. This helps the MySQL parser distinguish between function calls and references to tables or columns that happen to have the same name as a function. However, spaces around function arguments are permitted.
You can tell the MySQL server to accept spaces after function names by starting it with the --sql-mode=IGNORE_SPACE
option. (See Section 5.1.10, “Server SQL Modes”.) Individual client programs can request this behavior by using the CLIENT_IGNORE_SPACE
option for mysql_real_connect()
. In either case, all function names become reserved words.
For the sake of brevity, most examples in this chapter display the output from the mysql program in abbreviated form. Rather than showing examples in this format:
mysql> SELECT MOD(29,9);
+-----------+
| mod(29,9) |
+-----------+
| 2 |
+-----------+
1 rows in set (0.00 sec)
This format is used instead:
mysql> SELECT MOD(29,9);
-> 2