Appendix C. Operator and Function Reference

This appendix lists all the MySQL functions that you can use in SQL statements. It also lists the operators used to construct expressions. 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.

Unless otherwise indicated, the operators and functions listed have been present in MySQL at least as far back as MySQL 3.21.0. If an operator or function appears to behave differently than described here, check the change notes in the MySQL Reference Manual. It's very possible that old versions of MySQL have bugs that have since been fixed.

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 result 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 out 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 section "Summary Functions" 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 a fixed meaning:

• 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 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 ([]).