Oracle 5.0, Справочное руководство

Страница: 892 / 3232

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