Chapter 12. Functions and Operators

Daftar Isi

Expressions can be used at several points in SQL statements, such as in the ORDER BY or HAVING clauses of SELECT statements, in the WHERE clause of a SELECT, DELETE, or UPDATE statement, or in SET statements. Expressions can be written using literal values, column values, NULL, built-in functions, stored functions, user-defined functions, and operators. This chapter describes the functions and operators that are permitted for writing expressions in MySQL. Instructions for writing stored functions and user-defined functions are given in Section 19.2, "Using Stored Routines (Procedures and Functions)", and Section 23.3, "Adding New Functions to MySQL". See Section 9.2.4, "Function Name Parsing and Resolution", for the rules describing how the server interprets references to different kinds of functions.

An expression that contains NULL always produces a NULL value unless otherwise indicated in the documentation for a particular function or operator.

Note

By default, there must be no whitespace between a function name and the parenthesis following it. This helps the MySQL parser distinguish between function calls and references to tables or columns that happen to have the same name as a function. However, spaces around function arguments are permitted.

You can tell the MySQL server to accept spaces after function names by starting it with the --sql-mode=IGNORE_SPACE option. (See Section 5.1.7, "Server SQL Modes".) Individual client programs can request this behavior by using the CLIENT_IGNORE_SPACE option for mysql_real_connect(). In either case, all function names become reserved words.

For the sake of brevity, most examples in this chapter display the output from the mysql program in abbreviated form. Rather than showing examples in this format:

mysql> SELECT MOD(29,9);+-----------+| mod(29,9) |+-----------+| 2 |+-----------+1 rows in set (0.00 sec)

This format is used instead:

mysql> SELECT MOD(29,9); -> 2

12.1. Function and Operator Reference

Table 12.1. Functions/Operators

