MySQL——用数值类型查询varchar字段

comet_

已于 2022-09-06 13:49:57 修改

阅读量1.9k

点赞数 1

分类专栏：数据库文章标签： mysql 数据库

于 2022-09-05 11:36:14 首次发布

本文链接：https://blog.csdn.net/comet_/article/details/126701691

版权

数据库专栏收录该内容

1 篇文章 0 订阅

订阅专栏

问题描述：

一个简单的查询语句，查询时间太长。导致同步数据时部分数据丢失，抓取数据时响应时间太慢。

在这里插入图片描述

问题分析：

使用explain分析查询语句：possible_keys命中cycle索引，但实际查询的时候都没有使用到索引。查询的时候还是全表查询了，salary_records表数据量三百多万条数据，整个查询时间长。

列	值	描述
select_type	SIMPLE	不包含子查询和union的简单查询
table	salary_records	查询的表名称
type	ALL	关联类型或者访问类型最优到最差：system>const>eq_ref>ref>range>index>all
possible_keys	cycle	查询中可能要用到的索引
key		MySQL实际查询用到的索引
key_len		显示MySQL在索引里使用的字节数此列可以算出具体使用了索引中的那些列
ref		显示key列记录的索引中，表查找时使用到的列或常量
rows	2529812	MySQL在查询中估计要读取的行数
Extra	Using where	额外信息 1. Using index：使用覆盖索引 2. Using where：使用where语句来处理结果，并且查询的列未被索引覆盖 3. Using index condition：查询的列不完全被索引覆盖，where条件中是一个查询的范围 4. Using temporary： MySQL需要创建一张临时表来处理查询。（需要优化） 5. Using filesort：使用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序 6. Select tables optimized away：使用某些聚合参数（max、min）来访问存在索引的某个字段

查看了salary_records表设计其中 where 查询条件中 cycle_id 用varchar字段存储的，而查询值是数值类型。
在这里插入图片描述

将查询值用字符串查询时，查询耗时明显提升了。

参考官方文档

规则描述了比较操作如何进行转换：

如果一个或者两个参数是NULL，则比较的结果是NULL，但NULL-safe <=>相等比较运算符除外，对于NULL<=>NULL，结果为真。无需转换。
如果比较操作中的两个参数都是字符串，则将他们作为字符串进行比较。
如果两个参数都是整数，则将他们作为整数进行比较。
如果不与数字比较，十六进制值将被视为二进制字符串。
如果其中一个参数是 a TIMESTAMP列，而另一个参数是常量，则在执行比较之前将常量转换为时间戳。
如果其中一个参数是十进制值，则比较取决于另一个参数。如果另一个参数是十进制或整数值，则将参数作为十进制进行比较，如果另一个参数是浮点值，则将其作为浮点值进行比较。
在所有其它情况下，参数将作为浮点（双精度）数字进行比较。字符串和数字操作数的比较是作为浮点数的进行比较的

示例说明

字符串转换为数字进行比较操作：

mysql> SELECT 1 > '6x';
        -> 0
mysql> SELECT 7 > '6x';
        -> 1
mysql> SELECT 0 > 'x6';
        -> 0
mysql> SELECT 0 = 'x6';
        -> 1

字符串与数字比较，Mysql不能使用列上的索引来快速查找值。如果 *str_col*是索引字符串列，则在以下语句中执行查找时不能使用索引。
```
SELECT * FROM tbl_name WHERE str_col=1;
```
许多不同的字符串可以转换为数值1，例如 ‘1’, ’ 1’或’1a’。(字符串开头是数字，就一直截取，直到取到的不是数字是其他字符为止，若开头不是数字，转换数值类型的结果就直接取0)
```
SELECT '1' + 1 as value; --> 2
SELECT '1a' + 1 as value; --> 2
SELECT 'a1' + 1 as value; --> 1
SELECT ' 1' + 1 as value; --> 2
SELECT ' 1 ' + 1 as value; --> 2
```
浮点数和大INTEGER类型值之间的比较是近似的，因为整数在比较之前转换为双精度浮点数，它不能准确地表示所有的64位整数。例如，整数值2^53 + 1不能表示为浮点数，并且在浮点比较之前四舍五入为2^53或253+2，具体取决于平台。
```
mysql> SELECT '9223372036854775807' = 9223372036854775807;
        -> 1
mysql> SELECT '9223372036854775807' = 9223372036854775806;
        -> 1
```
当发生从字符串到浮点以及从整数到浮点的转换时，它们的发生方式不一定相同，CPU可以将整数转换为浮点数，而字符串在涉及浮点乘法的运算中逐位转换。此外，结果可能会收到计算机体系结构或编译器版本或优化级别等因素的影响。避免此类问题的一种方法是使用CAST()使值不会隐式转换为浮点数。
```
mysql> SELECT CAST('9223372036854775807' AS UNSIGNED) = 9223372036854775806;
        -> 0
```

总结

在字符串和数值作比较的时候，字符串和数值都会转换成浮点数进行比较。
字符串在转浮点数时，如果字符串开头是数字，那就一直截取，直到截取到的不是数字是其他字符为止。若开头不是数字，那么转换数值类型的结果就直接取0。（如’123abc’转成的值是123，'ab123’取到的值是0）
如果用了字符串字段，在查询的时候最好不要用数值（如整数之类的）来查询，因为如果用数值查询，也就是如上面说的，MySql会自动把表达式中的值都转换成浮点数。而这里索引字段varchar类型进行自动转换成了浮点数，导致了索引失效。