SQLite ORDER BY 字符串包含以 0 开头的数字

Question

如标题所述:

我有一个选择查询，我试图“排序”一个包含数字的字段，问题是这个数字实际上是从 0 开始的字符串，所以“排序”正在这样做......

...
10
11
12
01
02
03
...

有什么想法吗？

编辑:如果我这样做:“...ORDER BY (field+1)” 我可以解决这个问题，因为不知何故字符串在内部被转换为整数。这是像 C 的 atoi 一样“正式”转换它的方法吗？

Answer 1

您可以使用 CAST http://www.sqlite.org/lang_expr.html#castexpr 将表达式转换为整数。

sqlite> CREATE TABLE T (value VARCHAR(2));
sqlite> INSERT INTO T (value) VALUES ('10');
sqlite> INSERT INTO T (value) VALUES ('11');
sqlite> INSERT INTO T (value) VALUES ('12');    
sqlite> INSERT INTO T (value) VALUES ('01');
sqlite> INSERT INTO T (value) VALUES ('02');
sqlite> INSERT INTO T (value) VALUES ('03');
sqlite> SELECT * FROM T ORDER BY CAST(value AS INTEGER);
01
02
03
10
11
12
sqlite>

if I do this: "...ORDER BY (field+1)" I can workaround this, because somehow the string is internally being converted to integer. Is the a way to "officially" convert it like C's atoi?

这很有趣，虽然我不知道有多少 DBMS 支持这样的操作，所以我不推荐它，以防万一您需要使用不支持它的不同系统，更不用说你添加了一个额外的操作，这会影响性能，尽管你也这样做 ORDER BY (field + 0) 我要调查性能

取自 sqlite3 文档:

A CAST expression is used to convert the value of to a different storage class in a similar way to the conversion that takes place when a column affinity is applied to a value. Application of a CAST expression is different to application of a column affinity, as with a CAST expression the storage class conversion is forced even if it is lossy and irrreversible.

4.0 Operators
All mathematical operators (+, -, *, /, %, <<, >>, &, and |) cast both operands to the NUMERIC storage class prior to being carried out. The cast is carried through even if it is lossy and irreversible. A NULL operand on a mathematical operator yields a NULL result. An operand on a mathematical operator that does not look in any way numeric and is not NULL is converted to 0 or 0.0.

我很好奇，所以我运行了一些基准测试:

>>> setup = """
... import sqlite3
... import timeit
... 
... conn = sqlite3.connect(':memory:')
... c = conn.cursor()
... c.execute('CREATE TABLE T (value int)')
... for index in range(4000000, 0, -1):
...     _ = c.execute('INSERT INTO T (value) VALUES (%i)' % index)
... conn.commit()
... """
>>> 
>>> cast_conv = "result = c.execute('SELECT * FROM T ORDER BY CAST(value AS INTEGER)')"
>>> cast_affinity = "result = c.execute('SELECT * FROM T ORDER BY (value + 0)')"
>>> timeit.Timer(cast_conv, setup).timeit(number = 1)
18.145697116851807
>>> timeit.Timer(cast_affinity, setup).timeit(number = 1)
18.259973049163818
>>>

我们可以看到它有点慢，但不是很多，很有趣。