# 分析

The following rules describe how conversion occurs for comparison operations: .... 省略一万字 .... In all other cases, the arguments are compared as floating-point (real) numbers.

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 '170325171202362928' = 170325171202362930;+-------------------------------------------+| '170325171202362928' = 170325171202362930 |+-------------------------------------------+|                                         1 |+-------------------------------------------+1 row in set (0.00 sec)```

``````mysql  > select '170325171202362928'+0.0;+--------------------------+| '170325171202362928'+0.0 |+--------------------------+|    1.7032517120236294e17 |+--------------------------+1 row in set (0.00 sec) mysql > select '170325171202362930'+0.0;+--------------------------+| '170325171202362930'+0.0 |+--------------------------+|    1.7032517120236294e17 |+--------------------------+1 row in set (0.00 sec)

``````mysql > select '170325171202362931'+0.0;+--------------------------+| '170325171202362931'+0.0 |+--------------------------+|    1.7032517120236294e17 |+--------------------------+1 row in set (0.00 sec) mysql > select '170325171202362941'+0.0;+--------------------------+| '170325171202362941'+0.0 |+--------------------------+|    1.7032517120236294e17 |+--------------------------+1 row in set (0.00 sec)

``````mysql > select '170325171202362931' = 170325171202362930;+-------------------------------------------+| '170325171202362931' = 170325171202362930 |+-------------------------------------------+|                                         1 |+-------------------------------------------+1 row in set (0.00 sec) mysql > select '170325171202362941' = 170325171202362930;+-------------------------------------------+| '170325171202362941' = 170325171202362930 |+-------------------------------------------+|                                         1 |+-------------------------------------------+1 row in set (0.00 sec)

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

``````mysql > create table tbl_name(id int ,str_col varchar(10),c3 varchar(5),primary key(id),key idx_str(str_col));Query OK, 0 rows affected (0.02 sec) mysql  > insert into tbl_name(id,str_col) values(1,'a'),(2,'b');Query OK, 2 rows affected (0.01 sec)Records: 2  Duplicates: 0  Warnings: 0 mysql  > insert into tbl_name(id,str_col) values(3,'3c'),(4,'4d');Query OK, 2 rows affected (0.00 sec)Records: 2  Duplicates: 0  Warnings: 0 mysql  > desc select * from tbl_name where str_col='a';+----+-------------+----------+------+---------------+---------+---------+-------+------+--------------------------+| id | select_type | table    | type | possible_keys | key     | key_len | ref   | rows | Extra                    |+----+-------------+----------+------+---------------+---------+---------+-------+------+--------------------------+|  1 | SIMPLE      | tbl_name | ref  | idx_str       | idx_str | 13      | const |    1 | Using where; Using index |+----+-------------+----------+------+---------------+---------+---------+-------+------+--------------------------+ mysql  > desc select * from tbl_name where str_col=3;+----+-------------+----------+------+---------------+------+---------+------+------+-------------+| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra       |+----+-------------+----------+------+---------------+------+---------+------+------+-------------+|  1 | SIMPLE      | tbl_name | ALL  | idx_str       | NULL | NULL    | NULL |    4 | Using where |+----+-------------+----------+------+---------------+------+---------+------+------+-------------+1 row in set (0.00 sec) mysql [localhost] {msandbox} (test) > select * from tbl_name where str_col=3;+----+---------+------+| id | str_col | c1   |+----+---------+------+|  3 | 3c      | NULL |+----+---------+------+1 row in set, 2 warnings (0.00 sec)

``````mysql  > show warnings;+---------+------+----------------------------------------+| Level   | Code | Message                                |+---------+------+----------------------------------------+| Warning | 1292 | Truncated incorrect DOUBLE value: '3c' || Warning | 1292 | Truncated incorrect DOUBLE value: '4d' |+---------+------+----------------------------------------+

MySQL针对3c 和 4d这两个值进行了转化，变成了3和4