Name	Description
ABS()	Return the absolute value
ACOS()	Return the arc cosine
ADDDATE()	Add time values (intervals) to a date value
ADDTIME()	Add time
AES_DECRYPT()	Decrypt using AES
AES_ENCRYPT()	Encrypt using AES
AND, &&	Logical AND
ASCII()	Return numeric value of left-most character
ASIN()	Return the arc sine
=	Assign a value (as part of a SET statement, or as part of the SET clause in an UPDATE statement)
:=	Assign a value
ATAN2(), ATAN()	Return the arc tangent of the two arguments
ATAN()	Return the arc tangent
AVG()	Return the average value of the argument
BENCHMARK()	Repeatedly execute an expression
BETWEEN ... AND ...	Check whether a value is within a range of values
BIN()	Return a string containing binary representation of a number
BINARY	Cast a string to a binary string
BIT_AND()	Return bitwise and
BIT_COUNT()	Return the number of bits that are set
BIT_LENGTH()	Return length of argument in bits
BIT_OR()	Return bitwise or
BIT_XOR()	Return bitwise xor
&	Bitwise AND
~	Invert bits
\|	Bitwise OR
^	Bitwise XOR
CASE	Case operator
CAST()	Cast a value as a certain type
CEIL()	Return the smallest integer value not less than the argument
CEILING()	Return the smallest integer value not less than the argument
CHAR_LENGTH()	Return number of characters in argument
CHAR()	Return the character for each integer passed
CHARACTER_LENGTH()	A synonym for CHAR_LENGTH()
CHARSET()	Return the character set of the argument
COALESCE()	Return the first non-NULL argument
COERCIBILITY()	Return the collation coercibility value of the string argument
COLLATION()	Return the collation of the string argument
COMPRESS()	Return result as a binary string
CONCAT_WS()	Return concatenate with separator
CONCAT()	Return concatenated string
CONNECTION_ID()	Return the connection ID (thread ID) for the connection
CONV()	Convert numbers between different number bases
CONVERT_TZ()	Convert from one timezone to another
CONVERT()	Cast a value as a certain type
COS()	Return the cosine
COT()	Return the cotangent
COUNT(DISTINCT)	Return the count of a number of different values
COUNT()	Return a count of the number of rows returned
CRC32()	Compute a cyclic redundancy check value
CURDATE()	Return the current date
CURRENT_DATE(), CURRENT_DATE	Synonyms for CURDATE()
CURRENT_TIME(), CURRENT_TIME	Synonyms for CURTIME()
CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP	Synonyms for NOW()
CURRENT_USER(), CURRENT_USER	The authenticated user name and host name
CURTIME()	Return the current time
DATABASE()	Return the default (current) database name
DATE_ADD()	Add time values (intervals) to a date value
DATE_FORMAT()	Format date as specified
DATE_SUB()	Subtract a time value (interval) from a date
DATE()	Extract the date part of a date or datetime expression
DATEDIFF()	Subtract two dates
DAY()	Synonym for DAYOFMONTH()
DAYNAME()	Return the name of the weekday
DAYOFMONTH()	Return the day of the month (0-31)
DAYOFWEEK()	Return the weekday index of the argument
DAYOFYEAR()	Return the day of the year (1-366)
DECODE()	Decodes a string encrypted using ENCODE()
DEFAULT()	Return the default value for a table column
DEGREES()	Convert radians to degrees
DES_DECRYPT()	Decrypt a string
DES_ENCRYPT()	Encrypt a string
DIV	Integer division
/	Division operator
ELT()	Return string at index number
ENCODE()	Encode a string
ENCRYPT()	Encrypt a string
<=>	NULL-safe equal to operator
=	Equal operator
EXP()	Raise to the power of
EXPORT_SET()	Return a string such that for every bit set in the value bits, you get an on string and for every unset bit, you get an off string
EXTRACT()	Extract part of a date
ExtractValue()	Extracts a value from an XML string using XPath notation
FIELD()	Return the index (position) of the first argument in the subsequent arguments
FIND_IN_SET()	Return the index position of the first argument within the second argument
FLOOR()	Return the largest integer value not greater than the argument
FORMAT()	Return a number formatted to specified number of decimal places
FOUND_ROWS()	For a SELECT with a LIMIT clause, the number of rows that would be returned were there no LIMIT clause
FROM_DAYS()	Convert a day number to a date
FROM_UNIXTIME()	Format UNIX timestamp as a date
GET_FORMAT()	Return a date format string
GET_LOCK()	Get a named lock
>=	Greater than or equal operator
>	Greater than operator
GREATEST()	Return the largest argument
GROUP_CONCAT()	Return a concatenated string
HEX()	Return a hexadecimal representation of a decimal or string value
HOUR()	Extract the hour
IF()	If/else construct
IFNULL()	Null if/else construct
IN()	Check whether a value is within a set of values
INET_ATON()	Return the numeric value of an IP address
INET_NTOA()	Return the IP address from a numeric value
INSERT()	Insert a substring at the specified position up to the specified number of characters
INSTR()	Return the index of the first occurrence of substring
INTERVAL()	Return the index of the argument that is less than the first argument
IS_FREE_LOCK()	Checks whether the named lock is free
IS NOT NULL	NOT NULL value test
IS NOT	Test a value against a boolean
IS NULL	NULL value test
IS_USED_LOCK()	Checks whether the named lock is in use. Return connection identifier if true.
IS	Test a value against a boolean
ISNULL()	Test whether the argument is NULL
LAST_DAY	Return the last day of the month for the argument
LAST_INSERT_ID()	Value of the AUTOINCREMENT column for the last INSERT
LCASE()	Synonym for LOWER()
LEAST()	Return the smallest argument
<<	Left shift
LEFT()	Return the leftmost number of characters as specified
LENGTH()	Return the length of a string in bytes
<=	Less than or equal operator
<	Less than operator
LIKE	Simple pattern matching
LN()	Return the natural logarithm of the argument
LOAD_FILE()	Load the named file
LOCALTIME(), LOCALTIME	Synonym for NOW()
LOCALTIMESTAMP, LOCALTIMESTAMP()	Synonym for NOW()
LOCATE()	Return the position of the first occurrence of substring
LOG10()	Return the base-10 logarithm of the argument
LOG2()	Return the base-2 logarithm of the argument
LOG()	Return the natural logarithm of the first argument
LOWER()	Return the argument in lowercase
LPAD()	Return the string argument, left-padded with the specified string
LTRIM()	Remove leading spaces
MAKE_SET()	Return a set of comma-separated strings that have the corresponding bit in bits set
MAKEDATE()	Create a date from the year and day of year
MAKETIME	MAKETIME()
MASTER_POS_WAIT()	Block until the slave has read and applied all updates up to the specified position
MATCH	Perform full-text search
MAX()	Return the maximum value
MD5()	Calculate MD5 checksum
MICROSECOND()	Return the microseconds from argument
MID()	Return a substring starting from the specified position
MIN()	Return the minimum value
-	Minus operator
MINUTE()	Return the minute from the argument
MOD()	Return the remainder
% or MOD	Modulo operator
MONTH()	Return the month from the date passed
MONTHNAME()	Return the name of the month
NAME_CONST()	Causes the column to have the given name
NOT BETWEEN ... AND ...	Check whether a value is not within a range of values
!=, <>	Not equal operator
NOT IN()	Check whether a value is not within a set of values
NOT LIKE	Negation of simple pattern matching
NOT REGEXP	Negation of REGEXP
NOT, !	Negates value
NOW()	Return the current date and time
NULLIF()	Return NULL if expr1 = expr2
OCT()	Return a string containing octal representation of a number
OCTET_LENGTH()	A synonym for LENGTH()
OLD_PASSWORD()	Return the value of the pre-4.1 implementation of PASSWORD
\|\|, OR	Logical OR
ORD()	Return character code for leftmost character of the argument
PASSWORD()	Calculate and return a password string
PERIOD_ADD()	Add a period to a year-month
PERIOD_DIFF()	Return the number of months between periods
PI()	Return the value of pi
+	Addition operator
POSITION()	A synonym for LOCATE()
POW()	Return the argument raised to the specified power
POWER()	Return the argument raised to the specified power
PROCEDURE ANALYSE()	Analyze the results of a query
QUARTER()	Return the quarter from a date argument
QUOTE()	Escape the argument for use in an SQL statement
RADIANS()	Return argument converted to radians
RAND()	Return a random floating-point value
REGEXP	Pattern matching using regular expressions
RELEASE_LOCK()	Releases the named lock
REPEAT()	Repeat a string the specified number of times
REPLACE()	Replace occurrences of a specified string
REVERSE()	Reverse the characters in a string
>>	Right shift
RIGHT()	Return the specified rightmost number of characters
RLIKE	Synonym for REGEXP
ROUND()	Round the argument
ROW_COUNT()	The number of rows updated
RPAD()	Append string the specified number of times
RTRIM()	Remove trailing spaces
SCHEMA()	A synonym for DATABASE()
SEC_TO_TIME()	Converts seconds to 'HH:MM:SS' format
SECOND()	Return the second (0-59)
SESSION_USER()	Synonym for USER()
SHA1(), SHA()	Calculate an SHA-1 160-bit checksum
SHA2()	Calculate an SHA-2 checksum
SIGN()	Return the sign of the argument
SIN()	Return the sine of the argument
SLEEP()	Sleep for a number of seconds
SOUNDEX()	Return a soundex string
SOUNDS LIKE	Compare sounds
SPACE()	Return a string of the specified number of spaces
SQRT()	Return the square root of the argument
STD()	Return the population standard deviation
STDDEV_POP()	Return the population standard deviation
STDDEV_SAMP()	Return the sample standard deviation
STDDEV()	Return the population standard deviation
STR_TO_DATE()	Convert a string to a date
STRCMP()	Compare two strings
SUBDATE()	A synonym for DATE_SUB() when invoked with three arguments
SUBSTR()	Return the substring as specified
SUBSTRING_INDEX()	Return a substring from a string before the specified number of occurrences of the delimiter
SUBSTRING()	Return the substring as specified
SUBTIME()	Subtract times
SUM()	Return the sum
SYSDATE()	Return the time at which the function executes
SYSTEM_USER()	Synonym for USER()
TAN()	Return the tangent of the argument
TIME_FORMAT()	Format as time
TIME_TO_SEC()	Return the argument converted to seconds
TIME()	Extract the time portion of the expression passed
TIMEDIFF()	Subtract time
*	Multiplication operator
TIMESTAMP()	With a single argument, this function returns the date or datetime expression; with two arguments, the sum of the arguments
TIMESTAMPADD()	Add an interval to a datetime expression
TIMESTAMPDIFF()	Subtract an interval from a datetime expression
TO_DAYS()	Return the date argument converted to days
TO_SECONDS()	Return the date or datetime argument converted to seconds since Year 0
TRIM()	Remove leading and trailing spaces
TRUNCATE()	Truncate to specified number of decimal places
UCASE()	Synonym for UPPER()
-	Change the sign of the argument
UNCOMPRESS()	Uncompress a string compressed
UNCOMPRESSED_LENGTH()	Return the length of a string before compression
UNHEX()	Return a string containing hex representation of a number
UNIX_TIMESTAMP()	Return a UNIX timestamp
UpdateXML()	Return replaced XML fragment
UPPER()	Convert to uppercase
USER()	The user name and host name provided by the client
UTC_DATE()	Return the current UTC date
UTC_TIME()	Return the current UTC time
UTC_TIMESTAMP()	Return the current UTC date and time
UUID_SHORT()	Return an integer-valued universal identifier
UUID()	Return a Universal Unique Identifier (UUID)
VALUES()	Defines the values to be used during an INSERT
VAR_POP()	Return the population standard variance
VAR_SAMP()	Return the sample variance
VARIANCE()	Return the population standard variance
VERSION()	Returns a string that indicates the MySQL server version
WEEK()	Return the week number
WEEKDAY()	Return the weekday index
WEEKOFYEAR()	Return the calendar week of the date (0-53)
XOR	Logical XOR
YEAR()	Return the year
YEARWEEK()	Return the year and week

