MySQL :: MySQL 8.0 Reference Manual :: 14 Functions and Operators (original) (raw)

Expressions can be used at several points inSQL statements, such as in theORDER BY or HAVING clauses ofSELECT statements, in theWHERE clause of aSELECT,DELETE, orUPDATE statement, or inSET 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, seeSection 7.7, “MySQL Server Loadable Functions”, andSection 27.2, “Using Stored Routines”. For the rules describing how the server interprets references to different kinds of functions, seeSection 11.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.

Note

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 7.1.11, “Server SQL Modes”.) Individual client programs can request this behavior by using theCLIENT_IGNORE_SPACE option formysql_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