Android/SQLite: 插入-更新表格列以保留标识符

Question

目前，我正在使用以下语句在 Android 设备上的 SQLite 数据库中创建一张表。

CREATE TABLE IF NOT EXISTS 'locations' (
  '_id' INTEGER PRIMARY KEY AUTOINCREMENT, 'name' TEXT, 
  'latitude' REAL, 'longitude' REAL, 
  UNIQUE ( 'latitude',  'longitude' ) 
ON CONFLICT REPLACE );

在最后的冲突子句中，如果新插入的行具有相同的坐标，则会导致这些行被删除。 SQLite文档 包含有关冲突子句的进一步信息。

我希望能够保留以前的行并仅更新它们的列。在Android / SQLite环境中，最有效的方法是什么？

• 作为CREATE TABLE语句中的冲突子句。
• 作为INSERT触发器。
• 作为ContentProvider#insert方法中的条件子句。
• ...您能想到的更好的方法

我认为在数据库内部处理此类冲突更为高效。此外，我发现很难重写ContentProvider#insert方法以考虑插入-更新场景。以下是insert方法的代码：

public Uri insert(Uri uri, ContentValues values) {
    final SQLiteDatabase db = mOpenHelper.getWritableDatabase();
    long id = db.insert(DatabaseProperties.TABLE_NAME, null, values);
    return ContentUris.withAppendedId(uri, id);
}

当后端传来数据时，我所做的就是将数据插入如下。

getContentResolver.insert(CustomContract.Locations.CONTENT_URI, contentValues);

我在这里有困难，不知道如何应用替代的调用方式来使用ContentProvider#update。此外，这也不是我首选的解决方案。

---

编辑：

@CommonsWare：我尝试实现您的建议，使用INSERT OR REPLACE。我想出了这段丑陋的代码。

private static long insertOrReplace(SQLiteDatabase db, ContentValues values, String tableName) {
    final String COMMA_SPACE = ", ";
    StringBuilder columnsBuilder = new StringBuilder();
    StringBuilder placeholdersBuilder = new StringBuilder();
    List<Object> pureValues = new ArrayList<Object>(values.size());
    Iterator<Entry<String, Object>> iterator = values.valueSet().iterator();
    while (iterator.hasNext()) {
        Entry<String, Object> pair = iterator.next();
        String column = pair.getKey();
        columnsBuilder.append(column).append(COMMA_SPACE);
        placeholdersBuilder.append("?").append(COMMA_SPACE);
        Object value = pair.getValue();
        pureValues.add(value);
    }
    final String columns = columnsBuilder.substring(0, columnsBuilder.length() - COMMA_SPACE.length());
    final String placeholders = placeholderBuilder.substring(0, placeholdersBuilder.length() - COMMA_SPACE.length());
    db.execSQL("INSERT OR REPLACE INTO " + tableName + "(" + columns + ") VALUES (" + placeholders + ")", pureValues.toArray());

    // The last insert id retrieved here is not safe. Some other inserts can happen inbetween.
    Cursor cursor = db.rawQuery("SELECT * from SQLITE_SEQUENCE;", null);
    long lastId = INVALID_LAST_ID;
    if (cursor != null && cursor.getCount() > 0 && cursor.moveToFirst()) {
        lastId = cursor.getLong(cursor.getColumnIndex("seq"));
    }
    cursor.close();
    return lastId;
}

然而，当我检查 SQLite 数据库时，相同的列仍然会被删除并插入新的 id。 我不明白为什么会发生这种情况，之前以为是我的冲突子句导致的。但文档说明相反。

在 INSERT 或 UPDATE 的 OR 子句中指定的算法将覆盖在 CREATE TABLE 中指定的任何算法。 如果没有在任何位置指定算法，则使用 ABORT 算法。

此次尝试的另一个缺点是你会丢失由 insert 语句返回的 id 值。 为了补偿这一点，我最终找到了一个选项来请求last_insert_rowid。如 dtmilano 和 swiz 的帖子所述。不过，我不确定这是否安全，因为可能会有另一次插入操作发生在它们之间。

- JJD

---

不，我的意思是“插入或替换”。http://sqlite.org/lang_insert.html - CommonsWare

我知道如何一般使用它，但不理解如何在这个Android环境中使用它。抱歉。 - JJD

在您的ContentProvider实现中，使用execSQL("INSERT OR REPLACE...")而不是insert()，其中...被替换为其余的SQL语句。例如：getWritableDatabase().execSQL("INSERT OR REPLACE INTO notes (position, prose) VALUES (?, ?)", args); - CommonsWare

