MySQL/Language/Operators

MySQL uses some standard SQL operators and some non-standard operators. They can be used to write expressions which involve constant values, variables, values contained in fields and / or other expressions.

Equality
If you want to check if 2 values are equal, you must use the = operator:

If you want to check if 2 values are different, you can use the <> or != operators, which have the same meaning:

<> return 1 where = returns 0 and vice versa.

IS and NULL-safe comparison
When you compare a NULL value with a non-NULL value, you'll get NULL. If you want to check if a value is null, you can use IS:

You can check if a value is non-NULL:

There is also an equality operator which considers NULL as a normal value, so it returns 1 (not NULL) if both values are NULL and returns 0 (not NULL) if one of the values is NULL:

There is not a NULL-safe non-equality operator, but you can type the following:

IS and Boolean comparisons
IS and IS NOT can also be used for Boolean comparisons. You can use them with the reserved words TRUE, FALSE and UNKNOWN (which is merely a synonym for NULL).

Greater, Less...
You can check if a value is greater than another value:

You can also check if a value is minor than another value:

This kind of comparisons also works on TEXT values:

Generally speaking, alphabetical order is used for TEXT comparisons. However, the exact rules are defined by the COLLATION used. A COLLATION defines the sorting rules for a given CHARACTER SET. For example, a COLLATION may be case-sensitive, while another COLLATION may be case-insensitive.

You can check if a value is equal or greater than another value. For example, the following queries have the same meaning:

Similarly, you can check if a value is less or equal to another value:

BETWEEN
If you want to check if a value is included in a given range (boundaries included), you can use the BETWEEN ... AND ... operator. AND doesn't have its usual meaning. Example:

The value after BETWEEN and the value after AND are included in the range.

You can also use NOT BETWEEN to check if a value is not included in a range:

IN
You can use the IN operator to check if a value is included in a list of values:

You should not include in the list both numbers and strings, or the results may be unpredictable. If you have numbers, you should quote them:

There is not a theoretical limit to the number of values included in the IN operator.

You can also use NOT IN:

MySQL Boolean logic
MySQL doesn't have a real BOOLEAN datatype.

FALSE is a synonym for 0. Empty strings are considered as FALSE in a Boolean context.

TRUE is a synonym for 1. All non-NULL and non-FALSE data are considered as TRUE in a boolean context.

UNKNOWN is a synonym for NULL. The special date 0/0/0 is NULL.

NOT
NOT is the only operator which has only one operand. It returns 0 if the operand is TRUE, returns 1 if the operand is FALSE and returns NULL if the operand is NULL.

! is a synonym for NOT.

AND
AND returns 1 if both the operands are TRUE, else returns 0; if at least one of the operands is NULL, returns NULL.

&& is a synonym for AND.

OR
OR returns TRUE if at least one of the operands is TRUE, else returns FALSE; if the two operands are NULL, returns NULL.


 * is a synonym for OR.

XOR
XOR (eXclusive OR) returns 1 if only one of the operands is TRUE and the other operand is FALSE; returns 0 if both the operands are TRUE o both the operands are FALSE; returns NULL if one of the operands is NULL.

Synonyms

 * AND can be written as &&
 * OR can be written ad ||
 * NOT can be written as !

Only NOT (usually) has a different precedence from its synonym. See operator precedence for detail.

Arithmetic operators
MySQL supports operands which perform all basic arithmetic operations.

You can type positive values with a '+', if you want:

You can type negative values with a '-'. - is an inversion operand:

You can make sums with '+':

You can make subtractions with '-':

You can multiply a number with '*':

You can make divisions with '/'. Returns a FLOAT number:

You can make integer divisions with DIV. Resulting number is an INTEGER. No remainder. This has been added in MySQL 4.1.

You can get the remainder of a division with '%' or MOD:

Using + to cast data
You can convert an INTEGER to a FLOAT doing so:

You can't convert a string to a FLOAT value by adding 0.0, but you can cast it to an INTEGER:

Text operators
There are no concatenation operators in MySQL.

Arithmetic operators convert the values into numbers and then perform arithmetic operations, so you can't use + to concatenate strings.

You can use the CONCAT function instead.

LIKE
The LIKE operator may be used to check if a string matches to a pattern. A simple example:

The pattern matching is usually case insensitive. There are two exceptions:


 * when a LIKE comparison is performed against a column which has been declared with the BINARY flag (see CREATE TABLE);
 * when the expression contains the BINARY clause:

You can use two special characters for LIKE comparisons:
 * _ means "any character" (but must be 1 char, not 0 or 2)
 * % means "any sequence of chars" (even 0 chars or 1000 chars)

Note that "\" also escapes quotes ("'") and this behaviour can't be changed by the ESCAPE clause. Also, the escape character does not escape itself.

Common uses of LIKE:


 * Find titles starting with the word "hello":


 * Find titles ending with the word "world":


 * Find titles containing the word "gnu":

These special chars may be contained in the pattern itself: for example, you could need to search for the "_" character. In that case, you need to "escape" the char: Sometimes, you may want to use an escape character different from "\". For example, you could use "/":

When you use = operator, trailing spaces are ignored. When you use LIKE, they are taken into account.

LIKE also works with numbers.

If you want to check if a pattern doesn't match, you can use NOT LIKE:

SOUNDS LIKE
You can use SOUNDS LIKE to check if 2 text values are pronounced in the same way. SOUNDS LIKE uses the SOUNDEX algorithm, which is based on English rules and is very approximate (but simple and thus fast).

SOUNDS LIKE is a MySQL-specific extension to SQL. It has been added in MySQL 4.1.

Regular expressions
You can use REGEXP to check if a string matches to a pattern using regular expressions.

You can use RLIKE as a synonym for REGEXP.

Bitwise operators
Bit-NOT:

Bit-AND:

Bit-OR:

Bit-XOR:

Left shift:

Right shift:

IF
The structure  only functions in the stored procedures. To manage a condition out of them, we can use :.

Example:  returns 0.

Example with several conditions (switch) :

CASE
Example:  renvoie 0.

Example with several conditions :

In one request:

Operator precedence
Table of operator precedence:

Modifiers:
 * PIPES_AS_CONCAT - If this SQL mode is enabled, || has precedence on ^, but - and ~ have precedence on ||.
 * HIGH_NOT_PRECEDENCE - If this SQL mode is enabled, NOT has the same precedence level as !.

Use of parenthesis
You can use parenthesis to force MySQL to evaluate a subexpression before another independently from operator precedence:

You can also use parenthesis to make an expression more readable by humans, even if they don't affect the precedence:

Assignment operators
You can use the = operator to assign a value to a column:

When you want to assign a value to a variable, you must use the := operator, because the use of = would be ambiguous (is it as assignment or a comparison?)

You can also use SELECT INTO to assign values to one or more variables.

MySQL/Opérateurs