12.2. Type Conversion in Expression Evaluation

When an operator is used with operands of different types, type conversion occurs to make the operands compatible. Some conversions occur implicitly. For example, MySQL automatically converts numbers to strings as necessary, and vice versa.

mysql> SELECT 1+'1'; -> 2mysql> SELECT CONCAT(2,' test'); -> '2 test'

It is also possible to convert a number to a string explicitly using the CAST() function. Conversion occurs implicitly with the CONCAT() function because it expects string arguments.

mysql> SELECT 38.8, CAST(38.8 AS CHAR); -> 38.8, '38.8'mysql> SELECT 38.8, CONCAT(38.8); -> 38.8, '38.8'

See later in this section for information about the character set of implicit number-to-string conversions.

The following rules describe how conversion occurs for comparison operations:

If one or both arguments are NULL, the result of the comparison is NULL, except for the NULL-safe <=> equality comparison operator. For NULL <=> NULL, the result is true. No conversion is needed.
If both arguments in a comparison operation are strings, they are compared as strings.
If both arguments are integers, they are compared as integers.
Hexadecimal values are treated as binary strings if not compared to a number.
If one of the arguments is a TIMESTAMP or DATETIME column and the other argument is a constant, the constant is converted to a timestamp before the comparison is performed. This is done to be more ODBC-friendly. Note that this is not done for the arguments to IN()! To be safe, always use complete datetime, date, or time strings when doing comparisons. For example, to achieve best results when using BETWEEN with date or time values, use CAST() to explicitly convert the values to the desired data type.
If one of the arguments is a decimal value, comparison depends on the other argument. The arguments are compared as decimal values if the other argument is a decimal or integer value, or as floating-point values if the other argument is a floating-point value.
In all other cases, the arguments are compared as floating-point (real) numbers.

For information about conversion of values from one temporal type to another, see Section 11.3.7, "Conversion Between Date and Time Types".

The following examples illustrate conversion of strings to numbers for comparison operations:

mysql> SELECT 1 > '6x'; -> 0mysql> SELECT 7 > '6x'; -> 1mysql> SELECT 0 > 'x6'; -> 0mysql> SELECT 0 = 'x6'; -> 1

For comparisons of a string column with a number, MySQL cannot use an index on the column to look up the value quickly. If str_col is an indexed string column, the index cannot be used when performing the lookup in the following statement:

SELECT * FROM tbl_name WHERE str_col=1;

The reason for this is that there are many different strings that may convert to the value 1, such as '1', ' 1', or '1a'.

