Operators

There are several types of operators: for comparison, arithmetic, logical operations, pattern matching, type casting, and bitwise operations. Let's go through these types one at a time.

Comparison Operators

The comparison operators have their syntax as follows:

  • = (equal to)

  • <=> (NULL-safe equal to)

  • != or <> (not equal to)

  • < (less than)

  • <= (less than or equal to)

  • >= (greater than or equal to)

  • > (greater than)

  • expr IS [NOT] NULL (is or is not NULL)

  • expr IN (expr1, expr2, ...) (expression in range of possible values)

  • expr BETWEEN expr_min AND expr_max (expression between two given values)

Comparison operators return 1 if the result of a comparison is true, or 0 if the result is false. Here are a few examples with = (equals):

SELECT 1 = 1 returns 1.

SELECT 1 = "1" returns 1.

SELECT 1 = 1.00000 returns 1.

SELECT 1 = 0 returns 0.

SELECT 1 = 0 returns 1.

But beware! Watch out for these conditions:

SELECT NULL=0 returns NULL.

SELECT NULL=NULL returns NULL.

As you can see, whenever a comparison uses NULL as an operator, you get a NULL result. You might consider the last of the preceding examples to be unsatisfactory. If so, use <=>, the NULL-safe equals operator:

SELECT NULL <=> NULL returns 1.

SELECT NULL <=> 0 returns 0.

The other comparison operators can be used with predictable results with numerical terms, but again beware comparing a NULL. Here are a few examples:

SELECT 1 != 2 returns 1.

SELECT 1 <= 2 returns 1.

SELECT 1 > 2 returns 0.

SELECT 1 > 2 returns 0.

SELECT 1 > NULL returns 0.

When used with strings, an alphabetical comparison is made. To be precise, this is in the order of the ISO-8859-1 Latin1 character set by default:

SELECT "a" > "b" returns 0.

SELECT "a" < "b" returns 1.

SELECT "A" < "b" returns 1.

SELECT "a" < "B" returns 1.

SELECT "abz" < "abc" returns 0.

SELECT "Bertie" < "Bettie" returns 1.

Note

For German language applications, MySQL 4 introduces the latin_de character set, which puts character sorting in the same order as a German language telephone book. To enable this, you should start mysqld with the --default-character-set=latin_de option.

For more information on setting server variables for mysqld, see Day 15, “Administration.”


You can use the comparators to compare dates and times, the least one being the earlier in time. Whether in string or numeric format, the result is the same. For example:

SELECT '2002-06-31' <= '2002-10-09' returns 1.

SELECT 20020631 <= 20021009 returns 1.

But you must make sure that your dates are formatted properly. Beware comparing dates without their leading zeros, or years in two-digit format; the following would both be bad practice:

SELECT '2002-6-31' <= '2002-10-9' returns 0.

SELECT '99-06-31' <= '02-10-09' returns 0.

You can test whether an expression is NULL, using

							expr IS [NOT] NULL

For example, this query would find all people who have an email address in some table:

SELECT * FROM friends WHERE email address IS NOT NULL

You can test whether an expression is in one of a list of values, using

							expr IN (expr1, expr2, ...)

You use IN like this—for example, this would normally be true:

SELECT * FROM wines WHERE color IN ("red", "white", "rose")

or with NOT, such as:

SELECT * FROM students WHERE age NOT IN (21, 22, 29, 30)

You can find out whether an expression is between two values using BETWEEN, which takes the format

							expr BETWEEN expr_min AND expr_max

For example:

SELECT (14.5 * 2) BETWEEN 25 AND 50 returns 1.

Arithmetic Operators

MySQL can do basic arithmetic, with the following operators:

  • x * y (multiplication)

  • x / y (division)

  • x + y (addition)

  • x - y (subtraction)

  • x % y (modulo)

The arithmetic operators expect numbers rather than strings to be passed to them. However, a string containing a numerical value at the beginning of the string will still be processed: Each value is evaluated to a number, which will be non-zero if the string starts with numeric information. Thus:

SELECT 9+2

will return 11 but

SELECT "4.5abc" - 0.5 + "two2"

