Appendix B: MySQL Function and Operator Reference

MySQL has many useful operators and functions. The operators are one of the basics of MySQL, and you won't get far without them. There are many obscure functions that you'll probably never use, but it's worth going through the list because you may find some you will use and some you will store in the back of your mind for later.

Logical Operators

Logical operators, or Boolean operators, check whether something is true or false. They return 0 if the expression is false and 1 if it's true. Null values are handled differently depending on the operator. Usually they return a NULL result.

AND, &&

value1 AND value1 value1 && value2

Returns true (1) if both values are true.

For example:

mysql> SELECT 1 AND 0; +---------+ | 1 AND 0 | +---------+ |       0 | +---------+ mysql> SELECT 1=1 && 2=2; +------------+ | 1=1 && 2=2 | +------------+ |          1 | +------------+ 

OR, ||

value1 OR value2 value1 || value 2

Returns true (1) if either value1 or value2 is true.

For example:

mysql> SELECT 1 OR 1; +--------+ | 1 OR 1 | +--------+ |      1 | +--------+ mysql> SELECT 1=2 || 2=3; +------------+ | 1=2 || 2=3 | +------------+ |          0 | +------------+

NOT, !

NOT value1 ! value1

Returns the opposite of value1, which is true if value1 is false and false if value1 is true.

For example:

mysql> SELECT !1; +----+ | !1 | +----+ |  0 | +----+ mysql> SELECT NOT(1=2); +----------+ | NOT(1=2) | +----------+ |        1 | +----------+