Android SQLite插入时如何将字段设为与自增ID同值？

Absolutely! You can eliminate the extra database call by using an SQLite trigger, which works across all Android versions. Here's how to implement it:

Use an AFTER INSERT Trigger #

Triggers let SQLite automatically perform actions when specific events (like inserting a row) occur. We’ll create a trigger that sets ID_2 to match the newly generated ID right after a row is inserted.

Step 1: Create the Trigger #

Run this SQL once (typically when initializing your database or during a schema upgrade):

CREATE TRIGGER IF NOT EXISTS set_id2_on_insert
AFTER INSERT ON YOUR_TABLE_NAME
BEGIN
    UPDATE YOUR_TABLE_NAME 
    SET ID_2 = NEW.ID 
    WHERE ID = NEW.ID;
END;

Replace YOUR_TABLE_NAME with your actual table name. The NEW keyword refers to the row that was just inserted, so NEW.ID gives us the auto-generated primary key value.

Step 2: Simplify Your Insert Code #

Now you can remove the entire UPDATE block from your code. Your simplified transaction will look like this:

mDb.beginTransaction();
ContentValues contentValues = new ContentValues();
contentValues.put(NAME, obj.getName());
// Insert the record—trigger handles setting ID_2 automatically
long objId = mDb.insert(TABLE_NAME, null, contentValues);
obj.setId(objId);
obj.setId2(objId); // Keep this to sync your in-memory object
mDb.setTransactionSuccessful();

The trigger runs atomically within your transaction, so ID_2 will be persisted without any extra database calls.

Why This Works #

When you insert a row without specifying ID, SQLite generates the auto-increment value. The trigger fires immediately after the insert, using that generated NEW.ID to update ID_2 for the same row. This removes the need for a separate UPDATE call entirely.

Alternative for Android 11+ (API 30+) #

If your app targets Android 11 or higher, you could use the RETURNING clause to fetch the generated ID in one step, but this doesn’t eliminate the need to set ID_2—the trigger is still the most efficient way to handle that automatically.

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