would be the same as

SELECT 4.5 - 0.5 + 0

and will therefore return 4.

The - (minus) operator can also be used as a unary minus, by putting it to the left of the term. Thus

SELECT 4.5 * - 2

returns -9.0.

The % (modulo) operator works like this:

x % y

x is divided by y, and the remainder is returned. For example:

SELECT 10 % 4

returns 2.

Logical Operators

The logical (or boolean) operators NOT, OR, and AND (with equivalents as !, ||, and &&) have their syntax as follows:

  • NOT expr (not), which is equivalent to

  • ! expr

  • expr1 OR expr2 (or), which is equivalent to

  • expr1 || expr2

  • expr1 AND expr2 (and), which is equivalent to

  • expr1 && expr2

NOT or ! does logical negation of the operand that it precedes, returning 1 for true or 0 for false. It expects the operand to be numeric and will convert a string to a number before evaluating. Note that any negation of NULL also returns NULL. Some examples:

SELECT NOT 1 returns 0.

SELECT NOT 0 returns 1.

SELECT NOT "xyz" returns 1.

SELECT NOT "" returns 1.

SELECT NOT NULL returns NULL.

The logical OR takes two operands and returns 1 if either is true, or 0 otherwise. The logical AND returns 1 if both its operands are true, or 0 otherwise. As with NOT, strings are converted to numbers first. Some examples:

SELECT 1 OR 0 returns 1.

SELECT 1 AND 0 returns 0.

SELECT "a" AND "b" returns 0.

Note the behavior when comparing NULL:

SELECT NULL OR NULL returns NULL.

SELECT NULL OR 1 returns 1.

SELECT NULL AND 1 returns NULL.

SELECT NULL AND "a" returns NULL.

SELECT NULL AND NULL returns NULL.

SELECT 1 AND NULL returns NULL.

SELECT "a" AND NULL returns 0.

Pattern Matching and Cast Operators

MySQL provides a set of pattern matching operators, ranging from the simple LIKE to the powerful regular expression operator REGEXP (or its equivalent RLIKE). Here's the syntax of the operators:

  • string LIKE pattern (simple pattern match)

  • string REGEXP pattern (regular expression comparator), same as

  • string RLIKE pattern

  • BINARY string (interpret string as binary to force case sensitivity)

You can use LIKE to do simple, pattern matching that is not case sensitive. You would typically use LIKE to find whether one pattern contains another, or whether two patterns are similar (perhaps ignoring case). It returns a 1 for true (a match) or 0 for false.

You will find the % wildcard character useful, either before or after the pattern. For example, to find whether a string is equivalent to another except for case:

SELECT "Felix" LIKE "felix" returns 1.

SELECT "Felix Mendelsohn" LIKE "felix" returns 0.

Or to determine whether a string contains that name, at the beginning, end, or anywhere, respectively:

SELECT "Felix Mendelsohn" LIKE "felix%" returns 1.

SELECT "Felix Mendelsohn" LIKE "%felix" returns 0.

SELECT "Felix, John, Bruno" LIKE "%john%" returns 1.

To force a case-sensitive comparison, use the BINARY operator on either string or pattern. This converts either one to binary. For example:

SELECT "Felix, John, Bruno" LIKE BINARY "%john%" returns 0.

You can also use the _ (underscore) operator, which matches any single character. For example:

SELECT "abc" LIKE "ab_" returns 1.

SELECT "abcd" LIKE "ab_" returns 0.

SELECT "abc" LIKE "___" returns 1.

You can use % and _ in combination:

SELECT "abcd" LIKE "a__%" returns 1.

but

SELECT "ab" LIKE "a__%" returns 0.

Caution

Pattern matching operators can be used as powerful text search tools, such as for building a search engine that finds rows in a table that contain a given piece of text. But beware the performance impact it will have because any string comparison will inevitably take a good deal of processor time owing to the multiple comparisons it must make.

