Expression Evaluation and Type Conversion

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

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

Each column selected 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 on.

When MySQL encounters an expression, it evaluates it to produce a result. For example, (4*3)/(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 discussed. For more information, see Appendix C.

Writing Expressions

An expression can be as simple as a single constant:

 0       Numeric constant 'abc'   String constant

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 function, there can be spaces around arguments, but there must be no space between the function name and the opening parenthesis:^[5]

^[5] Actually, you can tell MySQL to allow spaces after function names by starting the server with the --ansi or --sql-mode=IGNORE_SPACE option. However, this causes function names to be treated as reserved words.

 NOW()                   Function with no arguments  STRCMP('abc','def')     Function with two arguments STRCMP( 'abc', 'def' )  Spaces around arguments are legal STRCMP ('abc','def')    Space after function name is illegal

If there is a space after the function name, the MySQL parser may interpret the function name as a column name. (Function names are not reserved words, and you can use them for column names if you want.) The usual result is a syntax error.

You can use table column values in expressions. In the simplest case, when the table to which a column belongs is clear from context, a column reference can 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, column names can be preceded by the table name. If it's not clear which database should be used, the table name can be preceded by the database name. You can also use these more-specific 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, member  WHERE president.last_name = member.last_name; SELECT sampdb.student.name FROM sampdb.student;

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

Operator Types

MySQL includes several kinds of operators that can be used to combine terms of expressions. Arithmetic operators, listed in Table 2.16, 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, as well as for / and % when the operation is performed in a context where the result is expected to be an integer. Otherwise, DOUBLE is used. 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 2.16. 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
`%`	`a % b`	Modulo; remainder after division of operands

Logical operators, shown in Table 2.17, 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). MySQL allows the C-style &&, ||, and ! operators as alternative forms of AND, OR, and NOT. Note in particular the || operator; ANSI SQL specifies || as the string concatenation operator, but in MySQL it signifies a logical OR operation.^[6]

^[6] If you want the ANSI behavior for ||, start the server with the --ansi or --sql-mode=PIPES_AS_CONCAT option.

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

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

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

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

Bit operators, shown in Table 2.18, 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. (The XOR and ^ exclusive-OR operators are not available until MySQL 4.0.2.) You can also perform bit shifts left or right. Bit operations are performed using BIGINT (64-bit) integer values.

Table 2.18. 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 2.19, 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 and was introduced in MySQL 3.23.

