Operators

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.

Table 3-2. 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 .

Table 3-3. 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.

Table 3-4. Truth table for AND operator

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).

Table 3-5. Truth table for the OR operator

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.

Table 3-6. Truth table for the XOR operator

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 .

Table 3-7. 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.