啊！我没看到这个。缺点是我需要“解包”ContentValues对象以获取列名及其值。你认为有什么办法可以避免这种情况吗？ - JJD

我曾经遇到过类似的情况，并通过条件/更新子句进行处理。如果(db.update(TABLE, values, CONDITION, null) == 0)，那么这样数据库会首先尝试更新，如果没有行受到影响，即没有冲突，则会添加一行新数据。 - Rarw

Answer 1

我可以理解认为在SQL中完成所有这些逻辑是最佳性能的看法，但或许在这种情况下，最简单（代码最少）的解决方案才是最好的？为什么不先尝试更新，然后再使用insertWithOnConflict()和CONFLICT_IGNORE进行插入（如果需要）并获取所需的行id：

public Uri insert(Uri uri, ContentValues values) {
    final SQLiteDatabase db = mOpenHelper.getWritableDatabase();
    String selection = "latitude=? AND longitude=?"; 
    String[] selectionArgs = new String[] {values.getAsString("latitude"),
                values.getAsString("longitude")};

    //Do an update if the constraints match
    db.update(DatabaseProperties.TABLE_NAME, values, selection, null);

    //This will return the id of the newly inserted row if no conflict
    //It will also return the offending row without modifying it if in conflict
    long id = db.insertWithOnConflict(DatabaseProperties.TABLE_NAME, null, values, CONFLICT_IGNORE);        

    return ContentUris.withAppendedId(uri, id);
}

一个更简单的解决方案是检查update()的返回值，仅在受影响的记录数量为零时执行插入操作，但这样会存在一种情况，即您无法获取现有行的id而不进行额外的查询。这种插入形式将始终向您返回正确的id以在Uri中传递回来，并且不会对数据库进行超过必要的修改。

如果您想一次性执行大量此类操作，则可以查看提供程序上的bulkInsert()方法，其中可以在单个事务内运行多个插入操作。在这种情况下，由于您不需要返回已更新记录的id，因此“更简单”的解决方案应该完全可行：

public int bulkInsert(Uri uri, ContentValues[] values) {
    final SQLiteDatabase db = mOpenHelper.getWritableDatabase();
    String selection = "latitude=? AND longitude=?";
    String[] selectionArgs = null;

    int rowsAdded = 0;
    long rowId;
    db.beginTransaction();
    try {
        for (ContentValues cv : values) {
            selectionArgs = new String[] {cv.getAsString("latitude"),
                cv.getAsString("longitude")};

            int affected = db.update(DatabaseProperties.TABLE_NAME, 
                cv, selection, selectionArgs);
            if (affected == 0) {
                rowId = db.insert(DatabaseProperties.TABLE_NAME, null, cv);
                if (rowId > 0) rowsAdded++;
            }
        }
        db.setTransactionSuccessful();
    } catch (SQLException ex) {
        Log.w(TAG, ex);
    } finally {
        db.endTransaction();
    }

    return rowsAdded;
}

事实上，事务代码通过最小化将数据库内存写入文件的次数使事情更快，bulkInsert()只允许使用单个对提供程序的调用传递多个ContentValues。

- devunwired

---

我很欣赏你的想法，但我担心在大量项目上进行前端操作会花费太长时间。也许，我的担忧是没有理由的... - JJD

如果您正在处理大量数据集，您应该在ContentProvider.bulkInsert()方法内部实现相同的逻辑，并使用事务来减少实际数据库文件I/O的负载。 - devunwired

同时，我意识到顺序的 INSERT 会导致性能问题（请阅读这里）。你会如何设计批量 INSERT？ - JJD

已添加。基本上针对传递的所有值执行插入/更新对，所有操作都在一个事务中进行。 - devunwired

我已经切换到你的解决方案了。最初，我没有意识到 bulkInsert() 是内容提供程序的一个方法。据我所知，它工作得相当不错。再次感谢。现在，我仍然需要解决一个[小更新问题]（https://dev59.com/1WfWa4cB1Zd3GeqPgmjG）。 - JJD

如果使用CONFLICT_IGNORE的db.insertWithOnConflict存在冲突，将返回-1。因此，您可能无法始终获得受更新影响的正确行数。 - mahdi

Answer 2

有一个解决方案是为locations表创建一个视图，并在视图上创建一个INSTEAD OF触发器，然后插入到该视图中。以下是实现的代码示例：

视图:

CREATE VIEW locations_view AS SELECT * FROM locations;

触发器：

