Data Types in MySQL

This chapter is from the book 

3.5 Expression Evaluation and Type Conversion

Expressions contain terms and operators and are evaluated to produce values. Terms can include values such as constants, function calls, references to table columns, and scalar subqueries. These values may be combined using different kinds of operators, such as arithmetic or comparison operators, and terms of an expression may be grouped with parentheses. Expressions occur most commonly in the output column list and WHERE clause of SELECT statements. For example, here is a query that is similar to one used for age calculations in Chapter 1:

SELECT
  CONCAT(last_name, ', ', first_name),
  TIMESTAMPDIFF(YEAR, birth, death)
FROM president
WHERE
  birth > '1900-1-1' AND DEATH IS NOT NULL;

Each selected value represents an expression, as does the content of the WHERE clause. Expressions also occur in the WHERE clause of DELETE and UPDATE statements, the VALUES() clause of INSERT statements, and so forth.

When MySQL encounters an expression, it evaluates the expression to produce a result. For example, (4*3) DIV (4-2) evaluates to the value 6. Expression evaluation may involve type conversion, such as when MySQL converts the number 960821 into a date '1996-08-21' if the number is used in a context requiring a DATE value.

This section discusses how you can write expressions in MySQL and the rules that govern the various kinds of type conversions that MySQL performs during the process of expression evaluation. Each of MySQL’s operators is listed here, but MySQL has so many functions that only a few are touched on. For more information, see Appendix C.

3.5.1 Writing Expressions

An expression can be as simple as a single constant, such as the numeric value 0 or string value 'abc'.

Expressions can use function calls. Some functions take arguments (values inside the parentheses), and some do not. Multiple arguments should be separated by commas. When you invoke a built-in function, there can be spaces around arguments, but if there is a space between the function name and the opening parenthesis, the MySQL parser might misinterpret the function name. The usual result is a syntax error. You can tell MySQL to allow spaces after names of built-in functions by enabling the IGNORE_SPACE SQL mode. However, that also causes function names to be treated as reserved words.

Expressions can include references to table columns. In the simplest case, when the table to which a column belongs is clear from context, a column reference may be given simply as the column name. Only one table is named in each of the following SELECT statements, so the column references are unambiguous, even though the same column names are used in each statement:

SELECT last_name, first_name FROM president;
SELECT last_name, first_name FROM member;

If it’s not clear which table should be used, a column name can be qualified by preceding it with the proper table name. If it’s not even clear which database should be used, the table name can be preceded by the database name. You can also use these more-specific qualified forms in unambiguous contexts if you simply want to be more explicit:

SELECT
  president.last_name, president.first_name,
  member.last_name, member.first_name
FROM president INNER JOIN member
WHERE president.last_name = member.last_name;

SELECT sampdb.student.name FROM sampdb.student;

Scalar subqueries can be used to provide a single value in an expression. The subquery requires surrounding parentheses:

SELECT * FROM president WHERE birth = (SELECT MAX(birth) FROM president);

Finally, you can combine all these kinds of values (constants, function calls, column references, and subqueries) to form more complex expressions.

3.5.1.1 Operator Types

Terms of expressions can be combined using several kinds of operators. This section describes what they do, and Section 3.5.1.2, “Operator Precedence,” discusses the order in which they are evaluated.

Arithmetic operators, listed in Table 3.18, include the usual addition, subtraction, multiplication, and division operators, as well as the modulo operator. Arithmetic is performed using BIGINT (64-bit) integer values for +, -, and * when both operands are integers. If both operands are integers, the result is unsigned if either operand is unsigned. For each operator other than DIV, if any operand is an approximate value, double-precision floating-point arithmetic is used. This is also true for strings converted to numbers, because strings are converted to double-precision numbers. Be aware that if an integer operation involves large values such that the result exceeds 64-bit range, you will get unpredictable results. (Actually, you should try to avoid exceeding 63-bit values; one bit is needed to represent the sign.)

Table 3.18. Arithmetic Operators

