MySQL operators include the familiar operators common to most programming languages, although C-style operators (++,,+=, etc.) are not supported.
Operators are typically used within the SET statement to change the value of a variable, within comparison statements such as IF or CASE, and in loop control expressions. Example 3-10 shows a few simple examples of using operators within stored programs.
Example 3-10. Examples of operators in a stored program
create procedure operators( ) begin DECLARE a int default 2; declare b int default 3; declare c FLOAT; set c=a+b; select 'a+b=',c; SET c=a/b; select 'a/b=',c; SET c=a*b; Select 'a*b=',c; IF (a |
The various types of operators (mathematical , comparison , logical, and bitwise) are described in the following subsections.
3.2.1. Mathematical Operators
MySQL supports the basic mathematical operators you learned about in elementary school (pay attention class!): addition (+), subtraction (-), multiplication (*), and division (/).
In addition, MySQL supports two additional operators related to division: the DIV operator returns only the integer portion of division, while the modulus operator (%) returns only the remainder from a division. Table 3-2 lists, describes, and provides an example of the MySQL mathematical operators.
Operator |
Description |
Example |
---|---|---|
+ |
Addition |
SET var1=2+2; 4 |
- |
Subtraction |
SET var2=3-2; 1 |
* |
Multiplication |
SET var3=3*2; 6 |
/ |
Division |
SET var4=10/3; 3.3333 |
DIV |
Integer division |
SET var5=10 DIV 3; 3 |
% |
Modulus |
SET var6=10%3 ; 1 |
3.2.2. Comparison Operators
Comparison operators compare values and return TRUE, FALSE, or UNKNOWN (usually if one of the values being compared is NULL or UNKNOWN). They are typically used within expressions in IF, CASE, and loop control statements.
Table 3-3 summarizes the MySQL comparison operators .
Operator |
Description |
Example |
Example result |
---|---|---|---|
> |
Is greater than |
1>2 |
False |
< |
Is less than |
2<1 |
False |
<= |
Is less than or equal to |
2<=2 |
True |
>= |
Is greater than or equal to |
3>=2 |
True |
BETWEEN |
Value is between two values |
5 BETWEEN 1 AND 10 |
True |
NOT BETWEEN |
Value is not between two values |
5 NOT BETWEEN 1 AND 10 |
False |
IN |
Value is in a list |
5 IN (1,2,3,4) |
False |
NOT IN |
Value is not in a list |
5 NOT IN (1,2,3,4) |
True |
= |
Is equal to |
2=3 |
False |
<>, != |
Is not equal to |
2<>3 |
False |
<=> |
Null safe equal (returns TRUE if both arguments are Null) |
NULL<=>NULL |
True |
LIKE |
Matches a simple pattern |
"Guy Harrison" LIKE "Guy%" |
True |
REGEXP |
Matches an extended regular expression |
"Guy Harrison" REGEXP "[Gg]reg" |
False |
IS NULL |
Value is NULL |
0 IS NULL |
False |
IS NOT NULL |
Value is not NULL |
0 IS NOT NULL |
True |
3.2.3. Logical Operators
Logical operators operate on the three-valued logic values TRUE, FALSE, and NULL and return a like value. These operators are typically used with comparison operators to create more complex expressions.
For many of the logical operations, if any of the values being compared is NULL, then the result is also NULL. It is extremely important to remember this simple fact when creating logical expressions since, otherwise, subtle bugs can arise in your code.
The AND operator compares two Boolean expressions and returns TRUE only if both of the expressions are true. Table 3-4 shows the possible values generated by the AND function.
AND |
TRUE |
FALSE |
NULL |
---|---|---|---|
TRUE |
TRUE |
FALSE |
NULL |
FALSE |
FALSE |
FALSE |
NULL |
NULL |
NULL |
NULL |
NULL |
The OR operator compares two Boolean expressions and returns TRUE if either of the expressions provided is TRUE (Table 3-5).
OR |
TRUE |
FALSE |
NULL |
---|---|---|---|
TRUE |
TRUE |
TRUE |
TRUE |
FALSE |
TRUE |
FALSE |
NULL |
NULL |
TRUE |
NULL |
NULL |
The XOR operator returns TRUE if eitherbut not bothof the values is TRUE. Table 3-6 shows the possible values for an XOR expression.
XOR |
TRUE |
FALSE |
NULL |
---|---|---|---|
TRUE |
FALSE |
TRUE |
NULL |
FALSE |
TRUE |
FALSE |
NULL |
NULL |
NULL |
NULL |
NULL |
Example 3-11 shows the use of the AND operator to combine multiple comparisons.
Example 3-11. Example of logical operators in practice
CREATE FUNCTION f_title(in_gender CHAR(1), in_age INT, in_marital_status VARCHAR(7)) RETURNS VARCHAR(6) BEGIN DECLARE title VARCHAR(6); IF in_gender='F' AND in_age<16 THEN SET title='Miss'; ELSEIF in_gender='F' AND in_age>=16 AND in_marital_status='Married' THEN SET title='Mrs'; ELSEIF in_gender='F' AND in_age>=16 AND in_marital_status='Single' THEN SET title='Ms'; ELSEIF in_gender='M' AND in_age<16 THEN SET title='Master'; ELSEIF in_gender='M' AND in_age>=16 THEN SET title='Mr'; END IF; RETURN(title); END; |
3.2.4. Bitwise Operators
Bitwise operators perform operations on the underlying binary representation of a variable. Table 3-7 lists the bitwise operators .
Operator |
Use |
---|---|
| |
OR |
& |
AND |
<< |
Shift bits to left |
>> |
Shift bits to right |
~ |
NOT or invert bits |
Bitwise operators are similar to logical operators, except that they perform their operations on each bit within a variable.
For instance, consider the integers 5 (binary 101) and 4 (binary 010). The OR operator sets each bit if either of the bits is set in the inputs; so 5|2=7, because 101|010=111, which is 7 in decimal.
The bitwise AND operator sets a bit only if both the bits are true in the input. So 5&6=7, because 101&110=111, which equals 4.
Part I: Stored Programming Fundamentals
Introduction to MySQL Stored Programs
MySQL Stored Programming Tutorial
Language Fundamentals
Blocks, Conditional Statements, and Iterative Programming
Using SQL in Stored Programming
Error Handling
Part II: Stored Program Construction
Creating and Maintaining Stored Programs
Transaction Management
MySQL Built-in Functions
Stored Functions
Triggers
Part III: Using MySQL Stored Programs in Applications
Using MySQL Stored Programs in Applications
Using MySQL Stored Programs with PHP
Using MySQL Stored Programs with Java
Using MySQL Stored Programs with Perl
Using MySQL Stored Programs with Python
Using MySQL Stored Programs with .NET
Part IV: Optimizing Stored Programs
Stored Program Security
Tuning Stored Programs and Their SQL
Basic SQL Tuning
Advanced SQL Tuning
Optimizing Stored Program Code
Best Practices in MySQL Stored Program Development