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 early as MySQL 4.1.0. Changes made since then are so noted.

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.

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, may 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 may be a numeric, string, or date or time expression, and may incorporate constants, references to table columns, or other expressions.

• str represents a string; it can be a literal string, a reference to a table column that has a string data type, 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. Square brackets ([]) in syntax descriptions indicate optional parts of operator or function call sequences. Evaluation of an expression often involves type conversion of the values in that expression. See Chapter 3, "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.