Operator	Syntax	Meaning
+	a + b	Addition; sum of operands
-	a - b	Subtraction; difference of operands
-	-a	Unary minus; negation of operand
*	a * b	Multiplication; product of operands
/	a / b	Division; quotient of operands
DIV	a DIV b	Division; integer quotient of operands
%	a % b	Modulo; remainder after division of operands

Logical operators, shown in Table 3.19, evaluate expressions to determine whether they are true (non-zero) or false (zero). It is also possible for a logical expression to evaluate to NULL if its value cannot be ascertained. For example, 1 AND NULL is of indeterminate value.

Table 3.19. Logical Operators

Operator	Syntax	Meaning
AND, &&	a AND b, a && b	Logical intersection; true if both operands are true
OR, ||	a OR b, a || b	Logical union; true if either operand is true
XOR	a XOR b	Logical exclusive-OR; true if exactly one operand is true
NOT, !	NOT a, !a	Logical negation; true if operand is false

As alternative forms of AND, OR, and NOT, MySQL supports the &&, ||, and ! operators, respectively, as used in the C programming language. Note in particular the || operator. Standard SQL specifies || as the string concatenation operator, but in MySQL it signifies a logical OR operation. If you use the following expression, expecting it to perform string concatenation, you may be surprised to discover that it returns the number 0:

'abc' || 'def'                                    → 0

This happens because 'abc' and 'def' are converted to integers for the operation, and both turn into 0. In MySQL, you must use CONCAT('abc','def') or proximity to perform string concatenation:

CONCAT('abc','def')                               → 'abcdef'
'abc' 'def'                                       → 'abcdef'

If you want the standard SQL behavior for ||, enable the PIPES_AS_CONCAT SQL mode.

Bit operators, shown in Table 3.20, perform bitwise intersection, union, and exclusive-OR, where each bit of the result is evaluated as the logical AND, OR, or exclusive-OR of the corresponding bits of the operands. You can also perform bit shifts left or right. Bit operations are performed using BIGINT (64-bit) integer values.

Table 3.20. Bit Operators

Operator	Syntax	Meaning
&	a & b	Bitwise AND (intersection); each bit of result is set if corresponding bits of both operands are set
|	a | b	Bitwise OR (union); each bit of result is set if corresponding bit of either operand is set
^	a ^ b	Bitwise exclusive-OR; each bit of result is set only if exactly one corresponding bit of the operands is set
<<	a << b	Left shift of a by b bit positions
>>	a >> b	Right shift of a by b bit positions

Comparison operators, shown in Table 3.21, include operators for testing relative magnitude or lexical ordering of numbers and strings, as well as operators for performing pattern matching and for testing NULL values. The <=> operator is MySQL-specific.

Table 3.21. Comparison Operators

Operator	Syntax	Meaning
=	a = b	True if operands are equal
<=>	a <=> b	True if operands are equal (even if NULL)
<>, !=	a <> b, a != b	True if operands are not equal
<	a < b	True if a is less than b
<=	a <= b	True if a is less than or equal to b
>=	a >= b	True if a is greater than or equal to b
>	a > b	True if a is greater than b
IN	a IN (b1, b2, ...)	True if a is equal to any of b1, b2, ...
BETWEEN	a BETWEEN b AND c	True if a is between the values of b and c, inclusive
NOT BETWEEN	a NOT BETWEEN b AND c	True if a is not between the values of b and c, inclusive
LIKE	a LIKE b	SQL pattern match; true if a matches b
NOT LIKE	a NOT LIKE b	SQL pattern match; true if a does not match b
REGEXP	a REGEXP b	Regular expression match; true if a matches b
NOT REGEXP	a NOT REGEXP b	Regular expression match; true if a does not match b
IS NULL	a IS NULL	True if operand is NULL
IS NOT NULL	a IS NOT NULL	True if operand is not NULL

For a discussion of the comparison properties of strings, see Section 3.1.2, “String Values.”

