Appendix C. Operator and Function Reference

This appendix lists the operators and functions you can use to construct expressions in SQL statements. Unless otherwise indicated, the operators and functions listed here have been present in MySQL at least as far back as MySQL 3.22.0. If an operator or function appears to act differently than described here, check the change notes in the MySQL Reference Manual to see if its behavior has been modified.

Operator and function examples are written in the following format:

expression                                       result

The expression demonstrates how to use an operator or function, and the result shows the value that results from evaluating the expression. For example

LOWER('ABC')                                       'abc'

This means that the function call LOWER('ABC') produces the string result 'abc'. You can try the examples shown in this appendix for yourself using the mysql program. To try the preceding example, invoke mysql, type in the example expression with SELECT in front of it and a semicolon after it and press Enter:

mysql> SELECT LOWER('ABC'); 
+--------------+
| LOWER('ABC') |
+--------------+
| abc          |
+--------------+

MySQL does not require a SELECT statement to have a FROM clause, which makes it easy to experiment with operators and functions by entering arbitrary expressions in this way. (Some database systems don't let you issue a SELECT without a FROM?an unfortunate restriction.)

Examples include complete SELECT statements for functions that cannot be demonstrated otherwise. The "Summary Functions" section is written that way because those functions make no sense except in reference to a particular table.

Function names, as well as operators that are words, such as BETWEEN, can be specified in any lettercase.

Certain types of function arguments occur repeatedly and are represented by names with the following conventional meanings:

expr represents an expression; depending on the context, this can be a numeric, string, date, or time expression, and can incorporate constants, references to table columns, or other expressions.
str represents a string; it can be a literal string, a reference to a string-valued table column, or an expression that produces a string.
n represents an integer (as do letters near to n in the alphabet).
x represents a floating-point number (as do letters near to x in the alphabet).

Other argument names are used less often and are defined where used. Optional parts of operator or function call sequences are indicated by square brackets ([]).

Evaluating an expression often involves type conversion of the values in that expression. See Chapter 2, "Working with Data in MySQL," for details on the circumstances under which type conversion occurs and the rules that MySQL uses to convert values from one type to another.