Comparisons that use floating-point numbers (or values that are converted to floating-point numbers) are approximate because such numbers are inexact. This might lead to results that appear inconsistent:

mysql> SELECT '18015376320243458' = 18015376320243458; -> 1mysql> SELECT '18015376320243459' = 18015376320243459; -> 0

Such results can occur because the values are converted to floating-point numbers, which have only 53 bits of precision and are subject to rounding:

mysql> SELECT '18015376320243459'+0.0; -> 1.8015376320243e+16

Furthermore, the conversion from string to floating-point and from integer to floating-point do not necessarily occur the same way. The integer may be converted to floating-point by the CPU, whereas the string is converted digit by digit in an operation that involves floating-point multiplications.

The results shown will vary on different systems, and can be affected by factors such as computer architecture or the compiler version or optimization level. One way to avoid such problems is to use CAST() so that a value will not be converted implicitly to a float-point number:

mysql> SELECT CAST('18015376320243459' AS UNSIGNED) = 18015376320243459; -> 1

For more information about floating-point comparisons, see Section C.5.5.8, "Problems with Floating-Point Values".

As of MySQL 5.5.3, the server includes dtoa, a conversion library that provides the basis for improved conversion between string or DECIMAL values and approximate-value (FLOAT/DOUBLE) numbers:

Consistent conversion results across platforms, which eliminates, for example, Unix versus Windows conversion differences.
Accurate representation of values in cases where results previously did not provide sufficient precision, such as for values close to IEEE limits.
Conversion of numbers to string format with the best possible precision. The precision of dtoa is always the same or better than that of the standard C library functions.

Because the conversions produced by this library differ in some cases from previous results, the potential exists for incompatibilities in applications that rely on previous results. For example, applications that depend on a specific exact result from previous conversions might need adjustment to accommodate additional precision.

The dtoa library provides conversions with the following properties. D represents a value with a DECIMAL or string representation, and F represents a floating-point number in native binary (IEEE) format.

F -> D conversion is done with the best possible precision, returning D as the shortest string that yields F when read back in and rounded to the nearest value in native binary format as specified by IEEE.
D -> F conversion is done such that F is the nearest native binary number to the input decimal string D.

These properties imply that F -> D -> F conversions are lossless unless F is -inf, +inf, or NaN. The latter values are not supported because the SQL standard defines them as invalid values for FLOAT or DOUBLE.

For D -> F -> D conversions, a sufficient condition for losslessness is that D uses 15 or fewer digits of precision, is not a denormal value, -inf, +inf, or NaN. In some cases, the conversion is lossless even if D has more than 15 digits of precision, but this is not always the case.

As of MySQL 5.5.3, implicit conversion of a numeric or temporal value to string produces a value that has a character set and collation determined by the character_set_connection and collation_connection system variables. (These variables commonly are set with SET NAMES. For information about connection character sets, see Section 10.1.4, "Connection Character Sets and Collations".)

This change means that such a conversion results in a character (nonbinary) string (a CHAR, VARCHAR, or LONGTEXT value), except when the connection character set is set to binary. In that case, the conversion result is a binary string (a BINARY, VARBINARY, or LONGBLOB value).

Before MySQL 5.5.3, an implicit conversion always produced a binary string, regardless of the connection character set. Such implicit conversions to string typically occur for functions that are passed numeric or temporal values when string values are more usual, and thus could have effects beyond the type of the converted value. Consider the expression CONCAT(1, 'abc'). The numeric argument 1 was converted to the binary string '1' and the concatenation of that value with the nonbinary string 'abc' produced the binary string '1abc'.

Some functions are unaffected by this change in behavior:

CHAR() without a USING clause still returns VARBINARY.
Functions that previously returned utf8 strings still do so. Examples include CHARSET() and COLLATION().
Encryption and compression functions that expect string arguments and previously returned binary strings are unaffected if the return value can contain non-ASCII characters. Examples include AES_ENCRYPT() and COMPRESS(). If the return value contains only ASCII characters, the function now returns a character string with the connection character set and collation. Examples include MD5() and PASSWORD().

12.3. Operators

12.3.1. Operator Precedence
12.3.2. Comparison Functions and Operators
12.3.3. Logical Operators
12.3.4. Assignment Operators

Table 12.2. Operators

Name	Description
AND, &&	Logical AND
=	Assign a value (as part of a SET statement, or as part of the SET clause in an UPDATE statement)
:=	Assign a value
BETWEEN ... AND ...	Check whether a value is within a range of values
BINARY	Cast a string to a binary string
&	Bitwise AND
~	Invert bits
\|	Bitwise OR
^	Bitwise XOR
CASE	Case operator
DIV	Integer division
/	Division operator
<=>	NULL-safe equal to operator
=	Equal operator
>=	Greater than or equal operator
>	Greater than operator
IS NOT NULL	NOT NULL value test
IS NOT	Test a value against a boolean
IS NULL	NULL value test
IS	Test a value against a boolean
<<	Left shift
<=	Less than or equal operator
<	Less than operator
LIKE	Simple pattern matching
-	Minus operator
% or MOD	Modulo operator
NOT BETWEEN ... AND ...	Check whether a value is not within a range of values
!=, <>	Not equal operator
NOT LIKE	Negation of simple pattern matching
NOT REGEXP	Negation of REGEXP
NOT, !	Negates value
\|\|, OR	Logical OR
+	Addition operator
REGEXP	Pattern matching using regular expressions
>>	Right shift
RLIKE	Synonym for REGEXP
SOUNDS LIKE	Compare sounds
*	Multiplication operator
-	Change the sign of the argument
XOR	Logical XOR

