sql where条件为int但列类型为Varchar [英] sql where condition for int but column type is Varchar

问题描述

我有一个名为 Buttons 的表.按钮表我有列 button_number .表包含 100 多条记录.现在我必须选择 10 到 50 之间的按钮编号.当我使用以下查询时，它不会返回零行.

I have table called Buttons. Buttons table i have column button_number . Table contain more than 100 records. Now i have to select a buttons number between 10 to 50. When i used this following query it's not returning zero row.

select * from Buttons where button_number >= '10' and button_number <='50'

查询中有什么问题.如果我对不同的列使用相同的查询(数据类型为 int)，那么它工作正常.问题是因为列的数据类型吗?如果是这样，解决方法是什么?

What's the problem in the query. If i use the same query with different column(data type is int) then it's working fine. Is the problem because of the data type of the column? If so what's the fix for this?

注意:button_number 列数据类型为 Varchar.

Note : button_number column data type is Varchar.

推荐答案

button_number 是 varchar 并且您正在尝试进行整数样式比较.您需要将 button_number 列转换为整数.

The button_number is varchar and you are trying to do an integer style comparison. You will need to cast the button_number column to an integer.

select * from Buttons where 
    convert(integer, button_number) >= 10 and convert(integer, button_number) <= 50

编辑

上述查询需要进行表扫描，因为它需要在应用 where 子句之前将每个 button_number 转换为整数.这对于 100 行来说不是问题，但对于大量数据来说却是个问题.

The above query will require a table scan since it needs to convert every button_number to an integer before applying the where clause. This isn't a problem for 100 rows, but would be an issues for large numbers.

正如 Mikael Eriksson 的回答中提到的，更好的选择是将 button_number 的类型更改为整数类型(如果可能).这将有几个优点:

As mentioned in Mikael Eriksson's answer, a better alternative would be to change the type of button_number to an integer type (if possible). This would have several advantages:

• 不可能在列中输入非整数值
• 然后您就可以对列应用整数索引.当表包含大量行时，这将大大提高查询速度.

这篇关于sql where条件为int但列类型为Varchar的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！