使用Xamarin+SQLite保存表单时遇创建数据库列重复异常

Hey, let's work through that SQLite.SQLiteException: duplicate column error you're running into when trying to save form data with Xamarin and SQLite. First, let's recap your setup to make sure we're on the same page:

你的现有代码 #

MoodDatabaseController 初始化代码

SQLiteConnection db;
public MoodDatabaseController() {
    db = DependencyService.Get<ISQLite>().GetConnection();
    db.CreateTable<MoodEntry>();
}

MoodEntry 实体类

public class MoodEntry {
    [PrimaryKey, AutoIncrement]
    public int MoodEntryID { get; set; }
    [Indexed]
    public DateTime EntryDate { get; set; }
    // 其他字段...
}

Android端 GetConnection 实现

public SQLite_Android() { }
public SQLite.SQLiteConnection GetConnection() {
    var sqliteFileName = "myMood.db3";
    string documentsPath = System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal);
    var path = Path.Combine(documentsPath, sqliteFileName);
    var conn = new SQLite.SQLiteConnection(path);
    return conn;
}

错误原因分析 #

This error almost always pops up because:

• You've already created the MoodEntry table in the database before, but you've since modified your MoodEntry class (added a new field, changed a field's attribute, etc.)
• The CreateTable<MoodEntry>() method tries to create the table from scratch every time, and when it finds a column that already exists in the existing table, it throws the duplicate column exception.
• It could also be leftover cache from previous app installs where the old table structure didn't get cleaned up.

解决方法 #

Here are a few ways to fix this, depending on whether you're in development or production:

1. 删除旧数据库（仅开发测试阶段使用）

If you're still testing and don't care about losing existing test data, just wipe the old database file:

• On an Android device/emulator: Go to Settings > Apps > [Your App] > Storage > Clear Data
• This will delete the myMood.db3 file entirely, so next time your app runs, it'll create a fresh table that matches your current MoodEntry class.

2. 使用自动迁移的CreateTable重载（推荐开发阶段）

Modify your CreateTable call to use a CreateFlags parameter that lets SQLite.NET auto-migrate the table structure instead of trying to create it from scratch:

// 自动迁移表结构，添加新列而不报错
db.CreateTable<MoodEntry>(CreateFlags.AutoMigrate);

Or use CreateFlags.AllImplicit if you want to enable all implicit behaviors (including auto-migration):

db.CreateTable<MoodEntry>(CreateFlags.AllImplicit);

This will detect differences between your MoodEntry class and the existing table, then add new columns automatically without throwing duplicate errors.

3. 手动编写迁移脚本（生产环境适用）

If your app is already live and you can't delete user data, you'll need to manually alter the table to match your updated MoodEntry class. For example, if you added a new MoodRating integer field:

// 先检查目标列是否已经存在
var columnExists = db.ExecuteScalar<int>("PRAGMA table_info(MoodEntry) WHERE name = 'MoodRating'") > 0;
if (!columnExists)
{
    // 执行ALTER语句添加新列
    db.Execute("ALTER TABLE MoodEntry ADD COLUMN MoodRating INTEGER");
}
// 确保表存在（如果是首次安装）
db.CreateTable<MoodEntry>(CreateFlags.None);

This approach is more controlled and ensures you don't lose any user data during structure updates.

额外注意事项 #

• Always test migrations thoroughly before pushing to production
• For development, sticking with CreateFlags.AutoMigrate will save you a lot of hassle when iterating on your entity classes
• Double-check that your entity attributes (like [PrimaryKey], [Indexed]) match the existing table structure if you're not using auto-migration

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