Expression & Operators

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

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

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.

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.

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.

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.