主页 / dba / 问题 / 267843
Accepted
Nishant
Asked: 2020-05-26 04:42:31 +0800 CST

为什么 SQL Server 说它不能将 varchar 转换为数字？

  • 772

我在 MSSQL Server 2019 ( SQLFiddle )中有以下架构/数据：

CREATE TABLE products(
    idn NUMERIC(9) PRIMARY KEY
);

CREATE TABLE sales(
    idn NUMERIC(9) PRIMARY KEY,
    pid VARCHAR(50) NOT NULL,
    type VARCHAR(10) NOT NULL
);

INSERT INTO products (idn) VALUES (1);
INSERT INTO sales (idn, pid, type) VALUES (1, 1, 'number');
INSERT INTO sales (idn, pid, type) VALUES (2, 'Colgate', 'word');

sales具有混合数据，即VARCHAR和NUMERIC。事务过滤器负责JOIN正确处理。

为什么以下SELECT失败（由于某种我无法控制的原因，生成的查询包含N字符串的文字。）？

SELECT 
    * 
FROM 
    products 
    INNER JOIN sales ON products.idn = sales.pid 
    AND sales.type = N'number' 
WHERE 
    products.idn in (1);

我不明白为什么投射NVARCHAR到NUMERIC是一个问题：

SELECT CAST (N'1' as NUMERIC);

如果我稍微修改查询，它可以工作：

SELECT 
    * 
FROM 
    products 
    INNER JOIN sales ON products.idn = sales.pid 
    AND sales.type = N 'number' 
WHERE
    -- Selecting the same data from `sales`.
    sales.pid in (1);

SELECT 
    * 
FROM 
    products 
    INNER JOIN sales ON products.idn = sales.pid
    -- Dropping the `N` prefix.
    AND sales.type = 'number' 
WHERE 
    products.idn in (1);
sql-server type-conversion

4 个回答

  1. Best Answer

    当尝试将sales.pid值 'Colgate' 值转换为 numeric(9) 以评估连接条件时，将在运行时发生转换错误。至于这是否真的发生取决于执行计划中的评估顺序。

    下面是来自Unicode 文字执行计划的销售表聚集索引扫描谓词，显示了在评估“数字”条件之前发生的转换：

    CONVERT_IMPLICIT(numeric(9,0),[tempdb].[dbo].[sales].[pid],0)=(1.) AND CONVERT_IMPLICIT(nvarchar(10),[tempdb].[dbo].[sales].[type],0)=N'number'

    具有非 Unicode 文字的计划具有相同的形状，只是扫描运算符中的谓词顺序颠倒了：

    [tempdb].[dbo].[sales].[type]='number' AND CONVERT_IMPLICIT(numeric(9,0),[tempdb].[dbo].[sales].[pid],0)=(1.)

    尽管非 Unicode 文字（或参数）可以解决问题，但查询仍然容易受到运行时错误的影响。这可以用TRY_CAST, TRY_CONVERT,CASE表达式等来解决，但最好修复数据模型，例如确保只比较相似或兼容的数据类型。

    • 2

  2. 这是因为您正在使用包含文本值 (pid) 的列来连接数字列

    INNER JOIN sales ON products.idn = sales.pid

    当您对这两个表进行连接时，SQL 需要将 idn 中的所有行与 pid 中的所有行进行比较。为了比较它们，它需要将您的 varchar 转换为数字。

    SQL如何将'Colgate'转换为数字？这就是它失败的原因。

    您应该阅读有关数据库规范化的信息。

    您的 PID 列似乎不太好，您可能需要另一个带有产品 ID 和产品描述（其中“高露洁”将是描述）的表才能使其按预期工作。

    • 1

  3. 为什么以下 SELECT 失败（由于某些我无法控制的原因，生成的查询有 N 个字符串文字。）？

    因为它得到了一个执行计划来评估

    sales.pid=1

    对于与

    sales.pid = 'Colgate'

    在它评估谓词之前

    sales.type = N'Number'

    int具有比执行比较更高的数据类型优先级的 'varchar' 隐式转换为. 对于“高露洁”，此转换失败。varcharint

    锁定这两个条件的评估顺序的方法是使用 CASE 表达式。例如

    SELECT 
    * 
FROM 
    products 
    INNER JOIN sales 
    ON  case when sales.type = N'number' and sales.pid = products.idn then 1 else 0 end = 1
WHERE 
products.idn = 1

    这可能会导致大规模的性能问题，但这是每列应该只有一种类型的数据的众多原因之一。

    • 1

  4. 查询优化器可能会更改执行操作的顺序，以便在它提供的选项中以最快的方式获得结果（假设查询的结果没有改变）。

    知道了这一点，如果您检查执行计划，它将帮助您了解一个查询出错的原因，而不是给定您提供的示例数据的另一个查询：

    查询一：

    SELECT 
    * 
FROM 
    products 
    INNER JOIN sales ON products.idn = sales.pid
    -- Dropping the `N` prefix.
    AND sales.type = 'number' 
WHERE 
    products.idn in (1);

    查询计划

    查询 2：

    SELECT 
    * 
FROM 
    products 
    INNER JOIN sales ON products.idn = sales.pid 
    AND sales.type = N'number' 
WHERE 
    products.idn in (1);

    查询计划

    查询计划的谓词

    查询 1有效，因为当您删除 N（用于将字符串转换为nvarchar）时，SQL Server 不需要执行sales.type从 varchar 到 nvarchar 的隐式转换。在这种情况下sales.pid，对两个查询都有一个隐式转换，因为它正在与products.idn具有不同数据类型的比较。查询优化器决定在没有隐式转换的情况下比较列的成本较低，因此它首先执行谓词sales.type = 'number'，并生成一个由包含所有值的行组成的子集sales.typeas 数字（并且您可以将实数从字符串 cloumn 没有问题）。因此查询有效。

    查询 2引发错误，因为来自的两个谓词sales都具有隐式转换，并且优化器选择从sales.pid仅过滤保留数字的行之前开始。当它尝试将值转换'Colgate'为numeric(9,0)数据类型时，您会收到错误消息。

    • 1

相关问题