Flutter SQLite数据类型不匹配异常(code20)排查求助

问题 #

Flutter开发中遇到DatabaseException(datatype mismatch (code 20 SQLITE_MISMATCH))异常，尝试将pincode改为int等方案无效，无法定位问题根源。

相关代码与数据 #

创建表代码 #

database = await openDatabase(join(await getDatabasesPath(), "abc.db"),
        version: 1, onCreate: (Database db, int version) async {
          
          await db.execute(
            "CREATE TABLE $tableName(${AppConstants.studentId} INTEGER PRIMARY KEY, ${AppConstants.eleID} TEXT, ${AppConstants.firstName} TEXT, ${AppConstants.middleName} TEXT, lastName TEXT, ${AppConstants.dob} TEXT, ${AppConstants.bloodGroup} TEXT, ${AppConstants.gender} TEXT,"
                " ${AppConstants.studentClass} TEXT, ${AppConstants.adharCard} TEXT, ${AppConstants.address1} TEXT, ${AppConstants.address2} TEXT, ${AppConstants.address3} TEXT, ${AppConstants.city} TEXT, ${AppConstants.state} TEXT,"
                " ${AppConstants.pincode} VARCHAR(10), ${AppConstants.images} VARCHAR(100), ${AppConstants.bloodType} TEXT, ${AppConstants.routeId} TEXT,${AppConstants.schoolId} TEXT, ${AppConstants.parents} TEXT, ${AppConstants.emails} TEXT, ${AppConstants.lat} TEXT, ${AppConstants.lng} TEXT)",
          );
        });

报错信息 #

[ERROR:flutter/runtime/dart_vm_initializer.cc(41)] Unhandled Exception: DatabaseException(datatype mismatch (code 20 SQLITE_MISMATCH)) sql 'INSERT OR REPLACE INTO student (studentId, eleID, firstName, middleName, lastName, dob, bloodGroup, gender, studentClass, adharCard, address1, address2, address3, city, state, pincode, images, bloodType, routeId, schoolId, parents, emails, lat, lng) VALUES (?, ?, ?, ?, ?, ?, NULL, ?, ?, NULL, ?, ?, ?, ?, ?, ?, NULL, NULL, NULL, ?, NULL, NULL, NULL, NULL)' args [3a2bef43-784c-433f-82e0-c45f45bdc3ec, ELIDPPS002, abc, S, cdcdcd, Sat Apr 03 00:00:00 EDT 2010, FEMALE, 7, T96502, Amanora Park Town, Neo Towers, Hadapsar, Pune, Maharashtra, 411028, 5ea3ae2f-722a-44ce-9ea8-d2ce3a1706a1]

接口返回数据 #

response: {studentId: 3a2bef43-784c-433f-82e0-c45f45bdc3ec, eleID: ELIDPPS002, firstName: abc, middleName: S, lastName: cdcdcd, dob: Sat Apr 03 00:00:00 EDT 2010, bloodGroup: null, gender: FEMALE, studentClass: 7, adharCard: null, address1: T96502, Amanora Park Town, address2: Neo Towers, address3: Hadapsar, city: Pune, state: Maharashtra, pincode: 411028, images: null, bloodType: null, parentIds: [87259c0c-4770-4eb9-8e88-2d42ad598954, 8dae86f3-d6a0-4c67-aaa5-40a4a5249576], routeId: null, schoolId: 5ea3ae2f-722a-44ce-9ea8-d2ce3a1706a1, parents: null, emails: null, lat: null, lng: null}

toJson方法 #

Map<String, dynamic> toJson() {
    final Map<String, dynamic> data = new Map<String, dynamic>();
    data[AppConstants.studentId] = studentId;
    data[AppConstants.eleID] = eleID;
    data[AppConstants.firstName] = firstName;
    data[AppConstants.middleName] = middleName;
    data[AppConstants.lastName] = lastName;
    data[AppConstants.dob] = dob;
    data[AppConstants.bloodGroup] = bloodGroup;
    data[AppConstants.gender] = gender;
    data[AppConstants.studentClass] = studentClass;
    // data[AppConstants.studentClass] = studentClass??-1;
    data[AppConstants.adharCard] = adharCard;
    data[AppConstants.address1] = address1;
    data[AppConstants.address2] = address2;
    data[AppConstants.address3] = address3;
    data[AppConstants.city] = city;
    data[AppConstants.state] = state;
    data[AppConstants.pincode] = pincode!=null?pincode.toString():null;
    data[AppConstants.images] = images;
    data[AppConstants.bloodType] = bloodType;
    // data[AppConstants.parentIds] = null;
    data[AppConstants.routeId] = routeId;
    data[AppConstants.schoolId] = schoolId;
    data[AppConstants.parents] = parents;
    data[AppConstants.emails] = emails;
    data[AppConstants.lat] = lat;
    data[AppConstants.lng] = lng;
    return data;
  }

排查分析 #

核心问题在于studentId字段类型不匹配：

• 创建表时studentId被定义为INTEGER PRIMARY KEY，但实际传入的是字符串格式的UUID（如3a2bef43-784c-433f-82e0-c45f45bdc3ec），SQLite无法将字符串存入INTEGER类型字段，触发SQLITE_MISMATCH异常。
• 之前关注的pincode字段已通过toString()转为字符串，与表中VARCHAR(10)类型兼容，不是异常根源。

解决方案 #

1. 修改表结构：将studentId的类型改为TEXT PRIMARY KEY，适配UUID字符串：

await db.execute(
  "CREATE TABLE $tableName(${AppConstants.studentId} TEXT PRIMARY KEY, ${AppConstants.eleID} TEXT, ${AppConstants.firstName} TEXT, ${AppConstants.middleName} TEXT, lastName TEXT, ${AppConstants.dob} TEXT, ${AppConstants.bloodGroup} TEXT, ${AppConstants.gender} TEXT,"
      " ${AppConstants.studentClass} TEXT, ${AppConstants.adharCard} TEXT, ${AppConstants.address1} TEXT, ${AppConstants.address2} TEXT, ${AppConstants.address3} TEXT, ${AppConstants.city} TEXT, ${AppConstants.state} TEXT,"
      " ${AppConstants.pincode} VARCHAR(10), ${AppConstants.images} VARCHAR(100), ${AppConstants.bloodType} TEXT, ${AppConstants.routeId} TEXT,${AppConstants.schoolId} TEXT, ${AppConstants.parents} TEXT, ${AppConstants.emails} TEXT, ${AppConstants.lat} TEXT, ${AppConstants.lng} TEXT)",
);

2. 更新数据库版本：修改openDatabase的version参数（比如从1改为2），并添加onUpgrade方法处理表结构更新：

database = await openDatabase(join(await getDatabasesPath(), "abc.db"),
  version: 2,
  onCreate: (Database db, int version) async {
    // 新建表逻辑（用修改后的SQL）
    await db.execute(/* 修改后的CREATE TABLE语句 */);
  },
  onUpgrade: (Database db, int oldVersion, int newVersion) async {
    if (oldVersion < 2) {
      // 升级逻辑：可以删除旧表重建，或者ALTER TABLE修改字段类型（SQLite修改字段类型需重建表）
      await db.execute("DROP TABLE IF EXISTS $tableName");
      await db.execute(/* 修改后的CREATE TABLE语句 */);
    }
  },
);

3. 开发阶段测试：如果是开发环境，也可以直接卸载应用后重新安装，让数据库重新执行onCreate创建新表结构。

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