If your pattern matching operator looks for a pattern only at the beginning of a string (for example, SELECT * FROM table WHERE text_column LIKE "pattern%”), you can build an index for that column and make your search faster.

But an index will not help if you will be searching for the pattern anywhere in the text (for example, SELECT * FROM table WHERE text_column LIKE "%pattern%"). If you want to run a search for a complete word (or words) occurring anywhere in a text column, consider using a full-text search, as described in Day 8, “Querying Data.”


For more sophisticated pattern matching, you can use the REGEXP operator (or RLIKE, which is the same). REGEXP does extended regular expression matching, returning a 1 for true (if the string contains the regular expression pattern), 0 otherwise, but NULL if either is NULL.

Regular expressions are powerful pattern matching algorithms and are also found in Perl, PHP, and Unix programs such as grep. They go beyond the simple LIKE operator, allowing you to specify, for example, how many instances of a given pattern element are required to qualify as a match.

Table 10.1 shows the regular expression sequences available to you.

Table 10.1. REGEXP Regular Expressions
SequenceMeaning
.(A dot) Matches any single character.
[...]Matches any character or characters that appear between the brackets. For example, [Aa] matches the letter a in upper- or lowercase.
[^...]Matches any character other than those that appear between the brackets. For example, [^0-9] matches any string lacking a non-numeric character.
^Matches at the beginning of the string. For example, ^John matches strings beginning with “John”.
$Matches at the end of the string. For example, Smith$ matches strings ending with “Smith”.
elementMatches element. For example, John matches strings containing “John” anywhere in its length.
element1|element2Matches element1 or element2. For example, a|b|c matches strings containing “a”, “b”, or “c” anywhere.
element*Matches 0 or more instances of element. For example, [0-9]* matches any number of digits (including none).
element+Matches 1 or more instances of element. For example, [0-9]+ matches at least one digit anywhere in the string.
element?Matches 0 or 1 instances of element.
element{n1}Matches exactly n1 instances of element.
element{n1,}Matches at least n1 instances of element.
element{,n2}Matches from 0 to n2 instances of element.
element{n1,n2}Matches from n1 to n2 instances of element.
()Use parentheses to group patterns, with inner patterns to be evaluated before outer patterns.

Here are a few more examples:

SELECT "John Smith" REGEXP "x" returns 0.

SELECT "John Smith" REGEXP "^John" returns 1.

SELECT "John Smith" REGEXP "Smith$" returns 1.

SELECT "John Smith" REGEXP "^Smith" returns 1.

SELECT "John Smith" REGEXP "^john smith$" returns 1.

SELECT "John Smith" REGEXP "[0-9]+" returns 0.

SELECT "John" REGEXP "J...." returns 0.

SELECT "John" REGEXP "J..." returns 1.

SELECT "John" REGEXP "J.." returns 1.

SELECT "John" REGEXP ".o.." returns 1.

Look for a four-letter word where the second letter is “o”:

SELECT "John" REGEXP "^.o..$" returns 1.

SELECT "Johnathan" REGEXP "^.o..$" returns 0.

Look for at least one “J”, first not case sensitive and then case sensitive:

SELECT "John" REGEXP "j{1,}" returns 1.

SELECT "John" REGEXP BINARY "J{1,}" returns 1.

Look for the last name after at least one space:

SELECT "Robert Grant" REGEXP " +Grant$" returns 1.

SELECT "Grant" REGEXP " +Grant$" returns 0.

Bitwise Operators

You can perform operations at a binary level. Here is the syntax of the bitwise operators, where expr should be a BIGINT:

  • expr << num (shift bits of expr left by num bits)

  • expr >> num (shift bits of expr right by num bits)

  • expr1 | expr2 (perform bitwise OR of expr1 and expr2)

  • expr1 & expr2 (perform bitwise AND of expr1 and expr2)

Order of Operator Precedence

Expressions are evaluated strictly in the following order (operators higher up the list are performed first):

BINARY
NOT !
- (unary)
* / %
+ -
<< >>
&
|
= <=> != <> < <= >= > LIKE REGEXP IS IN
BETWEEN
AND &&
OR ||

Thus, for example, SELECT 9+2*5 returns 19 because * is evaluated before +.

You may use parentheses to affect the order of evaluation. For example, SELECT (9+2)*5 returns 55.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.145.204.201