INT 数据库字段如何与 VARCHAR 类型进行比较 [英] How INT database field gets compared to VARCHAR type

问题描述

我有以下存储过程，不是写完整的存储过程，但其中一些是:

I have the following stored procedure, not writing the complete stored procedure but some of it is:

@course int = null,
    SET @query = @query + 'Where course_id='+ cast(@course as varchar)

我想知道何时将@course 转换为 VARCHAR 类型，而我在数据库中的 course_id 是 INT 类型 - 比较是如何进行的?

I want to know when I have converted the @course as the VARCHAR type while my course_id in the database is of INT type - how the comparison takes place?

推荐答案

SQL Server 中两种不同类型的比较是这样发生的:

The comparison between two different types in SQL Server takes place like this:

• 首先根据数据的规则比较两种类型的优先级输入优先级:

将优先级较低的数据类型转换为优先级较高的数据类型

the data type with the lower precedence is converted to the data type with the higher precedence

接下来将优先级较低的类型强制转换为优先级较高的类型

next the type with lower precedence is casted to the type with higher precedence

然后在具有较高优先级的类型的原始值和具有较低优先级的类型的转换值之间进行比较

the comparison is then done between the original value of the type with higher precedence and the converted value of the type with lower precedence

为了便于讨论:INT(优先级 16，高)和 VARCHAR(优先级 27，低)将通过转换 VARCHAR 到 INT 然后比较两者.

For the sake of discussion: an INT (precedence 16, high) and a VARCHAR (precedence 27, low) would compare by casting the VARCHAR to INT and then comparing the two.

在您的情况下，虽然没有发生转换，因为发生的是 @course 值被附加到动态构造的 @sql.不用说，这很糟糕.正确的解决方案是将 @course 作为参数传递给动态 SQL 调用:

In your case though there is no conversion occuring, because what happens is that the @course value gets appended to the dynamically constructed @sql. Needless to say, that is bad. the proper solution is to pass down the @course as a parameter to the dymnamic SQL invocation:

@course INT = null
...
 SET @query = @query + 'Where course_id= @course';
...
exec sp_executesql @sql, '@course int', @course;

这是:

• 更快:参数化查询而不是硬编码值
• 更安全:如果代码被重构并且@counter 成为可以进行 SQL 注入的类型，则降低 SQL 注入的风险
• 不易出错:没有传播 NULL 值以使整个 @sql
无效的风险

• faster: parameterized query instead of hard codded value
• safer: lower the risk of SQL injection if code gets refactored and @counter becomes a type tha can carry SQL injection
• less error-prone: no risk of NULL value propagating to nullify the entire @sql

这篇关于INT 数据库字段如何与 VARCHAR 类型进行比较的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！