CREATE TRIGGER update_location INSTEAD OF INSERT ON locations_view FOR EACH ROW 
  BEGIN 
    INSERT OR REPLACE INTO locations (_id, name, latitude, longitude) VALUES ( 
       COALESCE(NEW._id, 
         (SELECT _id FROM locations WHERE latitude = NEW.latitude AND longitude = NEW.longitude)),
       NEW.name, 
       NEW.latitude, 
       NEW.longitude
    );
  END;

不要将记录插入到locations表中，而是将其插入到locations_view视图中。触发器将使用子查询来提供正确的_id值。如果插入已经包含一个_id，则COALESCE将保留它并覆盖表中现有的_id。

您可能需要检查子查询对性能的影响，并将其与其他可能进行的更改进行比较，但它确实允许您将此逻辑保持在代码之外。

我尝试了一些其他解决方案，涉及基于INSERT OR IGNORE的表内触发器，但似乎仅当它实际插入表时，BEFORE和AFTER触发器才会触发。

您可能会发现这个答案有用，它是触发器的基础。

编辑：由于在忽略插入时BEFORE和AFTER触发器不会触发（然后可以进行更新），我们需要重写带有INSTEAD OF触发器的插入语句。不幸的是，它们不能用于表 - 我们必须创建一个视图来使用。

- blazeroni

---

我测试过了，就我所知它是有效的。但是，我不明白在INSERT时NEW.id的值是什么。我想知道它是否可以是NULL。但这会与_id不能为空相矛盾！另外，为什么要包含OR REPLACE - 这只是为了使用COALESCE吗？ - JJD

请问您能否在您的回答中解释一下为什么需要为表创建视图才能进行操作？ - JJD

由于每个INSERT现在都在VIEW上运行，我不能再使用另一个触发器来限制行数。这里是问题所在。 - JJD

通常 NEW._id 将为 NULL - 因为该表使用了 AUTOINCREMENT 它会为您创建一个 _id 。但是，由于可以包含 _id，COALESCE 可确保处理得当。为了维护现有的 _id，需要包含 OR REPLACE，因为如果纬度和经度已经存在于表中，它需要替换该行。 - blazeroni

Answer 3

INSERT OR REPLACE与ON CONFLICT REPLACE的作用相同。如果具有唯一列的行已经存在，则它将删除该行，然后进行插入。它永远不会更新。

我建议您继续使用当前的解决方案，创建带有ON CONFLICT子句的表，但每次插入行并发生约束冲突时，您的新行将具有新的_id，因为原始行将被删除。

或者，您可以创建没有ON CONFLICT子句的表，并使用INSERT OR REPLACE，您可以使用insertWithOnConflict()方法实现，但它仅适用于API级别8及以上版本，需要更多的编码工作，并导致与带有ON CONFLICT子句的表相同的解决方案。

如果您仍然想保留原始行，则意味着您想保留相同的_id，那么您将需要执行两个查询，第一个用于插入行，第二个用于在插入失败时更新行（或反之亦然）。为了保持一致性，您必须在事务中执行查询。

    db.beginTransaction();
    try {
        long rowId = db.insert(table, null, values);
        if (rowId == -1) {
            // insertion failed
            String whereClause = "latitude=? AND longitude=?"; 
            String[] whereArgs = new String[] {values.getAsString("latitude"),
                    values.getAsString("longitude")};
            db.update(table, values, whereClause, whereArgs);
            // now you have to get rowId so you can return correct Uri from insert()
            // method of your content provider, so another db.query() is required
        }
        db.setTransactionSuccessful();
    } finally {
        db.endTransaction();
    }

- biegleux

Answer 4

使用insertWithOnConflict并将最后一个参数(conflictAlgorithm)设置为CONFLICT_REPLACE。

更多信息请参见以下链接:

insertWithOnConflict文档

CONFLICT_REPLACE标志

- Muzikant

Answer 5

对我来说，如果没有“_id”，那么这些方法都不起作用。

你应该先调用更新操作，如果受影响的行数为零，则使用忽略插入它：

 String selection = MessageDetailTable.SMS_ID+" =?";
 String[] selectionArgs =  new String[] { String.valueOf(md.getSmsId())};

 int affectedRows = db.update(MessageDetailTable.TABLE_NAME, values, selection,selectionArgs);

 if(affectedRows<=0) {
     long id = db.insertWithOnConflict(MessageDetailTable.TABLE_NAME, null, values, SQLiteDatabase.CONFLICT_IGNORE);
 }

- Amir Hossein Ghasemi

Answer 6

使用INSERT OR REPLACE。

这是正确的方法。

- user1154664