如何防止 SQLite 将字符串视为数字? [英] How can I prevent SQLite from treating a string as a number?
问题描述
我想查询一个包含目录路径的 SQLite 表，以查找某个层次结构下的所有路径.以下是该列内容的示例:
I would like to query an SQLite table that contains directory paths to find all the paths under some hierarchy. Here's an example of the contents of the column:
/alpha/papa/
/alpha/papa/tango/
/alpha/quebec/
/bravo/papa/
/bravo/papa/uniform/
/charlie/quebec/tango/
如果我在 /bravo/papa/ 下搜索所有内容，我想得到:
If I search for everything under /bravo/papa/, I would like to get:
/bravo/papa/
/bravo/papa/uniform/
我目前正在尝试这样做(有关为什么我不能使用更简单的方法的长篇故事，请参见下文):
I am currently trying to do this like so (see below for the long story of why I can't use more simple methods):
SELECT * FROM Files WHERE Path >= '/bravo/papa/' AND Path < '/bravo/papa0';
这有效.它看起来有点奇怪，但它适用于这个例子.'0' 是大于 '/' 的 unicode 代码点 1.当按字典顺序排序时，所有以 '/bravo/papa/' 开头的路径比较大于它并小于 'bravo/papa0'.但是，在我的测试中，当我们尝试这样做时，我发现这会崩溃:
This works. It looks a bit weird, but it works for this example. '0' is the unicode code point 1 greater than '/'. When ordered lexicographically, all the paths starting with '/bravo/papa/' compare greater than it and less than 'bravo/papa0'. However, in my tests, I find that this breaks down when we try this:
SELECT * FROM Files WHERE Path >= '/' AND Path < '0';
这不返回任何结果，但它应该返回每一行.据我所知，问题在于 SQLite 将 '0' 视为数字，而不是字符串.例如，如果我使用 '0Z' 而不是 '0'，我确实得到了结果，但是我引入了获得误报的风险.(例如，如果实际上有一个条目0".)
This returns no results, but it should return every row. As far as I can tell, the problem is that SQLite is treating '0' as a number, not a string. If I use '0Z' instead of '0', for example, I do get results, but I introduce a risk of getting false positives. (For example, if there actually was an entry '0'.)
我的问题的简单版本是:是否有某种方法可以让 SQLite 将此类查询中的 '0' 视为包含 unicode 字符 '0'(它应该对诸如 '!' 之类的字符串进行排序)的长度为 1 的字符串., '*' 和 '/'，但在 '1'、'=' 和 'A' 之前)而不是整数 0(SQLite 在所有字符串之前排序)?
The simple version of my question is: is there some way to get SQLite to treat '0' in such a query as the length-1 string containing the unicode character '0' (which should sort strings such as '!', '*' and '/', but before '1', '=' and 'A') instead of the integer 0 (which SQLite sorts before all strings)?
我认为在这种情况下，我实际上可以通过特殊大小写搜索/"下的所有内容，因为我的所有条目始终以/"开头，但我真的很想知道如何避免这种情况总而言之，因为它与 Javascript 的=="运算符一样令人不快地出人意料.
I think in this case I can actually get away with special-casing a search for everything under '/', since all my entries will always start with '/', but I'd really like to know how to avoid this sort of thing in general, as it's unpleasantly surprising in all the same ways as Javascript's "==" operator.
更自然的方法是使用 LIKE 或 GLOB 运算符.例如:
A more natural approach would be to use the LIKE or GLOB operator. For example:
SELECT * FROM Files WHERE Path LIKE @prefix || '%';
但我想支持所有有效的路径字符，所以我需要对 '_' 和 '%' 符号使用 ESCAPE.显然，这会阻止 SQLite 在 Path 上使用索引.(参见 http://www.sqlite.org/optoverview.html#like_opt)我真的希望能够从这里的索引中受益，这听起来像是不可能使用 LIKE 或 GLOB 除非我可以保证它们的特殊字符都不会出现在目录名称中，并且 POSIX 允许除 NUL 和 '/'，甚至 GLOB 的 '*' 和 '?'字符.
But I want to support all valid path characters, so I would need to use ESCAPE for the '_' and '%' symbols. Apparently this prevents SQLite from using an index on Path. (See http://www.sqlite.org/optoverview.html#like_opt ) I really want to be able to benefit from an index here, and it sounds like that's impossible using either LIKE or GLOB unless I can guarantee that none of their special characters will occur in the directory name, and POSIX allows anything other than NUL and '/', even GLOB's '*' and '?' characters.
我提供这个作为上下文.我对解决潜在问题的其他方法感兴趣，但我更愿意接受一个直接解决 SQLite 中类似字符串的数字的歧义的答案.
I'm providing this for context. I'm interested in other approaches to solve the underlying problem, but I'd prefer to accept an answer that directly addresses the ambiguity of strings-that-look-like-numbers in SQLite.
在那个问题中，值没有被引用.即使值被引用或作为参数传入，我也会得到这些结果.
In that question, the values weren't quoted. I get these results even when the values are quoted or passed in as parameters.
EDIT - 请参阅下面的答案.该列是使用无效类型STRING"创建的，SQLite 将其视为 NUMERIC.
EDIT - See my answer below. The column was created with the invalid type "STRING", which SQLite treated as NUMERIC.
推荐答案
* Groan *.该列具有 NUMERIC 关联，因为它意外地被指定为STRING"而不是TEXT".由于 SQLite 无法识别类型名称，因此将其设为 NUMERIC，并且因为 SQLite 不强制执行列类型，所以其他一切都按预期工作，除了任何时候将类似数字的字符串插入该列时，它都会被转换为数字类型.
* Groan *. The column had NUMERIC affinity because it had accidentally been specified as "STRING" instead of "TEXT". Since SQLite didn't recognize the type name, it made it NUMERIC, and because SQLite doesn't enforce column types, everything else worked as expected, except that any time a number-like string is inserted into that column it is converted into a numeric type.
这篇关于如何防止 SQLite 将字符串视为数字?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