Pattern matching enables you to look for values without having to specify an exact literal value. MySQL provides SQL pattern matching using the LIKE operator and the wildcard characters ‘%’ (match any sequence of characters) and ‘_’ (match any single character). MySQL also provides pattern matching based on the REGEXP operator and regular expressions that are similar to those used in Unix programs such as grep, sed, and vi. You must use one of these pattern-matching operators to perform a pattern match; you cannot use the = operator. To reverse the sense of a pattern match, use NOT LIKE or NOT REGEXP.

The two types of pattern matching differ in important respects besides the use of different operators and pattern characters:

• LIKE is multi-byte safe. REGEXP works correctly only for single-byte character sets and does not take collation into account.
• LIKE SQL patterns match only if the entire string is matched. REGEXP regular expressions match if the pattern is found anywhere in the string.

Patterns used with the LIKE operator may include the ‘%’ and ‘_’ wildcard characters. For example, the pattern 'Frank%' matches any string that begins with 'Frank':

'Franklin' LIKE 'Frank%'                          → 1
'Frankfurter' LIKE 'Frank%'                       → 1

The wildcard character ‘%’ matches any sequence of characters, including the empty sequence, so 'Frank%' matches 'Frank':

'Frank' LIKE 'Frank%'                             → 1

This also means the pattern '%' matches any string, including the empty string. However, '%' will not match NULL. In fact, any pattern match with a NULL operand fails:

'Frank' LIKE NULL                                 → NULL
NULL LIKE '%'                                     → NULL

MySQL’s LIKE operator compares its operands as binary strings if either operand is a binary string. If the operands are non-binary strings, LIKE compares them according to their collation:

'Frankly' LIKE 'Frank%'                           → 1
'frankly' LIKE 'Frank%'                           → 1
BINARY 'Frankly' LIKE 'Frank%'                    → 1
BINARY 'frankly' LIKE 'Frank%'                    → 0
'Frankly' COLLATE latin1_general_cs LIKE 'Frank%' → 1
'frankly' COLLATE latin1_general_cs LIKE 'Frank%' → 0
'Frankly' COLLATE latin1_bin LIKE 'Frank%'        → 1
'frankly' COLLATE latin1_bin LIKE 'Frank%'        → 0

This behavior differs from that of the standard SQL LIKE operator, which is case sensitive.

The other wildcard character allowed with LIKE is ‘_’, which matches any single character. The pattern '___' matches any string of exactly three characters. 'c_t' matches 'cat', 'cot', 'cut', and even 'c_t' (because ‘_’ matches itself).

Wildcard characters may be specified anywhere in a pattern. '%bert' matches 'Englebert', 'Bert', and 'Albert'. '%bert%' matches all of those strings, and also strings like 'Berthold', 'Bertram', and 'Alberta'. 'b%t' matches 'Bert', 'bent', and 'burnt'.

To match literal instances of the ‘%’ or ‘_’ characters, turn off their special meaning by preceding them with a backslash (‘\%’ or ‘\_’):

'abc' LIKE 'a%c'                                  → 1
'abc' LIKE 'a\%c'                                 → 0
'a%c' LIKE 'a\%c'                                 → 1
'abc' LIKE 'a_c'                                  → 1
'abc' LIKE 'a\_c'                                 → 0
'a_c' LIKE 'a\_c'                                 → 1

MySQL’s other form of pattern matching uses regular expressions. The operator is REGEXP rather than LIKE. The following examples demonstrate several common regular expression pattern characters.

The ‘.’ character is a wildcard that matches any single character:

'abc' REGEXP 'a.c'                                → 1

The [...] construction matches any character listed between the square brackets.

'e' REGEXP '[aeiou]'                              → 1
'f' REGEXP '[aeiou]'                              → 0

You can specify a range of characters by listing the endpoints of the range separated by a dash (‘-’), or negate the sense of the class (to match any character not listed) by specifying ‘^’ as the first character of the class:

'abc' REGEXP '[a-z]'                              → 1
'abc' REGEXP '[^a-z]'                             → 0

‘*’ means “match any number of the previous thing,” so that, for example, the pattern 'x*' matches any number of ‘x’ characters:

