Flutter Sqflite插入报错：income_table无workType列求助

尝试向SQLite的income_table表插入包含workType和money字段的数据时，系统提示该表不存在workType列，但DatabaseHelper类中已定义该列，多次尝试无法解决，求排查。

错误日志 #

I/flutter (10808): Save button clicked
E/SQLiteLog(10808): (1) table income_table has no column named workType
E/flutter (10808): [ERROR:flutter/runtime/dart_vm_initializer.cc(41)] Unhandled Exception: DatabaseException(table income_table has no column named workType (code 1): , while compiling: INSERT INTO income_table (workType, money) VALUES (?, ?)) sql 'INSERT INTO income_table (workType, money) VALUES (?, ?)' args [tih, wj]
E/flutter (10808): #0      wrapDatabaseException (package:sqflite/src/exception_impl.dart:11:7)
E/flutter (10808): <asynchronous suspension>
E/flutter (10808): #1      SqfliteDatabaseMixin.txnRawInsert.<anonymous closure> (package:sqflite_common/src/database_mixin.dart:549:14)
E/flutter (10808): <asynchronous suspension>
E/flutter (10808): #2      BasicLock.synchronized (package:synchronized/src/basic_lock.dart:33:16)
E/flutter (10808): <asynchronous suspension>
E/flutter (10808): #3      SqfliteDatabaseMixin.txnSynchronized (package:sqflite_common/src/database_mixin.dart:490:14)
E/flutter (10808): <asynchronous suspension>
E/flutter (10808): #4      _IncomePageState._Save (package:expense/screens/incomepage.dart:133:16)
E/flutter (10808): <asynchronous suspension>
E/flutter (10808):

DatabaseHelper代码 #

import 'Income.dart';
import 'package:sqflite/sqflite.dart';
import 'dart:async';
import 'dart:io';
import 'package:path_provider/path_provider.dart';

class DatabaseHelper {
  static final DatabaseHelper databaseHelper = DatabaseHelper._createInstance();
  static Database? _database;

  String incomeTable = 'income_table';
  String colID = 'id';
  String colWorkType = 'workType';
  String colMoney = 'money';

  DatabaseHelper._createInstance();

  Future<Database?> get database async {
    if (_database != null) {
      return _database;
    }
    _database = await initializeDatabase();
    return _database;
  }

  Future<Database> initializeDatabase() async {
    Directory directory = await getApplicationDocumentsDirectory();
    String path = directory.path + 'incomes.db';

    var incomeDatabase = openDatabase(path, version: 1, onCreate: _createDb);
    return incomeDatabase;
  }

  void _createDb(Database db, int newVersion) async {
    db
        .execute(
            'CREATE TABLE $incomeTable($colID INTEGER PRIMARY KEY AUTOINCREMENT,$colWorkType TEXT,$colMoney TEXT)')
        .catchError((error) => print(error.toString()));
  }

  Future<List<Map<String, dynamic>>> getIncomeMapList() async {
    Database? db = await database;
    var result = db!.query(incomeTable, orderBy: '$colMoney ASC');
    return result;
  }

  Future<int> insertIncome(Income income) async {
    Database? db = await database;
    var result = db!.insert(incomeTable, income.toMap());
    return result;
  }

  Future<int> updateIncome(Income income) async {
    Database? db = await database;
    var result = await db!.update(incomeTable, income.toMap(),
        where: '$colID=?', whereArgs: [income.id]);
    return result;
  }

  Future<int> deleteIncome(int? id) async {
    Database? db = await database;
    var result =
        await db!.rawDelete('DELETE FROM $incomeTable where $colID=$id');
    return result;
  }

  Future<int?> getCount() async {
    Database? db = await database;
    List<Map<String, dynamic>> x =
        await db!.rawQuery('SELECT COUNT(*) from $incomeTable');
    int? result = Sqflite.firstIntValue(x);
    return result;
  }

  Future<List<Income>> getIncomeList() async {
    var incomeMapList = await getIncomeMapList();

    int count = incomeMapList.length;

    List<Income> incomeList = <Income>[];
    for (var i = 0; i < count; i++) {
      incomeList.add(Income.fromMapObject(incomeMapList[i]));
    }
    return incomeList;
  }
}

排查方案 #

• 清除旧数据库文件
  SQLite的onCreate仅在数据库首次创建时执行，若之前创建过无workType列的表，修改代码不会自动更新表结构。直接卸载应用重新安装，或手动删除应用存储目录下的incomes.db文件。

• 升级数据库版本并添加表结构更新逻辑
  把数据库版本号从1改为2，同时新增onUpgrade方法来添加缺失列：

  Future<Database> initializeDatabase() async {
    Directory directory = await getApplicationDocumentsDirectory();
    String path = directory.path + 'incomes.db';
  
    var incomeDatabase = openDatabase(
      path, 
      version: 2, 
      onCreate: _createDb,
      onUpgrade: _upgradeDb
    );
    return incomeDatabase;
  }
  
  Future<void> _upgradeDb(Database db, int oldVersion, int newVersion) async {
    if (oldVersion < 2) {
      await db.execute('ALTER TABLE $incomeTable ADD COLUMN $colWorkType TEXT');
    }
  }
  

• 修正表创建语句的细节
  给SQL语句中列之间添加空格，同时确保_createDb方法等待表创建完成：

  Future<void> _createDb(Database db, int newVersion) async {
    await db.execute(
      'CREATE TABLE $incomeTable($colID INTEGER PRIMARY KEY AUTOINCREMENT, $colWorkType TEXT, $colMoney TEXT)'
    );
  }
  

• 校验Income类的字段映射
  检查Income类的toMap方法，确保workType的键名与数据库列名完全一致（SQLite列名大小写敏感）：

  Map<String, dynamic> toMap() {
    return {
      'workType': this.workType,
      'money': this.money
    };
  }
  

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