12.3.1. Operator Precedence

Operator precedences are shown in the following list, from highest precedence to the lowest. Operators that are shown together on a line have the same precedence.

INTERVALBINARY, COLLATE!- (unary minus), ~ (unary bit inversion)^*, /, DIV, %, MOD-, +<<, >>&|= (comparison), <=>, >=, >, <=, <, <>, !=, IS, LIKE, REGEXP, INBETWEEN, CASE, WHEN, THEN, ELSENOT&&, ANDXOR||, OR= (assignment), :=

The precedence of = depends on whether it is used as a comparison operator (=) or as an assignment operator (=). When used as a comparison operator, it has the same precedence as <=>, >=, >, <=, <, <>, !=, IS, LIKE, REGEXP, and IN. When used as an assignment operator, it has the same precedence as :=. Section 13.7.4, "SET Syntax", and Section 9.4, "User-Defined Variables", explain how MySQL determines which interpretation of = should apply.

The meaning of some operators depends on the SQL mode:

By default, || is a logical OR operator. With PIPES_AS_CONCAT enabled, || is string concatenation, with a precedence between ^ and the unary operators.
By default, ! has a higher precedence than NOT. With HIGH_NOT_PRECEDENCE enabled, ! and NOT have the same precedence.

See Section 5.1.7, "Server SQL Modes".

The precedence of operators determines the order of evaluation of terms in an expression. To override this order and group terms explicitly, use parentheses. For example:

mysql> SELECT 1+2*3; -> 7mysql> SELECT (1+2)*3; -> 9

12.3.2. Comparison Functions and Operators

Table 12.3. Comparison Operators

Name	Description
BETWEEN ... AND ...	Check whether a value is within a range of values
COALESCE()	Return the first non-NULL argument
<=>	NULL-safe equal to operator
=	Equal operator
>=	Greater than or equal operator
>	Greater than operator
GREATEST()	Return the largest argument
IN()	Check whether a value is within a set of values
INTERVAL()	Return the index of the argument that is less than the first argument
IS NOT NULL	NOT NULL value test
IS NOT	Test a value against a boolean
IS NULL	NULL value test
IS	Test a value against a boolean
ISNULL()	Test whether the argument is NULL
LEAST()	Return the smallest argument
<=	Less than or equal operator
<	Less than operator
LIKE	Simple pattern matching
NOT BETWEEN ... AND ...	Check whether a value is not within a range of values
!=, <>	Not equal operator
NOT IN()	Check whether a value is not within a set of values
NOT LIKE	Negation of simple pattern matching
STRCMP()	Compare two strings

Comparison operations result in a value of 1 (TRUE), 0 (FALSE), or NULL. These operations work for both numbers and strings. Strings are automatically converted to numbers and numbers to strings as necessary.

The following relational comparison operators can be used to compare not only scalar operands, but row operands:

=  >  <  >=  <=  <>  !=

For examples of row comparisons, see Section 13.2.10.5, "Row Subqueries".

Some of the functions in this section return values other than 1 (TRUE), 0 (FALSE), or NULL. For example, LEAST() and GREATEST(). However, the value they return is based on comparison operations performed according to the rules described in Section 12.2, "Type Conversion in Expression Evaluation".

To convert a value to a specific type for comparison purposes, you can use the CAST() function. String values can be converted to a different character set using CONVERT(). See Section 12.10, "Cast Functions and Operators".

By default, string comparisons are not case sensitive and use the current character set. The default is latin1 (cp1252 West European), which also works well for English.

Equal:

mysql> SELECT 1 = 0; -> 0mysql> SELECT '0' = 0; -> 1mysql> SELECT '0.0' = 0; -> 1mysql> SELECT '0.01' = 0; -> 0mysql> SELECT '.01' = 0.01; -> 1

<=>
NULL-safe equal. This operator performs an equality comparison like the = operator, but returns 1 rather than NULL if both operands are NULL, and 0 rather than NULL if one operand is NULL.
```
mysql> SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL; -> 1, 1, 0mysql> SELECT 1 = 1, NULL = NULL, 1 = NULL; -> 1, NULL, NULL
```

<>, !=

Not equal:

mysql> SELECT '.01' <> '0.01'; -> 1mysql> SELECT .01 <> '0.01'; -> 0mysql> SELECT 'zapp' <> 'zappp'; -> 1

<=
Less than or equal:
```
mysql> SELECT 0.1 <= 2; -> 1
```
<
Less than:
```
mysql> SELECT 2 < 2; -> 0
```
>=
Greater than or equal:
```
mysql> SELECT 2 >= 2; -> 1
```
>
Greater than:
```
mysql> SELECT 2 > 2; -> 0
```
IS boolean_value
Tests a value against a boolean value, where boolean_value can be TRUE, FALSE, or UNKNOWN.
```
mysql> SELECT 1 IS TRUE, 0 IS FALSE, NULL IS UNKNOWN; -> 1, 1, 1
```
IS NOT boolean_value
Tests a value against a boolean value, where boolean_value can be TRUE, FALSE, or UNKNOWN.
```
mysql> SELECT 1 IS NOT UNKNOWN, 0 IS NOT UNKNOWN, NULL IS NOT UNKNOWN; -> 1, 1, 0
```
IS NULL
Tests whether a value is NULL.
```
mysql> SELECT 1 IS NULL, 0 IS NULL, NULL IS NULL; -> 0, 0, 1
```
To work well with ODBC programs, MySQL supports the following extra features when using IS NULL:
- If sql_auto_is_null variable is set to 1, then after a statement that successfully inserts an automatically generated AUTO_INCREMENT value, you can find that value by issuing a statement of the following form:
```
SELECT * FROM tbl_name WHERE auto_col IS NULL
```
  If the statement returns a row, the value returned is the same as if you invoked the LAST_INSERT_ID() function. For details, including the return value after a multiple-row insert, see Section 12.14, "Information Functions". If no AUTO_INCREMENT value was successfully inserted, the SELECT statement returns no row.
  The behavior of retrieving an AUTO_INCREMENT value by using an IS NULL comparison can be disabled by setting sql_auto_is_null = 0. See Section 5.1.4, "Server System Variables".
  The default value of sql_auto_is_null is 0 as of MySQL 5.5.3, and 1 for earlier versions.
