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

Question

如标题所述：

我有一个选择查询，我正在尝试对包含数字的字段进行“排序”，问题是此数字实际上是以0开头的字符串，因此“ order by”正在执行此操作…

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

有什么想法吗？

编辑：如果我这样做：“ … ORDER BY（field +
1）”我可以解决此问题，因为以某种方式在内部将字符串转换为整数。这是像C的atoi一样“正式”转换它的方法吗？

Answer 1

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

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>

如果我这样做：“ … ORDER BY（field +
1）”我可以解决此问题，因为以某种方式在内部将字符串转换为整数。是否可以像C的atoi一样“正式”转换它？

嗯，这很有趣，尽管我不知道有多少种DBMS支持这种操作，所以我不建议您在需要使用不支持该操作的其他系统的情况下使用它，更不用说您要添加额外的操作了，可能会影响性能，尽管您也可以这样做ORDER BY (field + 0)我将要调查性能

取自sqlite3文档：

CAST表达式用于将值转换为不同的存储类，类似于将 列亲和力
应用于值时进行的转换。CAST表达式的应用与列亲和力的应用不同，因为使用CAST表达式，即使存储类转换是有损且不可逆的，也会强制执行存储类转换。

4.0运算符在执行之前，

所有数学运算符（+，-，*，/，％，<<，>>，＆和|）都将这两个操作数都转换为NUMERIC存储类。即使是有损且不可逆的，转换也可以进行。数学运算符上的NULL操作数将产生NULL结果。数学运算符上的操作数将不会以任何形式出现且不是NULL的值将转换为0或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
>>>

正如我们所见，它的运行速度虽然慢了一点，但并不有趣。