'abcdef' REGEXP 'a.*f'                            → 1
'abc' REGEXP '[0-9]*abc'                          → 1
'abc' REGEXP '[0-9][0-9]*'                        → 0

“Any number” includes zero instances, which is why the second expression succeeds. To match one or more instances of the preceding thing rather than zero or more, use ‘+’ instead of ‘*’:

'abc' REGEXP 'cd*'                                → 1
'abc' REGEXP 'cd+'                                → 0
'abcd' REGEXP 'cd+'                               → 1

'^pattern' and 'pattern$' anchor a pattern match so that the pattern pattern matches only when it occurs at the beginning or end of a string, and '^pattern$' matches only if pattern matches the entire string:

'abc' REGEXP 'b'                                  → 1
'abc' REGEXP '^b'                                 → 0
'abc' REGEXP 'b$'                                 → 0
'abc' REGEXP '^abc$'                              → 1
'abcd' REGEXP '^abc$'                             → 0

MySQL’s regular expression matching has other special pattern elements as well. See Appendix C for more information.

A LIKE or REGEXP pattern can be taken from a table column, although this will be slower than a constant pattern if the column contains several different values. The pattern must be examined and converted to internal form each time the column value changes.

3.5.1.2 Operator Precedence

When MySQL evaluates an expression, it looks at the operators to determine the order in which it should group the terms of the expression. Some operators have higher precedence; that is, they are “stronger” than others in the sense that they are evaluated earlier than others. For example, multiplication and division have higher precedence than addition and subtraction. The following two expressions are equivalent because * and DIV are evaluated before + and -:

3 + 4 * 2 - 10 DIV 2                              → 6
3 + 8 - 5                                         → 6

Operator precedence is shown in the following list, from highest precedence to lowest. Operators listed on the same line have the same precedence. Operators at a higher precedence level are evaluated before operators at a lower precedence level. Operators at the same precedence level are evaluated left to right.

BINARY  COLLATE
!
- (unary minus)  ~ (unary bit negation)
^
*  /  DIV  %  MOD
+  -
<<  >>
&
|
<  <=  =  <=>  <>  !=  >=  >  IN  IS  LIKE  REGEXP  RLIKE
BETWEEN  CASE  WHEN  THEN  ELSE
NOT
AND  &&
XOR
OR  ||
:=

Some operators have a different precedence depending on the SQL mode or MySQL version. See Appendix C for details.

If you need to override the precedence of operators and change the order in which expression terms are evaluated, use parentheses to group terms:

1 + 2 * 3 - 4 / 5                                 → 6.2000
(1 + 2) * (3 - 4) / 5                             → -0.6000

3.5.1.3 NULL Values in Expressions

Take care when using NULL values in expressions, because the result may not always be what you expect. The following guidelines will help you avoid surprises.

If you supply NULL as an operand to any arithmetic or bit operator, the result is NULL:

1 + NULL                                          → NULL
1 | NULL                                          → NULL

With logical operators, the result is NULL unless the result can be determined with certainty:

1 AND NULL                                        → NULL
1 OR NULL                                         → 1
0 AND NULL                                        → 0
0 OR NULL                                         → NULL

NULL as an operand to any comparison or pattern-matching operator produces a NULL result, except for the <=>, IS NULL, and IS NOT NULL operators, which are intended specifically for dealing with NULL values:

1 = NULL                                          → NULL
NULL = NULL                                       → NULL
1 <=> NULL                                        → 0
NULL LIKE '%'                                     → NULL
NULL REGEXP '.*'                                  → NULL
NULL <=> NULL                                     → 1
1 IS NULL                                         → 0
NULL IS NULL                                      → 1

Functions generally return NULL if given NULL arguments, except for those functions designed to deal with NULL arguments. For example, IFNULL() is able to handle NULL arguments and returns true or false appropriately. On the other hand, STRCMP() expects non-NULL arguments; if you pass it a NULL argument, it returns NULL rather than true or false.