- For DATE and DATETIME columns that are declared as NOT NULL, you can find the special date '0000-00-00' by using a statement like this:
```
SELECT * FROM tbl_name WHERE date_column IS NULL
```
  This is needed to get some ODBC applications to work because ODBC does not support a '0000-00-00' date value.
  See Section 22.1.7.1.1, "Obtaining Auto-Increment Values", and the description for the FLAG_AUTO_IS_NULL option at Section 22.1.4.2, "Connector/ODBC Connection Parameters".

IS NOT NULL

Tests whether a value is not NULL.

mysql> SELECT 1 IS NOT NULL, 0 IS NOT NULL, NULL IS NOT NULL; -> 1, 1, 0

expr BETWEEN min AND max
If expr is greater than or equal to min and expr is less than or equal to max, BETWEEN returns 1, otherwise it returns 0. This is equivalent to the expression (min <= expr AND expr <= max) if all the arguments are of the same type. Otherwise type conversion takes place according to the rules described in Section 12.2, "Type Conversion in Expression Evaluation", but applied to all the three arguments.
```
mysql> SELECT 2 BETWEEN 1 AND 3, 2 BETWEEN 3 and 1; -> 1, 0mysql> SELECT 1 BETWEEN 2 AND 3; -> 0mysql> SELECT 'b' BETWEEN 'a' AND 'c'; -> 1mysql> SELECT 2 BETWEEN 2 AND '3'; -> 1mysql> SELECT 2 BETWEEN 2 AND 'x-3'; -> 0
```
For best results when using BETWEEN with date or time values, use CAST() to explicitly convert the values to the desired data type. Examples: If you compare a DATETIME to two DATE values, convert the DATE values to DATETIME values. If you use a string constant such as '2001-1-1' in a comparison to a DATE, cast the string to a DATE.
expr NOT BETWEEN min AND max
This is the same as NOT (expr BETWEEN min AND max).
COALESCE(value,...)
Returns the first non-NULL value in the list, or NULL if there are no non-NULL values.
```
mysql> SELECT COALESCE(NULL,1); -> 1mysql> SELECT COALESCE(NULL,NULL,NULL); -> NULL
```
GREATEST(value1,value2,...)
With two or more arguments, returns the largest (maximum-valued) argument. The arguments are compared using the same rules as for LEAST().
```
mysql> SELECT GREATEST(2,0); -> 2mysql> SELECT GREATEST(34.0,3.0,5.0,767.0); -> 767.0mysql> SELECT GREATEST('B','A','C'); -> 'C'
```
GREATEST() returns NULL if any argument is NULL.
expr IN (value,...)
Returns 1 if expr is equal to any of the values in the IN list, else returns 0. If all values are constants, they are evaluated according to the type of expr and sorted. The search for the item then is done using a binary search. This means IN is very quick if the IN value list consists entirely of constants. Otherwise, type conversion takes place according to the rules described in Section 12.2, "Type Conversion in Expression Evaluation", but applied to all the arguments.
```
mysql> SELECT 2 IN (0,3,5,7); -> 0mysql> SELECT 'wefwf' IN ('wee','wefwf','weg'); -> 1
```
You should never mix quoted and unquoted values in an IN list because the comparison rules for quoted values (such as strings) and unquoted values (such as numbers) differ. Mixing types may therefore lead to inconsistent results. For example, do not write an IN expression like this:
```
SELECT val1 FROM tbl1 WHERE val1 IN (1,2,'a');
```
Instead, write it like this:
```
SELECT val1 FROM tbl1 WHERE val1 IN ('1','2','a');
```
The number of values in the IN list is only limited by the max_allowed_packet value.
To comply with the SQL standard, IN returns NULL not only if the expression on the left hand side is NULL, but also if no match is found in the list and one of the expressions in the list is NULL.
IN() syntax can also be used to write certain types of subqueries. See Section 13.2.10.3, "Subqueries with ANY, IN, or SOME".
expr NOT IN (value,...)
This is the same as NOT (expr IN (value,...)).
ISNULL(expr)
If expr is NULL, ISNULL() returns 1, otherwise it returns 0.
```
mysql> SELECT ISNULL(1+1); -> 0mysql> SELECT ISNULL(1/0); -> 1
```
ISNULL() can be used instead of = to test whether a value is NULL. (Comparing a value to NULL using = always yields false.)
The ISNULL() function shares some special behaviors with the IS NULL comparison operator. See the description of IS NULL.
INTERVAL(N,N1,N2,N3,...)
Returns 0 if N < N1, 1 if N < N2 and so on or -1 if N is NULL. All arguments are treated as integers. It is required that N1 < N2 < N3 < ... < Nn for this function to work correctly. This is because a binary search is used (very fast).
```
mysql> SELECT INTERVAL(23, 1, 15, 17, 30, 44, 200); -> 3mysql> SELECT INTERVAL(10, 1, 10, 100, 1000); -> 2mysql> SELECT INTERVAL(22, 23, 30, 44, 200); -> 0
```
LEAST(value1,value2,...)
With two or more arguments, returns the smallest (minimum-valued) argument. The arguments are compared using the following rules:
- If any argument is NULL, the result is NULL. No comparison is needed.
- If the return value is used in an INTEGER context or all arguments are integer-valued, they are compared as integers.
- If the return value is used in a REAL context or all arguments are real-valued, they are compared as reals.
- If the arguments comprise a mix of numbers and strings, they are compared as numbers.
- If any argument is a nonbinary (character) string, the arguments are compared as nonbinary strings.
- In all other cases, the arguments are compared as binary strings.
```
mysql> SELECT LEAST(2,0); -> 0mysql> SELECT LEAST(34.0,3.0,5.0,767.0); -> 3.0mysql> SELECT LEAST('B','A','C'); -> 'A'
```
Note that the preceding conversion rules can produce strange results in some borderline cases:
```
mysql> SELECT CAST(LEAST(3600, 9223372036854775808.0) as SIGNED); -> -9223372036854775808
```
This happens because MySQL reads 9223372036854775808.0 in an integer context. The integer representation is not good enough to hold the value, so it wraps to a signed integer.