Table 2.19. 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`

The BINARY operator is available as of MySQL 3.23 and can be used to cast (convert) a string to a binary string. Generally, this is done to render a string case sensitive in comparison or sorting operations. The first of the following comparisons is not case sensitive, but the second and third ones are:

 'abc' = 'Abc'                                      1  BINARY 'abc' = 'Abc'                               0 'abc' = BINARY 'Abc'                               0

There is no corresponding NOT BINARY cast. If you expect to use a column both in case-sensitive and in not case-sensitive contexts, use a column type that is not case sensitive and use BINARY for those comparisons that you want to be case sensitive. Alternatively, for a column that is case sensitive, you can use it in a comparison that is not case sensitive by converting both operands to the same lettercase with UPPER() or LOWER():

 UPPER(col_name) < UPPER('Smith')  LOWER(col_name) < LOWER('Smith')

For string comparisons that are not case sensitive, it is possible that multiple characters will be considered equivalent, depending on your character set. For example, 'E' and 'É' might be treated the same for comparison and ordering operations. Binary (case sensitive) comparisons are done using the numeric codes of successive bytes in the values.

Pattern matching allows 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 not case sensitive unless at least one operand is a binary string. This is also true for REGEXP, except that prior to MySQL 3.23.4, REGEXP is always case sensitive.
SQL patterns match only if the entire string is matched. Regular expressions match if the pattern is found anywhere in the string.

Patterns used with the LIKE operator can 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 is not case sensitive unless one of its operands is a binary string. Thus, 'Frank%' matches both of the strings 'Frankly' and 'frankly' by default, but matches only one of them in a binary comparison:

 'Frankly' LIKE 'Frank%'                            1  'frankly' LIKE 'Frank%'                            1 BINARY 'Frankly' LIKE 'Frank%'                     1 BINARY 'frankly' LIKE 'Frank%'                     0

This differs from the ANSI SQL LIKE operator, which is case sensitive.

The wildcard character can be specified anywhere in the 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'.

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

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 most common regular expression pattern characters are as follows:

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.

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

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

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

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

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 / are evaluated before + and -:

 1 + 2 * 3 - 4 / 5                                  6.2 1 + 6 - .8                                         6.2

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

You can use parentheses to override the precedence of operators and change the order in which expression terms are evaluated:

 1 + 2 * 3 - 4 / 5                                  6.2 (1 + 2) * (3 - 4) / 5                              -0.6

NULL Values in Expressions

Take care when you use 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.^[7]

^[7] Prior to MySQL 3.23.9, NULL is treated as a false value with logical operators; this behavior may be considered a bug.

 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 it discovers you've passed it a NULL argument, it returns NULL rather than true or false.

In sorting operations, NULL values group together. However, whether they sort before or after non-NULL values is version dependent, as discussed in the "Sorting Query Results" section in Chapter 1.

Type Conversion

Whenever a value of one type is used in a context that requires a value of another type, MySQL performs extensive type conversion automatically according to the kind of operation you're performing. Type conversion can 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'

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; MySQL wants the operands to be numbers 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() will convert them to strings and then return their concatenation:

 CONCAT(1,2,3)                                      '123'

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 would be 12.3 in floating-point context, but REPEAT() expects an integer repeat count, so an integer division is performed to produce 12. Then REPEAT('X',12) produces a string result of 12 'X' characters.

A general principle to keep in mind is that MySQL attempts to convert values to the type required by an expression rather than generating an error. Depending on the context, it will convert 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 it's generally not a problem to convert a value to a string.

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 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'

In comparisons, treatment of hexadecimal constants depends on your version of MySQL. From MySQL 3.23.22 and later, hex constants in comparisons are treated as numbers:

 0x0a = '\n'                                        0 0xaaab < 0xab                                      0 0xaaab > 0xab                                      1 0x0a = 10                                          1

Prior to MySQL 3.23.22, hex constants are treated as binary strings unless compared to a number. Thus, several of the preceding comparisons have a different result when executed under older servers:

 0x0a = '\n'                                        1 0xaaab < 0xab                                      1 0xaaab > 0xab                                      0 0x0a = 10                                          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 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

Be aware that MySQL's string-to-number conversion rule changed as of version 3.23. Currently, numeric-looking strings are converted to floating-point values. Prior to 3.23, they are converted to integer values, with rounding:

 '-428.9' + 0                                       -428.9 (MySQL > 3.23)  '-428.9' + 0                                       -429   (MySQL < 3.23)

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

 0.3 OR .04                                         0 1.3 OR .04                                         1 0.3 AND .04                                        0 1.3 AND .04                                        0 1.3 AND 1.04                                       1

This type of conversion also occurs with the IF() function, which expects the first argument to be an integer. This means that values that round to zero will be considered false:

 IF(1.3,'non-zero','zero')                          'non-zero'  IF(0.3,'non-zero','zero')                          'zero' IF(-0.3,'non-zero','zero')                         'zero' IF(-1.3,'non-zero','zero')                         'non-zero'

To test floating-point values properly, it's best to use an explicit comparison:

 IF(0.3>0,'non-zero','zero')                        'non-zero'

Pattern matching operators expect to operate on strings. This means that 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 are both 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.)
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 (as is possible as of MySQL 4.1), the comparison may not 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.
As of MySQL 3.23.22, hexadecimal constants are compared as numbers. Before that, hex constants that are not compared to a number are compared as binary strings.
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 floating-point values. Note that this includes the case of comparing a string and a number. The string is converted to a 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.

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 = '2004-04-13';  INSERT INTO mytbl SET date_col = '20040413'; INSERT INTO mytbl SET date_col = 20040413;

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

 TO_DAYS('2004-04-10')                              732046  TO_DAYS('20040410')                                732046 TO_DAYS(20040410)                                  732046

Testing and Forcing Type Conversion

To see how type conversion will be handled in an expression, use the mysql program to issue a SELECT query that evaluates the expression:

 mysql> SELECT 0x41, 0x41 + 0;  +------+----------+ | 0x41 | 0x41 + 0 | +------+----------+ | A    |       65 | +------+----------+

As you might imagine, I did quite a bit of that sort of thing while writing this chapter!

Testing expression evaluation is especially important for statements such as DELETE or UPDATE that modify records 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 the table mytbl has a CHAR column char_col containing the following values:

 'abc'  'def' '00' 'ghi' 'jkl' '00' 'mno'

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 is 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 is evaluated as a comparison of two numbers. As the DELETE statement is performed, each value of char_col is converted to a number and compared 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. Obviously, 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'    | | 'def'    | | '00'     | | 'ghi'    | | 'jkl'    | | '00'     | | 'mno'    | +----------+

When you're uncertain about the way a value will be used, you may want to exploit MySQL's expression evaluation mechanism to force conversion of a value to a particular type or to call a function that performs the desired conversion:

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

Use FLOOR() to convert a floating-point number to an integer, or add +0.0 to convert an integer to a floating-point number:

 FLOOR(13.3)                                        13  13 + 0.0                                           13.0

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

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

 14                                                 14 CONCAT(14)                                         '14'

Or (as of MySQL 4.0.2), 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('abc');                                        '616263'

Use ASCII() to convert a 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() to force a string or number to be treated as a date:

 20030101                                           20030101 DATE_ADD(20030101, INTERVAL 0 DAY)                 '2003-01-01' '20030101'                                         '20030101' DATE_ADD('20030101', INTERVAL 0 DAY)               '2003-01-01'

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

 CURDATE()                                          '2002-09-18'  CURDATE()+0                                        20020918 CURTIME()                                          '12:05:41' CURTIME()+0                                        120541

In MySQL 4.1 and later, you can convert a string from one character set to another by using CONVERT() or by prepending a character set identifier to the string:

 'abc'                                              'abc'  CONVERT('abc' USING ucs2)                          '\0a\0b\0c' CHARSET('abc')                                     'latin1' CHARSET(CONVERT('abc' USING ucs2))                 'ucs2' CHARSET(_ucs2 'abc')                               'ucs2'

Conversion of Out-of-Range or Illegal Values

The basic principle is this: Garbage in, garbage out. If you don't verify your data first before storing it, you may not like what you get. Having said that, the following are some general principles that describe MySQL's handling of out-of-range or otherwise improper values:

For numeric or TIME columns, values that are outside the legal range are clipped to the nearest endpoint of the range and the resulting value is stored.
For date and time columns other than TIME, values that are outside the range for the type may be converted to the "zero" value, NULL, or some other value. (In other words, the results are unpredictable.)
For string columns other than ENUM or SET, strings that are too long are truncated to fit the maximum length of the column. Assignments to an ENUM or SET column depend on the values that are listed as legal in the column definition. If you assign to an ENUM column a value that is not listed as an enumeration member, the error member is assigned instead (that is, the empty string that corresponds to the zero-valued member). If you assign to a SET column a value containing substrings that are not listed as set members, those strings drop out and the column is assigned a value consisting of the remaining members.
For date or time columns, illegal values are converted to the appropriate "zero" value for the type (see Table 2.12).

These conversions are reported as warnings for ALTER TABLE, LOAD DATA, UPDATE, INSERT INTO ... SELECT, and multiple-row INSERT statements. In the mysql client, this information is displayed in the status line that is reported for a query. In a programming language, you may be able to get this information by some other means. If you're using the MySQL C or PHP APIs, you can invoke the mysql_info() function. With the Perl DBI API, you can use the mysql_info attribute of your database connection. The information provided is a count of the number of warnings.

Writing Expressions

Operator Types

Table 2.16. Arithmetic Operators

Table 2.17. Logical Operators