In sorting operations, NULL values group together. They appear first in ascending sorts and last in descending sorts.

3.5.2 Type Conversion

Whenever a value of one type is used in a context that requires a value of another type, MySQL performs type conversion automatically according to the kind of operation you’re performing. Conversion may occur for any of the following reasons:

• Conversion of operands to a type appropriate for evaluation of an operator
• Conversion of a function argument to a type expected by the function
• Conversion of a value for assignment into a table column that has a different type

You can also perform explicit type conversion using a cast operator or function.

The following expression involves implicit type conversion. It consists of the addition operator + and two operands, 1 and '2':

1 + '2'                                           → 3

The operands are of different types (number and string), so MySQL converts one of them to make them the same type. But which one should it change? In this case, + is a numeric operator, so MySQL wants the operands to be numbers thus and converts the string '2' to the number 2. Then it evaluates the expression to produce the result 3.

Here’s another example. The CONCAT() function concatenates strings to produce a longer string as a result. To do this, it interprets its arguments as strings, no matter what type they are. If you pass it a bunch of numbers, CONCAT() converts them to strings, and then returns their concatenation:

CONCAT(1,23,456)                                  → '123456'

If the call to CONCAT() is part of a larger expression, further type conversion may take place. Consider the following expression and its result:

REPEAT('X',CONCAT(1,2,3)/10)                      → 'XXXXXXXXXXXX'

CONCAT(1,2,3) produces the string '123'. The expression '123'/10 is converted to 123/10 because division is an arithmetic operator. The result of this expression is 12.3, but REPEAT() expects an integer repeat count, so the count is rounded to produce 12. Then REPEAT('X',12) produces a string result of 12 ‘X’ characters.

If all arguments to CONCAT() are non-binary strings, the result is a non-binary string. If any argument is a binary string, the result is a binary string. The latter principle includes the case of numeric arguments, which are converted to binary strings. These examples both appear to produce the same result:

CONCAT('1','23')                                  → '123'
CONCAT(1,'23')                                    → '123'

But if you check the result with CHARSET(), you can see that the expressions return a non-binary and binary string, respectively:

CHARSET(CONCAT('1','23'))                         → 'latin1'
CHARSET(CONCAT(1,'23'))                           → 'binary'

A general principle to keep in mind is that, by default, MySQL attempts to convert values to the type required by an expression rather than generating an error. Depending on the context, it converts values of each of the three general categories (numbers, strings, or dates and times) to values in any of the other categories. However, values can’t always be converted from one type to another. If a value to be converted to a given type doesn’t look like a legal value for that type, the conversion fails. Conversion to numbers of things like 'abc' that don’t look like numbers results in a value of 0. Conversion to date or time types of things that don’t look like a date or time result in the “zero” value for the type. For example, converting the string 'abc' to a date results in the “zero” date '0000-00-00'. On the other hand, any value can be treated as a string, so generally it’s not a problem to convert a value to a string.

If you want to prevent conversion of illegal values to the closest legal values during data input operations, you can enable strict mode to cause errors to occur instead. See Section 3.3, “How MySQL Handles Invalid Data Values.”

MySQL also performs more minor type conversions. If you use a floating-point value in an integer context, the value is converted (with rounding). Conversion in the other direction works as well; an integer can be used without problem as a floating-point number.

Hexadecimal constants are treated as binary strings unless the context clearly indicates a number. In string contexts, each pair of hexadecimal digits is converted to a character and the result is used as a string. The following examples illustrate how this works:

0x61                                              → 'a'
0x61 + 0                                          → 97
X'61'                                             → 'a'
X'61' + 0                                         → 97
CONCAT(0x61)                                      → 'a'
CONCAT(0x61 + 0)                                  → '97'
CONCAT(X'61')                                     → 'a'
CONCAT(X'61' + 0)                                 → '97'

For comparisons, context determines whether to treat a hexadecimal constant as a binary string or a number:

• This expression treats the operands as binary strings and performs a byte-by-byte comparison.

  0x0d0a = '\r\n'                                   → 1