12.3.3. Logical Operators

Table 12.4. Logical Operators

Name	Description
AND, &&	Logical AND
NOT, !	Negates value
\|\|, OR	Logical OR
XOR	Logical XOR

In SQL, all logical operators evaluate to TRUE, FALSE, or NULL (UNKNOWN). In MySQL, these are implemented as 1 (TRUE), 0 (FALSE), and NULL. Most of this is common to different SQL database servers, although some servers may return any nonzero value for TRUE.

MySQL evaluates any nonzero, non-NULL value to TRUE. For example, the following statements all assess to TRUE:

mysql> SELECT 10 IS TRUE;-> 1mysql> SELECT -10 IS TRUE;-> 1mysql> SELECT 'string' IS NOT NULL;-> 1

NOT, !
Logical NOT. Evaluates to 1 if the operand is 0, to 0 if the operand is nonzero, and NOT NULL returns NULL.
```
mysql> SELECT NOT 10; -> 0mysql> SELECT NOT 0; -> 1mysql> SELECT NOT NULL; -> NULLmysql> SELECT ! (1+1); -> 0mysql> SELECT ! 1+1; -> 1
```
The last example produces 1 because the expression evaluates the same way as (!1)+1.

AND, &&

Logical AND. Evaluates to 1 if all operands are nonzero and not NULL, to 0 if one or more operands are 0, otherwise NULL is returned.

mysql> SELECT 1 && 1; -> 1mysql> SELECT 1 && 0; -> 0mysql> SELECT 1 && NULL; -> NULLmysql> SELECT 0 && NULL; -> 0mysql> SELECT NULL && 0; -> 0

OR, ||
Logical OR. When both operands are non-NULL, the result is 1 if any operand is nonzero, and 0 otherwise. With a NULL operand, the result is 1 if the other operand is nonzero, and NULL otherwise. If both operands are NULL, the result is NULL.
```
mysql> SELECT 1 || 1; -> 1mysql> SELECT 1 || 0; -> 1mysql> SELECT 0 || 0; -> 0mysql> SELECT 0 || NULL; -> NULLmysql> SELECT 1 || NULL; -> 1
```
XOR
Logical XOR. Returns NULL if either operand is NULL. For non-NULL operands, evaluates to 1 if an odd number of operands is nonzero, otherwise 0 is returned.
```
mysql> SELECT 1 XOR 1; -> 0mysql> SELECT 1 XOR 0; -> 1mysql> SELECT 1 XOR NULL; -> NULLmysql> SELECT 1 XOR 1 XOR 1; -> 1
```
a XOR b is mathematically equal to (a AND (NOT b)) OR ((NOT a) and b).

12.3.4. Assignment Operators

Table 12.5. Assignment Operators

Name	Description
=	Assign a value (as part of a SET statement, or as part of the SET clause in an UPDATE statement)
:=	Assign a value

