Operators
872
The following examples illustrate conversion of strings to numbers for comparison operations:
mysql>
SELECT 1 > '6x';
-> 0
mysql>
SELECT 7 > '6x';
-> 1
mysql>
SELECT 0 > 'x6';
-> 0
mysql>
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;
-> 1
mysql>
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.801537616
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()
[948]
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”
.
Implicit conversion of a numeric or temporal value to a string produces a binary string (a
BINARY
,
VARBINARY
, or
BLOB
value). Such implicit conversions to string typically occur for functions that are
passed numeric or temporal values when string values are more usual, and thus can have effects
beyond the type of the converted value. Consider the expression
CONCAT(1, 'abc')
[887]
. The
numeric argument
1
is converted to the binary string
'1'
and the concatenation of that value with the
nonbinary string
'abc'
produces the binary string
'1abc'
.
12.3. Operators
Table 12.2. Operators
Name
Description
AND
,
&&
[880]
Logical AND
Summary of Contents for 5.0
Page 1: ...MySQL 5 0 Reference Manual ...
Page 18: ...xviii ...
Page 60: ...40 ...
Page 396: ...376 ...
Page 578: ...558 ...
Page 636: ...616 ...
Page 844: ...824 ...
Page 1234: ...1214 ...
Page 1427: ...MySQL Proxy Scripting 1407 ...
Page 1734: ...1714 ...
Page 1752: ...1732 ...
Page 1783: ...Configuring Connector ODBC 1763 ...
Page 1793: ...Connector ODBC Examples 1773 ...
Page 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Page 2850: ...2830 ...
Page 2854: ...2834 ...
Page 2928: ...2908 ...
Page 3000: ...2980 ...
Page 3122: ...3102 ...
Page 3126: ...3106 ...
Page 3174: ...3154 ...
Page 3232: ...3212 ...