• This expression compares a hexadecimal constant to a number, so it is converted to a number for the comparison.

  0x0a = 10                                         → 1

• This expression performs a binary string comparison. The first byte of the left operand has a lesser value than the first byte of the right operand, so the result is false.

  0xee00 > 0xff                                     → 0

• In this expression, the right operand hex constant is converted to a number because of the arithmetic operator. Then for the comparison, the left operand is converted to a number. The result is false because 0xee00 (60928) is not numerically less than 0xff (255).

  0xee00 > 0xff+0                                   → 1

It’s possible to force a hexadecimal constant to be treated as a non-binary string by using a character set introducer or CONVERT():

0x61                                              → 'a'
0x61 = 'A'                                        → 0
_latin1 0x61 = 'A'                                → 1
CONVERT(0x61 USING latin1) = 'A'                  → 1

Some operators force conversion of the operands to the type expected by the operator, no matter what the type of the operands is. Arithmetic operators are an example of this. They expect numbers, and the operands are converted accordingly:

3 + 4                                             → 7
'3' + 4                                           → 7
'3' + '4'                                         → 7

In a string-to-number conversion, it’s not enough for a string simply to contain a number somewhere. MySQL doesn’t look through the entire string hoping to find a number, it looks only at the beginning; if the string has no leading numeric part, the conversion result is 0.

'1973-2-4' + 0                                    → 1973
'12:14:01' + 0                                    → 12
'23-skidoo' + 0                                   → 23
'-23-skidoo' + 0                                  → -23
'carbon-14' + 0                                   → 0

MySQL’s string-to-number conversion rule converts numeric-looking strings to floating-point values:

'-428.9' + 0                                      → -428.9
'3E-4' + 0                                        → 0.0003

This conversion does not work for hexadecimal-looking constants, though. Only the leading zero is used:

'0xff' + 0                                        → 0

The bit operators are even stricter than the arithmetic operators. They want the operators to be not just numeric, but integers, and type conversion is performed accordingly. This means that a fractional number such as 0.3 is not considered true, even though it’s non-zero; that’s because when it’s converted to an integer, the result is 0. In the following expressions, the operands are not considered true until they have a value of at least 1:

0.3 | .04                                         → 0
1.3 | .04                                         → 1
0.3 & .04                                         → 0
1.3 & .04                                         → 0
1.3 & 1.04                                        → 1

Pattern matching operators expect to operate on strings. This means you can use MySQL’s pattern matching operators on numbers because it will convert them to strings in the attempt to find a match!

12345 LIKE '1%'                                   → 1
12345 REGEXP '1.*5'                               → 1

The magnitude comparison operators (<, <=, =, and so on) are context sensitive; that is, they are evaluated according to the types of their operands. The following expression compares the operands numerically because they both are numbers:

2 < 11                                            → 1

This expression involves string operands and thus results in a lexical comparison:

'2' < '11'                                        → 0

In the following comparisons, the types are mixed, so MySQL compares them as numbers. As a result, both expressions are true:

'2' < 11                                          → 1
2 < '11'                                          → 1

When evaluating comparisons, MySQL converts operands as necessary according to the following rules:

• Other than for the <=> operator, comparisons involving NULL values evaluate as NULL. (<=> is like =, except that NULL <=> NULL is true, whereas NULL = NULL is NULL.)
• If both operands are strings, they are compared lexically as strings. Binary strings are compared on a byte-by-byte basis using the numeric value of each byte. Comparisons for non-binary strings are performed character-by-character using the collating sequence of the character set in which the strings are expressed. If the strings have different character sets, the comparison may result in an error or fail to yield meaningful results. A comparison between a binary and a non-binary string is treated as a comparison of binary strings.
• If both operands are integers, they are compared numerically as integers.
• Hexadecimal constants that are not compared to a number are compared as binary strings.
• Other than for IN(), if either operand is a TIMESTAMP or DATETIME value and the other is a constant, the operands are compared as TIMESTAMP values. This is done to make comparisons work better for ODBC applications.
• Otherwise, the operands are compared numerically as double-precision floating-point values. Note that this includes the case of comparing a string and a number. The string is converted to a double-precision number, which results in a value of 0 if the string doesn’t look like a number. For example, '14.3' converts to 14.3, but 'L4.3' converts to 0.

