如何用R的RSQLite与DBI对SQL数据库按行名永久排序？

问题描述 #

我想要用R按行名对SQLite数据库里的表进行排序，目前已经能通过查询获取排序后的结果：

library(RSQLite)
library(DBI)
con <- dbConnect(RSQLite::SQLite(), dbname = ":memory:")
dbWriteTable(con, "cars", mtcars, temporary = TRUE, overwrite = FALSE, row.names = FALSE)
dbReadTable(con, "cars")
res <- dbSendQuery(con, 'SELECT * FROM cars ORDER BY rowname ASC')
dbFetch(res)

但我希望直接对数据库中的表本身进行排序，尝试执行dbExecute(con, 'SELECT * FROM cars ORDER BY rowname ASC')后，再用dbReadTable(con, "cars")查看，发现数据库并没有变化，请问该怎么解决？

解决方案 #

首先得明确一个关键知识点：SELECT ... ORDER BY语句只是在查询时返回排序后的结果集，完全不会修改数据库表的物理存储顺序。如果你想要让表本身的存储顺序变成按行名排序，需要用「重新创建排序后的表并替换原表」的方式，因为SQLite（以及多数关系型数据库）不支持直接修改已有表的存储顺序。

另外还要注意：你原来的代码里dbWriteTable用了row.names = FALSE，这会导致mtcars的行名没有被存入数据库表中，所以你的SELECT * FROM cars ORDER BY rowname ASC其实会报错——第一步得先把行名作为一列存入表中。

下面是完整的解决步骤：

1. 先将行名作为列存入数据库表
   修改写入表的代码，把mtcars的行名转为单独的列再存入：

   library(RSQLite)
   library(DBI)
   library(tibble) # 用rownames_to_column函数需要这个包
   
   con <- dbConnect(RSQLite::SQLite(), dbname = ":memory:")
   # 把mtcars的行名转为名为rowname的列
   mtcars_with_rownames <- mtcars %>% rownames_to_column(var = "rowname")
   # 写入数据库表
   dbWriteTable(con, "cars", mtcars_with_rownames, temporary = TRUE, overwrite = TRUE)
   

2. 创建排序后的新表并替换原表
   通过CREATE TABLE ... AS SELECT语句创建一个按行名排序的新表，然后删除原表、重命名新表：

   # 创建按rowname升序排序的新表
   dbExecute(con, 'CREATE TABLE cars_sorted AS SELECT * FROM cars ORDER BY rowname ASC')
   # 删除原表
   dbExecute(con, 'DROP TABLE cars')
   # 将新表重命名为原表名
   dbExecute(con, 'ALTER TABLE cars_sorted RENAME TO cars')
   

3. 验证结果
   现在读取表，就能看到表本身已经是按行名排序的了：

   dbReadTable(con, "cars")
   

额外说明 #

• 多数情况下，关系型数据库不推荐修改表的物理存储顺序，因为查询时随时可以用ORDER BY来排序结果，物理顺序对查询性能的影响很小。只有当你有特殊的性能优化需求时，才需要这么做。
• 如果你使用的是其他数据库（比如PostgreSQL），可能有更直接的方法，但SQLite只能通过重建表的方式实现物理排序。

内容的提问来源于stack exchange，提问作者Nivel