Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 35.0Mb
PDF (A4) - 35.1Mb
Man Pages (TGZ) - 255.5Kb
Man Pages (Zip) - 360.4Kb
Info (Gzip) - 3.4Mb
Info (Zip) - 3.4Mb
Excerpts from this Manual

MySQL 5.7 Reference Manual  /  Functions and Operators

Chapter 12 Functions and Operators

Table of Contents

12.1 Built-In Function and Operator Reference
12.2 Loadable Function Reference
12.3 Type Conversion in Expression Evaluation
12.4 Operators
12.4.1 Operator Precedence
12.4.2 Comparison Functions and Operators
12.4.3 Logical Operators
12.4.4 Assignment Operators
12.5 Flow Control Functions
12.6 Numeric Functions and Operators
12.6.1 Arithmetic Operators
12.6.2 Mathematical Functions
12.7 Date and Time Functions
12.8 String Functions and Operators
12.8.1 String Comparison Functions and Operators
12.8.2 Regular Expressions
12.8.3 Character Set and Collation of Function Results
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 User-Defined 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.17.1 JSON Function Reference
12.17.2 Functions That Create JSON Values
12.17.3 Functions That Search JSON Values
12.17.4 Functions That Modify JSON Values
12.17.5 Functions That Return JSON Value Attributes
12.17.6 JSON Utility Functions
12.18 Functions Used with Global Transaction Identifiers (GTIDs)
12.19 Aggregate Functions
12.19.1 Aggregate Function Descriptions
12.19.2 GROUP BY Modifiers
12.19.3 MySQL Handling of GROUP BY
12.19.4 Detection of Functional Dependence
12.20 Miscellaneous Functions
12.21 Precision Math
12.21.1 Types of Numeric Values
12.21.2 DECIMAL Data Type Characteristics
12.21.3 Expression Handling
12.21.4 Rounding Behavior
12.21.5 Precision Math Examples

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 values from several sources, such as literal values, column values, NULL, variables, built-in functions and operators, loadable functions, and stored functions (a type of stored object).

This chapter describes the built-in functions and operators that are permitted for writing expressions in MySQL. For information about loadable functions and stored functions, see Section 5.6, “MySQL Server Loadable Functions”, and Section 23.2, “Using Stored Routines”. For the rules describing how the server interprets references to different kinds of functions, see Section 9.2.5, “Function Name Parsing and Resolution”.

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.

To 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, some 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