3.5.2.1 Date and Time Interpretation Rules

MySQL freely converts strings and numbers to date and time values as demanded by context in an expression, and vice versa. Date and time values are converted to numbers in numeric context; numbers are converted to dates or times in date or time contexts. This conversion to a date or time value happens when you assign a value to a date or time column or when a function requires a date or time value. In comparisons, the general rule is that date and time values are compared as strings.

If the table mytbl contains a DATE column date_col, the following statements are equivalent:

INSERT INTO mytbl SET date_col = '2025-04-13';
INSERT INTO mytbl SET date_col = '20250413';
INSERT INTO mytbl SET date_col = 20250413;

In the following examples, the argument to the TO_DAYS() function is interpreted as the same value for all three expressions:

TO_DAYS('2025-04-13')                             → 739719
TO_DAYS('20250413')                               → 739719
TO_DAYS(20250413)                                 → 739719

3.5.2.2 Testing and Forcing Type Conversion

To see how type conversion will be handled in an expression, issue a SELECT query that evaluates the expression so that you can examine the result:

mysql> SELECT X'41', X'41' + 0;
+-------+-----------+
| X'41' | X'41' + 0 |
+-------+-----------+
| A     |        65 |
+-------+-----------+

If you cannot tell from inspection the type of an expression, select it into a new table and check the table definition:

mysql> CREATE TABLE t SELECT X'41' AS col1, X'41' + 0 AS col2;
mysql> DESCRIBE t;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| col1  | varbinary(1) | NO   |     |         |       |
| col2  | double(17,0) | NO   |     | 0       |       |
+-------+--------------+------+-----+---------+-------+

Testing expression evaluation is especially useful for statements such as DELETE or UPDATE that modify rows, because you want to be sure you’re affecting only the intended rows. One way to check an expression is to run a preliminary SELECT statement with the same WHERE clause that you’re going to use with the DELETE or UPDATE statement to verify that the clause selects the proper rows. Suppose that the table mytbl has a CHAR column char_col containing these values:

'abc'
'00'
'def'
'00'
'ghi'

Given these values, what is the effect of the following statement?

DELETE FROM mytbl WHERE char_col = 00;

The intended effect is probably to delete the two rows containing the value '00'. The actual effect would be to delete all the rows—an unpleasant surprise. This happens as a consequence of MySQL’s comparison rules. char_col is a string column, but 00 in the statement is not quoted, so it is treated as a number. By MySQL’s comparison rules, a comparison involving a string and a number evaluates as a comparison of two numbers. As MySQL executes the DELETE statement, it converts each value of char_col to a number and compares it to 0. Unfortunately, although '00' converts to 0, so do all the strings that don’t look like numbers. As a result, the WHERE clause is true for every row, and the DELETE statement empties the table. This is a case where it would have been prudent to test the WHERE clause with a SELECT statement prior to executing the DELETE, because that would have shown you that too many rows are selected by the expression:

mysql> SELECT char_col FROM mytbl WHERE char_col = 00;
+----------+
| char_col |
+----------+
| abc      |
| 00       |
| def      |
| 00       |
| ghi      |
+----------+

When you’re uncertain about the way a value will be used, you may want to exploit MySQL’s type conversion to force an expression to a value of a particular type, or to call a function that performs the desired conversion. The following list demonstrates several useful conversion techniques.

Add +0 or +0.0 to a term to force conversion to a numeric value:

0x65                                              → 'e'
0x65 + 0                                          → 101
0x65 + 0.0                                        → 101.0

To chop off the fractional part of a number, use FLOOR() or CAST(). To add a fractional part to an integer, add an exact-value zero with the required number of decimal digits:

FLOOR(13.3)                                       → 13
CAST(13.3 AS SIGNED)                              → 13
13 + 0.0                                          → 13.0
13 + 0.0000                                       → 13.0000

If you want rounding instead, use ROUND() rather than CAST().

Use CAST() or CONCAT() to turn a value into a string:

14                                                → 14
CAST(14 AS CHAR)                                  → '14'
CONCAT(14)                                        → '14'

CONCAT() returns a binary string if it must convert a numeric argument to string form, so the final two examples actually differ in their result. The CAST() expression returns a non-binary string, whereas the CONCAT() expression returns a binary string.

Use HEX() to convert a number to a hexadecimal string:

HEX(255)                                          → 'FF'
HEX(65535)                                        → 'FFFF'

You can also use HEX() with a string value to convert it to a string of hex digit pairs representing successive bytes in the string:

HEX('abcd');                                      → '61626364'

Use ASCII() to convert a single-byte character to its ASCII value:

'A'                                               → 'A'
ASCII('A')                                        → 65

To go in the other direction from ASCII code to character, use CHAR():

CHAR(65)                                          → 'A'

Use DATE_ADD() or INTERVAL arithmetic to force a string or number to be treated as a date:

DATE_ADD(20080101, INTERVAL 0 DAY)                → '2008-01-01'
20080101 + INTERVAL 0 DAY                         → '2008-01-01'
DATE_ADD('20080101', INTERVAL 0 DAY)              → '2008-01-01'
'20080101' + INTERVAL 0 DAY                       → '2008-01-01'

Generally, you can convert a date value to numeric form by adding zero:

CURDATE()                                         → '2007-09-07'
CURDATE()+0                                       → 20070907

Temporal values with a time part convert to a value with a microseconds part:

NOW()                                             → '2007-09-07 16:15:29'
NOW()+0                                           → 20070907161529.000000
CURTIME()                                         → '16:15:29'
CURTIME()+0                                       → 161529.000000

To chop off the fractional part, cast the value to an integer:

CAST(NOW() AS UNSIGNED)                           → 20070907161529
CAST(CURTIME() AS UNSIGNED)                       → 161529

To convert a string from one character set to another, use CONVERT(). To check whether the result has the desired character set, use the CHARSET() function:

'abcd'                                            → 'abcd'
CONVERT('abcd' USING ucs2)                        → 'abcd'
CHARSET('abcd')                                   → 'latin1'
CHARSET(CONVERT('abcd' USING ucs2))               → 'ucs2'

Preceding a string with a character set introducer does not cause conversion of the string, but MySQL interprets it as though it has the character set indicated by the introducer:

CHARSET(_ucs2 'abcd')                             → 'ucs2'

To determine the hexadecimal value of the UTF-8 character that corresponds to a given hexadecimal UCS-2 character, combine CONVERT() with HEX(). The following expression determines the UTF-8 value of the trademark symbol:

HEX(CONVERT(_ucs2 0x2122 USING utf8))             → 'E284A2'

To change the collation of a string, use the COLLATE operator. To check whether the result has the desired collation, use the COLLATION() function:

COLLATION('abcd')                                 → 'latin1_swedish_ci'
COLLATION('abcd' COLLATE latin1_bin)              → 'latin1_bin'

The character set and collation must be compatible. If they are not, use a combination of CONVERT() to convert the character set first and COLLATE to change the collation:

CONVERT('abcd' USING latin2) COLLATE latin2_bin

To convert a binary string to a non-binary string that has a given character set, use CONVERT():

0x61626364                                        → 'abcd'
0x61626364 = 'ABCD'                               → 0
CONVERT(0x61626364 USING latin1) = 'ABCD'         → 1

Alternatively, for binary quoted strings or hexadecimal values, use an introducer to change the interpretation of the binary string:

_latin1 0x61626364 = 'ABCD'                       → 1

To cast a non-binary string to a binary string, use the BINARY keyword:

'abcd' = 'ABCD'                                   → 1
BINARY 'abcd' = 'ABCD'                            → 0
'abcd' = BINARY 'ABCD'                            → 0