:=
Assignment operator. Causes the user variable on the left hand side of the operator to take on the value to its right. The value on the right hand side may be a literal value, another variable storing a value, or any legal expression that yields a scalar value, including the result of a query (provided that this value is a scalar value). You can perform multiple assignments in the same SET statement. You can perform multiple assignments in the same statement-
Unlike =, the := operator is never interpreted as a comparison operator. This means you can use := in any valid SQL statement (not just in SET statements) to assign a value to a variable.
```
mysql> SELECT @var1, @var2; -> NULL, NULLmysql> SELECT @var1 := 1, @var2; -> 1, NULLmysql> SELECT @var1, @var2; -> 1, NULLmysql> SELECT @var1, @var2 := @var1; -> 1, 1mysql> SELECT @var1, @var2; -> 1, 1mysql> SELECT @var1:=COUNT(*) FROM t1; -> 4mysql> SELECT @var1; -> 4
```
You can make value assignments using := in other statements besides SELECT, such as UPDATE, as shown here:
```
mysql> SELECT @var1; -> 4mysql> SELECT * FROM t1; -> 1, 3, 5, 7mysql> UPDATE t1 SET c1 = 2 WHERE c1 = @var1:= 1;Query OK, 1 row affected (0.00 sec)Rows matched: 1  Changed: 1  Warnings: 0mysql> SELECT @var1; -> 1mysql> SELECT * FROM t1; -> 2, 3, 5, 7
```
While it is also possible both to set and to read the value of the same variable in a single SQL statement using the := operator, this is not recommended. Section 9.4, "User-Defined Variables", explains why you should avoid doing this.
=
This operator is used to perform value assignments in two cases, described in the next two paragraphs.
Within a SET statement, = is treated as an assignment operator that causes the user variable on the left hand side of the operator to take on the value to its right. (In other words, when used in a SET statement, = is treated identically to :=.) The value on the right hand side may be a literal value, another variable storing a value, or any legal expression that yields a scalar value, including the result of a query (provided that this value is a scalar value). You can perform multiple assignments in the same SET statement.
In the SET clause of an UPDATE statement, = also acts as an assignment operator; in this case, however, it causes the column named on the left hand side of the operator to assume the value given to the right, provided any WHERE conditions that are part of the UPDATE are met. You can make multiple assignments in the same SET clause of an UPDATE statement.
In any other context, = is treated as a comparison operator.
```
mysql> SELECT @var1, @var2; -> NULL, NULLmysql> SELECT @var1 := 1, @var2; -> 1, NULLmysql> SELECT @var1, @var2; -> 1, NULLmysql> SELECT @var1, @var2 := @var1; -> 1, 1mysql> SELECT @var1, @var2; -> 1, 1
```
For more information, see Section 13.7.4, "SET Syntax", Section 13.2.11, "UPDATE Syntax", and Section 13.2.10, "Subquery Syntax".

12.4. Control Flow Functions

Table 12.6. Flow Control Operators

Name	Description
CASE	Case operator
IF()	If/else construct
IFNULL()	Null if/else construct
NULLIF()	Return NULL if expr1 = expr2

CASE value WHEN [compare_value] THEN result [WHEN [compare_value] THEN result ...] [ELSE result] END
CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END
The first version returns the result where value=compare_value. The second version returns the result for the first condition that is true. If there was no matching result value, the result after ELSE is returned, or NULL if there is no ELSE part.
```
mysql> SELECT CASE 1 WHEN 1 THEN 'one' -> WHEN 2 THEN 'two' ELSE 'more' END; -> 'one'mysql> SELECT CASE WHEN 1>0 THEN 'true' ELSE 'false' END; -> 'true'mysql> SELECT CASE BINARY 'B' -> WHEN 'a' THEN 1 WHEN 'b' THEN 2 END; -> NULL
```
The return type of a CASE expression is the compatible aggregated type of all return values, but also depends on the context in which it is used. If used in a string context, the result is returned as a string. If used in a numeric context, the result is returned as a decimal, real, or integer value.
Note
The syntax of the CASE expression shown here differs slightly from that of the SQL CASE statement described in Section 13.6.5.1, "CASE Syntax", for use inside stored programs. The CASE statement cannot have an ELSE NULL clause, and it is terminated with END CASE instead of END.
IF(expr1,expr2,expr3)
If expr1 is TRUE (expr1 <> 0 and expr1 <> NULL) then IF() returns expr2; otherwise it returns expr3. IF() returns a numeric or string value, depending on the context in which it is used.
```
mysql> SELECT IF(1>2,2,3); -> 3mysql> SELECT IF(1<2,'yes','no'); -> 'yes'mysql> SELECT IF(STRCMP('test','test1'),'no','yes'); -> 'no'
```
If only one of expr2 or expr3 is explicitly NULL, the result type of the IF() function is the type of the non-NULL expression.
The default return type of IF() (which may matter when it is stored into a temporary table) is calculated as follows.
Expression Return Value
expr2 or expr3 returns a string string
expr2 or expr3 returns a floating-point value floating-point
expr2 or expr3returns an integer integer
If expr2 and expr3 are both strings, the result is case sensitive if either string is case sensitive.
Note
There is also an IF statement, which differs from the IF() function described here. See Section 13.6.5.2, "IF Syntax".
IFNULL(expr1,expr2)
If expr1 is not NULL, IFNULL() returns expr1; otherwise it returns expr2. IFNULL() returns a numeric or string value, depending on the context in which it is used.
```
mysql> SELECT IFNULL(1,0); -> 1mysql> SELECT IFNULL(NULL,10); -> 10mysql> SELECT IFNULL(1/0,10); -> 10mysql> SELECT IFNULL(1/0,'yes'); -> 'yes'
```
The default result value of IFNULL(expr1,expr2) is the more "general" of the two expressions, in the order STRING, REAL, or INTEGER. Consider the case of a table based on expressions or where MySQL must internally store a value returned by IFNULL() in a temporary table:
```
mysql> CREATE TABLE tmp SELECT IFNULL(1,'test') AS test;mysql> DESCRIBE tmp;+-------+--------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+--------------+------+-----+---------+-------+| test  | varbinary(4) | NO   | | |   |+-------+--------------+------+-----+---------+-------+
```
In this example, the type of the test column is VARBINARY(4).
NULLIF(expr1,expr2)
Returns NULL if expr1 = expr2 is true, otherwise returns expr1. This is the same as CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END.
```
mysql> SELECT NULLIF(1,1); -> NULLmysql> SELECT NULLIF(1,2); -> 1
```
Note that MySQL evaluates expr1 twice if the arguments are not equal.