为何类型一致仍出现SQLite 'datatype mismatch'（数据类型不匹配）错误？

问题场景 #

在包含普通表meta和关联它的FTS5虚拟表search的SQLite数据库中，执行数据插入操作时触发SQLite Error 20: 'datatype mismatch'错误，已检查变量类型未发现问题。

关联C#代码 #

using Microsoft.Data.Sqlite;

namespace SqliteExample
{
    struct Track
    {
        public string FilePath;
        public string Artist;
        public string Title;

        public Track(string file_path, string artist, string title)
        {
            FilePath = file_path;
            Artist = artist;
            Title = title;
        }
    }

    class Program
    {
        static async Task Main(string[] args)
        {
            // 获取数据库连接
            var connection = await Init();

            await InsertTracks(connection, [new Track("test_path", "test_artist", "test_title")]);
        }

        static async Task<SqliteConnection> Init()
        {
            // 建立并打开数据库连接
            var connection = new SqliteConnection("Data Source=meta.db");
            await connection.OpenAsync();

            // 不存在则创建表
            using (var command = connection.CreateCommand())
            {
                Console.WriteLine("\nCreating tables...");
                // 创建元数据表
                command.CommandText = """
                CREATE TABLE IF NOT EXISTS meta (
                    file TEXT PRIMARY KEY,
                    artist TEXT,
                    title TEXT
                )
                """;

                await command.ExecuteNonQueryAsync();

                // 创建FTS5虚拟表
                command.CommandText = """
                CREATE VIRTUAL TABLE IF NOT EXISTS search USING fts5(
                    artist,
                    title,
                    content='meta',
                    content_rowid='file'
                );
                """;

                await command.ExecuteNonQueryAsync();

                // 创建同步触发器
                command.CommandText = """
                CREATE TRIGGER IF NOT EXISTS insert_search AFTER INSERT ON meta BEGIN
                    INSERT INTO search(rowid, artist, title) VALUES (new.file, new.artist, new.title);
                END;
                CREATE TRIGGER IF NOT EXISTS update_search AFTER UPDATE ON meta BEGIN
                    INSERT INTO search(search, rowid, artist, title) VALUES ('delete', old.file, old.artist, old.title);
                    INSERT INTO search(rowid, artist, title) VALUES (new.file, new.artist, new.title);
                END;
                CREATE TRIGGER IF NOT EXISTS delete_search AFTER DELETE ON meta BEGIN
                    INSERT INTO search(search, rowid, artist, title) VALUES ('delete', old.file, old.artist, old.title);
                END;
                """;

                await command.ExecuteNonQueryAsync();
            }

            return connection;
        }

        static async Task InsertTracks(SqliteConnection connection, Track[] tracks)
        {
            using (var command = connection.CreateCommand())
            {
                Console.WriteLine("\nInserting entries...");
                command.CommandText = "INSERT INTO meta (file, artist, title) VALUES ($path, $artist, $title)";

                foreach (Track track in tracks)
                {
                    command.Parameters.Add("$path", SqliteType.Text).Value = track.FilePath;
                    command.Parameters.Add("$artist", SqliteType.Text).Value = track.Artist;
                    command.Parameters.Add("$title", SqliteType.Text).Value = track.Title;

                    await command.ExecuteNonQueryAsync();
                }
            }
        }
    }
}

完整错误信息 #

Unhandled exception. Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 20: 'datatype mismatch'.
   at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db)
   at Microsoft.Data.Sqlite.SqliteDataReader.NextResult()
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior)
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader()
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteNonQuery()
   at System.Data.Common.DbCommand.ExecuteNonQueryAsync(CancellationToken cancellationToken)
--- End of stack trace from previous location ---
   at SqliteExample.Program.InsertTracks(SqliteConnection connection, Track[] tracks) in E:\Documents\projects\sqlite-in-csharp\SqliteExample\Program.cs:line 201
   at SqliteExample.Program.Main(String[] args) in E:\Documents\projects\sqlite-in-csharp\SqliteExample\Program.cs:line 39
   at SqliteExample.Program.<Main>(String[] args)

问题原因 #

FTS5虚拟表的rowid字段默认是整数类型，但触发器中把meta表的file字段（TEXT类型）当作search的rowid插入，直接导致类型不匹配。

修复方案 #

方案1：修改meta表主键为整数类型（推荐） #

将meta表的主键改为整数自增ID，file字段设为唯一约束，符合SQLite的rowid设计规范：

CREATE TABLE IF NOT EXISTS meta (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    file TEXT UNIQUE NOT NULL,
    artist TEXT,
    title TEXT
);

同步更新FTS5虚拟表和触发器：

CREATE VIRTUAL TABLE IF NOT EXISTS search USING fts5(
    artist,
    title,
    content='meta',
    content_rowid='id'
);

-- 插入触发器
CREATE TRIGGER IF NOT EXISTS insert_search AFTER INSERT ON meta BEGIN
    INSERT INTO search(rowid, artist, title) VALUES (new.id, new.artist, new.title);
END;

-- 更新触发器
CREATE TRIGGER IF NOT EXISTS update_search AFTER UPDATE ON meta BEGIN
    INSERT INTO search(search, rowid, artist, title) VALUES ('delete', old.id, old.artist, old.title);
    INSERT INTO search(rowid, artist, title) VALUES (new.id, new.artist, new.title);
END;

-- 删除触发器
CREATE TRIGGER IF NOT EXISTS delete_search AFTER DELETE ON meta BEGIN
    INSERT INTO search(search, rowid, artist, title) VALUES ('delete', old.id, old.artist, old.title);
END;

方案2：使用FTS5无内容模式（保留TEXT主键） #

如果必须用file作为TEXT类型主键，改用FTS5的无内容模式，避免rowid类型冲突：

CREATE VIRTUAL TABLE IF NOT EXISTS search USING fts5(
    file,
    artist,
    title,
    content=''
);

触发器调整为直接插入所有字段：

CREATE TRIGGER IF NOT EXISTS insert_search AFTER INSERT ON meta BEGIN
    INSERT INTO search(file, artist, title) VALUES (new.file, new.artist, new.title);
END;

CREATE TRIGGER IF NOT EXISTS update_search AFTER UPDATE ON meta BEGIN
    INSERT INTO search(search, file, artist, title) VALUES ('delete', old.file, old.artist, old.title);
    INSERT INTO search(file, artist, title) VALUES (new.file, new.artist, new.title);
END;

CREATE TRIGGER IF NOT EXISTS delete_search AFTER DELETE ON meta BEGIN
    INSERT INTO search(search, file, artist, title) VALUES ('delete', old.file, old.artist, old.title);
END;

额外代码优化 #

InsertTracks方法中每次循环重复添加参数会导致参数冗余，建议提前定义参数，循环内仅更新值：

static async Task InsertTracks(SqliteConnection connection, Track[] tracks)
{
    using (var command = connection.CreateCommand())
    {
        Console.WriteLine("\nInserting entries...");
        command.CommandText = "INSERT INTO meta (file, artist, title) VALUES ($path, $artist, $title)";

        // 提前定义参数，避免重复添加
        var pathParam = command.Parameters.Add("$path", SqliteType.Text);
        var artistParam = command.Parameters.Add("$artist", SqliteType.Text);
        var titleParam = command.Parameters.Add("$title", SqliteType.Text);

        foreach (Track track in tracks)
        {
            pathParam.Value = track.FilePath;
            artistParam.Value = track.Artist;
            titleParam.Value = track.Title;

            await command.ExecuteNonQueryAsync();
        }
    }
}

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