在 MySQL 中比较时，转换为 varchar 或 varchar 为 int 是否更快? [英] Is it faster to convert into to varchar or varchar to int when comparing in MySQL?

问题描述

所以我有两个表，它们的 ID 引用了同一个对象.其中一个表将其存储为 varchar，另一个将其存储为 int.我想要子查询(或可能连接)这两个表.

So I have two tables that have IDs that reference the same object. One of those tables stores it as a varchar and the other stores it as an int. I want subquery (or possibly join) these two tables.

WHERE foo = CAST(bar AS UNSIGNED)

或

WHERE CAST(foo AS CHAR) = bar

哪个更好?

两个 ID 均未编入索引.

Neither of the IDs are indexed.

推荐答案

这取决于索引.如果两列都设置了索引，则应该强制转换为 int，因为它只需要比较 4 个字节.但是，如果只有 varchar 列被索引，则转换为 varchar.

That depends on the indexes. If both columns have indexes set, you should cast to int, because it only needs to compare 4 bytes. If, however, only the varchar column is indexed, cast to varchar.

最后，您应该让您的查询explained，以查看使用了哪些索引，并进行相应的转换.

At the end, you should have your query explained, to see what index gets used, and cast accordingly.

如果没有设置索引，一般来说转换为 int 是更好的方法.但是，如果没有设置索引，这并不重要，通过索引比选择正确的转换方式可以节省更多的时间.再说一次，桌子很小.那么，也无所谓了.

If no index is set, casting to int is the better approach in general. However, if there is no index set, it doesn't really matter, you would save much more time by indexing than by choosing the correct way to cast. Except, again, the tables are small. Then, it doesn't really matter either.

事实上，我们不仅应该考虑比较的成本，还应该考虑转换的成本:将 int 转换为 varchar 比解析 varchar 稍快code>varchar 到 int.但是，比较具有至少 4 个字符的 varchar 会使这种优势变得毫无用处.如果您的值往往高于 999，则转换为 int 会更快.但这现在取决于您的操作系统、体系结构、您的 mysql 二进制文件使用的指令集等等.获得真正可靠答案的唯一方法是:在目标机器上对这种情况进行基准测试.

In fact, we should take into account not only the costs for comparison, but also for casting: Casting an int to a varchar is slightly faster than parsing a varchar to an int. However, comparing a varchar that has at least 4 characters will render that advantage useless. If your values tend to be higher than 999, casting to int will be faster. But that now depends on your operating system, the architecture, the instruction set used by your mysql binary and so on. The only way to get a really reliable answer is: Benchmark that situation on the target machine.

这篇关于在 MySQL 中比较时，转换为 varchar 或 varchar 为 int 是否更快?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！