Android Kotlin Room SQLite 如何通过@Query批量插入列表到指定列

你想要的按列把整个List批量映射到对应表字段的写法，在SQLite和Room中都是不可行的。SQLite从语法设计上就不支持这种“列级批量赋值”的单条插入逻辑——所有写入操作的基本单位是「行」，单条INSERT语句只能逐行组织值，不存在把List<Int>直接塞给id列、List<String>直接塞给first_name列的语法支撑。

之前放弃@Insert注解、选择逐行循环写入，本质是踩了两个常见的Room使用坑：

• 配置onConflict = OnConflictStrategy.REPLACE的@Insert，底层执行逻辑是碰到主键/唯一键冲突时，直接删除整条旧记录，再插入全新的整行数据。如果传入的实体对象没有给第五列赋值，它自然会被重置为默认值，这不是@Insert本身的问题，是REPLACE冲突策略的执行逻辑和需求不匹配。
• 之前写的@Query方法只接收单值参数，所以只能逐行调用，根本没用到Room本身支持的批量传参能力。

Room从1.0版本就支持把List类型的参数传入@Query方法，底层会自动把同索引位置的元素匹配成同一行的列值，一次性拼接成多组VALUES子句批量提交，完全不需要自己写for循环。
对应需求（只写入4个指定列，全程不触碰第五列的原有值），可直接使用如下写法：

@Query("INSERT INTO friends_table (id, first_name, last_name, photo) " +
    "VALUES (:idList, :firstNameList, :lastNameList, :photoList) " +
    "ON CONFLICT(id) DO UPDATE SET " +
    "first_name = excluded.first_name, " +
    "last_name = excluded.last_name, " +
    "photo = excluded.photo")
suspend fun batchSyncFriendData(
    idList: List<Int>,
    firstNameList: List<String>,
    lastNameList: List<String>,
    photoList: List<String>
)

写法说明 #

• 只要传入的4个List长度完全一致，Room会自动展开SQL，把同索引的元素拼成对应行的值，最终生成类似如下的可执行SQL：

  INSERT INTO friends_table (id, first_name, last_name, photo) 
  VALUES (1, "张", "三", "url1"), (2, "李", "四", "url2"), (3, "王", "五", "url3")
  ON CONFLICT(id) DO UPDATE SET 
  first_name = excluded.first_name,
  last_name = excluded.last_name,
  photo = excluded.photo
  

• 整个执行过程由SQLite在单事务内完成，比之前的for循环逐条写入的性能高1~2个数量级。
• 因为SQL的写入列清单里从始至终没有包含第五列，碰到主键冲突时也只更新三个非主键列的内容，第五列的原有值不会被任何操作触碰。
• 注意：Room对列表参数的自动匹配逻辑是按行索引对齐同位置元素，不会把某个列表单独映射到单个列的所有行。也就是说只要传入的4个列表长度一致，生成的批量写入结果和之前for循环逐条写入的逻辑完全等价，只是批量提交的效率高很多。

如果使用的Room版本在2.2.0以上，可以直接用内置的@Upsert注解替代上面的手写@Query逻辑，它底层用的就是不删除旧行的ON CONFLICT更新逻辑，不会像REPLACE策略一样清空整行数据，只需要给实体加上对应注解，传入完整的实体列表即可，效果和上面的手写SQL完全一致。

内容的提问来源于stack exchange，提问作者Денис