MySQL in a Nutshell (2008)
Part V. Appendixes
Appendix B. Operators
Operators are used in mathematical or logical operations. An operator is typically placed between two values (i.e., numbers, strings, columns, or expressions) that you want to compare or evaluate. There are four types of operators: arithmetic, relational, logical, and bitwise. This appendix provides a listing of operators grouped into these four types. This appendix also includes a list of special pattern-matching characters and constructs for regular expressions.
Arithmetic Operators
The arithmetic operators in MySQL work only on numbers, not on strings. However, MySQL will convert a string into a number when in a numeric context if it can. If it can’t convert a particular string, it will return 0. Table B-1 lists the arithmetic operators allowed.
The minus sign may be used for subtracting numbers or for setting a number to a negative. The DIV operator converts values to integers and returns only integers. It doesn’t round fractions that would be returned, but rather truncates them.
Table B-1. Arithmetic operators
Operator |
Use |
+ |
Addition |
– |
Subtraction and negation |
* |
Multiplication |
/ |
Division |
DIV |
Division of integers |
% |
Modulo division |
Relational Operators
Relational operators are used for comparing numbers and strings. If a string is compared to a number, MySQL will try to convert the string to a number. If a TIMESTAMP column is compared to a string or a number, MySQL will attempt to convert the string or number to a timestamp value. If it’s unsuccessful at converting the other value to a timestamp, it will convert the TIMESTAMP column’s value to a string or a number. TIME and DATE columns are compared to other values as strings. Table B-2 lists the logical and relational operators allowed in MySQL.
The minus sign may be used for subtracting numbers or for setting a number to a negative. The equals sign is used to compare two values. If one value is NULL, though, NULL will be returned. The <=> operator is used to compare values for equality; it’s NULL-safe. For example, an SQL statement containing something like IF(col1 <=> col2), where the values of both are NULL, will return 1 and not NULL.
Table B-2. Relational operators
Operator |
Use |
< |
Less than |
> |
Greater than |
<= |
Less than or equal to |
>= |
Greater than or equal to |
expression BETWEEN n AND n |
Between first and second number |
expression NOT BETWEEN n AND n |
Not between first and second number |
IN (...) |
In a set |
NOT IN (...) |
Not in a set |
= |
Equal to |
<=> |
Equal to (for comparing NULL values) |
LIKE |
Matches a pattern |
SOUNDS LIKE |
Matches a sound pattern (see SOUNDEX() function described in Chapter 11) |
NOT LIKE |
Doesn’t match a pattern |
REGEXP, RLIKE |
Matches a regular expression |
NOT REGEXP |
Doesn’t match a regular expression |
!= |
Not equal to |
<> |
Not equal to |
NOT, ! |
Negates |
IS NULL |
NULL |
IS NOT NULL |
Not NULL |
Logical Operators
Logical operators are used for evaluating values or expressions for true, false, or unknown. Table B-3 lists allowable logical operators.
The operators IS and IS NOT are added in version 5.0.2 of MySQL. A boolean value of TRUE, FALSE, or UNKNOWN should immediately follow these operators.
Table B-3. Logical operators
Operator |
Use |
AND |
Logical AND |
&& |
Logical AND |
IS boolean |
Logical equal |
IS NOT boolean |
Logical equal |
OR |
Logical OR |
|| |
Logical OR |
NOT |
Logical NOT |
! |
Logical NOT |
XOR |
Logical XOR |
Bitwise Operators
Bitwise operators are used for comparing numbers based on their binary digits. These operators are listed in Table B-4.
The tilde (~) may be used to invert the bits of a value.
Table B-4. Bitwise operators
Operator |
Use |
| |
OR |
^ |
XOR |
& |
AND |
<< |
Shift bits to left |
>> |
Shift bits to right |
~ |
NOT or invert bits |
Regular Expressions
When using the operators REGEXP, RLIKE, and NOT REGEXP, you may need special characters and parameters to be able to search for data based on regular expressions. Table B-5 lists the special characters, and Table B-6 shows special constructs that may be used. In keeping with convention, patterns to match are given within quotes. As an example of a regular expression used with a SELECT statement, suppose that we want to find the name of a particular student in a college’s database, but we can’t quite remember his last name. All we remember is that it’s something like Smith, but it could be Smithfield or maybe Smyth. We could run an SQL statement like the following to get a list of possibilities:
SELECT student_id,
CONCAT(name_first, SPACE(1), name_last) AS Student
FROM students
WHERE name_last REGEXP 'Smith.*|Smyth';
As an example using a pattern-matching construct, suppose that we suspect there are a few student records in which the name columns contain numeric characters. Suppose also that there are some student records in which the social_security column contains characters other than numbers or dashes. We could search for them by executing an SQL statement like the following:
SELECT student_id, soc_sec,
CONCAT(name_first, SPACE(1), name_last) AS Student
FROM students
WHERE CONCAT(name_first, name_last) REGEXP '[[:digit:]]+'
OR soc_sec REGEXP '[[:alpha:]]+';
As an example of a construct using a character name, suppose that the column containing Social Security tax identification numbers (i.e., soc_sec) shouldn’t contain the usual hyphen separator (i.e., 443-78-8391). We could enter an SQL statement like the following to find records with hyphens in that column:
SELECT student_id, soc_sec,
CONCAT(name_first, SPACE(1), name_last) AS Student
FROM students
WHERE soc_sec REGEXP '[[.hyphen.]]+';
To find any rows that do not specifically meet the format for the Social Security number (i.e., nnn-nn-nnnn), we could use this longer but more specific regular expression:
SELECT student_id, soc_sec,
CONCAT(name_first, SPACE(1), name_last) AS Student
FROM students
WHERE soc_sec NOT REGEXP
'[[:digit:]]{3}[[.hyphen.]]{1}[[:digit:]]{2}[[.hyphen.]]{1}[[:digit:]]{4}';
Notice that this statement uses the curly braces after each construct to specify the exact number of characters or digits permitted.
Table B-5. Pattern-matching characters
Character |
Use |
^ |
Matches the beginning of the string. |
$ |
Matches the beginning of the string. |
. |
Matches any character, space, or line ending. |
* |
Matches zero or more of the characters immediately preceding. |
+ |
Matches one or more of the characters immediately preceding. |
? |
Matches zero or one of the characters immediately preceding. |
| |
An OR operator; matches the characters before or after it (e.g., 'Russell|Rusty'). |
(characters)* |
Matches zero or more occurrences of the sequence of characters given in parentheses. |
{number} |
Specifies the number of occurrences of the previous pattern given. |
{number,number} |
Specifies the minimum number of occurrences of the previous pattern given, followed by the maximum number of occurrences. If only the minimum number is omitted, 0 is assumed. If just the maximum number is omitted, unlimited is assumed. |
[x-x] |
Specifies a range of characters in alphabetical order (e.g., '[a-g]' for the first seven lowercase letters), or numbers in numeric sequence (e.g., '[0-9]' for all numbers). |
Table B-6. Pattern-matching constructs
Construct |
Use |
[.character.] |
Matches the given character or character name (e.g., backslash, carriage return, newline, tab). |
[=character=] |
Matches characters of the same class as the character given. |
[[:<:]] |
Matches the beginning of a word. |
[[:>:]] |
Matches the end of a word. |
[:alnum:] |
Matches alphanumeric characters. |
[:alpha:] |
Matches alphabetical characters. |
[:blank:] |
Matches a blank or whitespace characters. |
[:cntrl:] |
Matches control characters. |
[:digit:] |
Matches digits. |
[:lower:] |
Matches lowercase alphabetical characters. |
[:print:] |
Matches graphic and space characters. |
[:punct:] |
Matches punctuation characters. |
[:space:] |
Matches space, carriage return, newline, and tab characters. |
[:upper:] |
Matches uppercase alphabetical characters. |
[:xdigit:] |
Matches hexadecimal characters. |