基于Sqlite文档: https: //www.sqlite.org/datatype3.html#type_conversions_prior_to_comparison,特别是这个声明:
如果一个操作数具有 INTEGER、REAL 或 NUMERIC 关联性,而另一操作数具有 TEXT 或 BLOB 或无关联性,则 NUMERIC 关联性将应用于另一操作数。
我期望以下查询:
CREATE TABLE `invoice` (
`id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
`amount` DOUBLE PRECISION DEFAULT NULL
);
insert into `invoice` (`amount`) values (4.0);
insert into `invoice` (`amount`) values (15.0);
insert into `invoice` (`amount`) values (4.0);
select *,
typeof(amount), amount = '4',
typeof(sum(amount)), sum(amount) = '4', sum(amount) = '4.0', sum(amount) = 4
from invoice
group by id;
sum(amount) = '4'
为每行返回相同的结果,amount = '4'
因为两个操作数类型在每次比较中都具有相同的类型(使用typeof()
, for non进行验证SUM()
,比较是否按预期工作)。
我认为您已经发现 SQLite 的行为略有不一致!下面的所有代码都可以在此处的小提琴上找到。
首先我们有:
结果:
然后我们这样做:
结果:
所以,现在我们尝试一下这个表:
结果:
最后,
结果:
因此,在 f/f/f 的情况下,您尝试强制 SUM(amount) 来匹配 TEXT!在我看来,当您尝试隐式强制字符串匹配 SUM 时,SQLite 会变得混乱。我以为可能都是聚合函数,但事实并非如此!
看一下这个:
结果:
因此,对于 COUNT,它是 t/f,但对于 MAX,它是 t/t!
正如我所说,我认为您已经发现了 SQLite 行为的差异,当然,除非 D. Richard Hipp 使用了一些我不太理解的逻辑 - 而且他是一个非常聪明的人!你举报还是我举报?
尽管如此,“在现实生活中”真正想要做到这一点的彻底疯子远远超出了我的工资等级!:-)
最后,仅供参考,您不需要 SQLite 的反引号 - 甚至 MySQL 也不再需要它们了!有趣的帖子+1!
类型关联不应与数据类型(Sqlite 称为存储类)混淆。
Sqlite 中的每个值都有一个存储类,它始终是 NULL、INTEGER、REAL、TEXT 或 BLOB。
4 是 INTEGER 存储类别的值。'xyz' 是 TEXT 存储类别的值。
请注意,
typeof()
返回值的存储类,而不是列的类型关联。由于在 Sqlite 中,列不需要具有特定的存储类,并且您可以在同一列中存储 4 或“xyz”,因此 Sqlite 引入了类型亲和性的概念,它指示应该存储哪种类型的值如果可能的话,该列。所以如果你声明一个列
您实际上是说 amount应该包含 REAL 值(具有 REAL类型affinity),但实际值可以是任何类型。
'4'
当您插入一个值into时,Sqlite 会尝试满足这种关联性amount
:它会在存储之前将其转换为 REAL 值。但如果您插入值'xyz'
,它无法将其转换为 REAL,而是将该值存储为 TEXT。当您在查询中使用该列时
amount
,Sqlite 假定该列中存储的值应该是 REAL 值。因此,当它进行比较时amount = '4'
,它将应用类型亲和力的规则:'4'
将转换为 REAL 并与列中存储的内容进行比较。由于类型亲和力是由创建时的列定义决定的,而不是由包含的单个值决定的,这意味着:
A. _
amount
即使行中包含的值为 ,类型关联性也始终为 REAL'xyz'
。B. _ 任何不是列的内容都从未使用类型关联进行声明,因此任何不是对实际表的列的简单引用或未显式 CAST 的表达式都不具有类型关联。
这意味着
4
和'4'
没有类型关联(即使它们分别是 INTEGER 和 TEXT 存储类的常量)。另外amount+10
,sum(amount)
或min(amount)
都是不具有类型关联的表达式(您没有向 Sqlite 声明这些表达式应该是哪种类型),但是,当对每一行进行计算时,它们将计算为特定存储类中的值。这就是为什么:
4 = '4'
为 FALSE,因为两者都没有类型亲和性,并且 sqlite 将整数与字符串进行比较amount = '4'
当 a 存储在行中时为 TRUE4
,因为 amount 的类型亲和性为 REAL,sqlite 期望包含的所有值都应该是实数并在比较之前转换'4'
为。4
sum(amount) = '4'
即使 sum 实际上是 FALSE4
,因为sum(amount)
它不是对列的简单引用,它是一个表达式,因此它没有类型亲和力。4
然后与'4'
第一个示例中的结果进行比较,结果为 false。CAST(sum(amount) AS REAL) = '4'
它是 TRUE4
,因为 CAST 为左手表达式分配了 REAL 类型关联,因此 sqlite 知道它必须在比较之前将右手转换'4'
为4
。另外,如果我理解正确的话,虽然在运行时检查和评估存储类(因为不同的行可以具有不同
amount
存储类的不同值),但我认为类型亲和力是在准备时评估的,因此实际上为每个建立了任何必要的转换行,然后检查行中的值。