Android Studio中SQLite模拟器正常但真机报错：表无sensor2列

我的SQLite相关代码在Android模拟器运行正常，但真机上执行时报错，提示board_table_0表不存在sensor2列。已经定位到MyDbHandler类的addSensorData()方法，但没发现明显错误。以下是错误日志及相关代码：

错误日志 #

E/SQLiteLog: (1) table board_table_0 has no column named sensor2 in "INSERT INTO board_table_0(time,sensor1,sensor2,sensor3,sensor4,sensor5,sensor6) VALUES (?,?,?,?,?,?,?)"
E/SQLiteDatabase: Error inserting time=00 sensor1=00 sensor2=00 sensor3=00 sensor4=00 sensor5=00 sensor6=00
    android.database.sqlite.SQLiteException: table board_table_0 has no column named sensor2 (code 1 SQLITE_ERROR): , while compiling: INSERT INTO board_table_0(time,sensor1,sensor2,sensor3,sensor4,sensor5,sensor6) VALUES (?,?,?,?,?,?,?)
        at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
        at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:1045)
        at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:652)
        at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:590)
        at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:61)
        at android.database.sqlite.SQLiteStatement.<init>(SQLiteStatement.java:33)
        at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1699)
        at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1570)
        at com.example.uwowirelessmotionsensortrackingapp.data.MyDbHandler.addSensorData(MyDbHandler.java:59)
        at com.example.uwowirelessmotionsensortrackingapp.Test.onCreate(Test.java:40)
        at android.app.Activity.performCreate(Activity.java:8108)
        at android.app.Activity.performCreate(Activity.java:8092)
        at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1309)
        at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:3492)
        at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:3694)
        at android.app.servertransaction.LaunchActivityItem.execute(LaunchActivityItem.java:85)
        at android.app.servertransaction.TransactionExecutor.executeCallbacks(TransactionExecutor.java:135)
        at android.app.servertransaction.TransactionExecutor.execute(TransactionExecutor.java:95)
        at android.app.ActivityThread$H.handleMessage(ActivityThread.java:2143)
        at android.os.Handler.dispatchMessage(Handler.java:106)
        at android.os.Looper.loop(Looper.java:250)
        at android.app.ActivityThread.main(ActivityThread.java:7868)
        at java.lang.reflect.Method.invoke(Native Method)
        at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:592)
        at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:958)

Test类代码 #

public class Test extends AppCompatActivity {
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_device_options);
    //创建数据库
    MyDbHandler db = new MyDbHandler(Test.this);

    //创建Board对象
    Board dbBoard0 = new Board();

for(int i=0;i<10;i++){
    dbBoard0.setTime("00");
    dbBoard0.setSensor1("00");
    dbBoard0.setSensor2("00");
    dbBoard0.setSensor3("00");
    dbBoard0.setSensor4("00");
    dbBoard0.setSensor5("00");
    dbBoard0.setSensor6("00");
    db.addSensorData(dbBoard0, Params.BOARD_TABLE_0);

    //获取所有数据
    List<Board> allSensorData = db.getAllData(Params.BOARD_TABLE_0);

    for(Board tempBoard: allSensorData){
        Log.d("finn", "\nId: " + tempBoard.getId() + "\n" +
                "Time: " + tempBoard.getTime() + "\n"
                + "Sensor 1 Data: " + tempBoard.getSensor1() + "\n"
                + "Sensor 2 Data: " + tempBoard.getSensor2() + "\n"
                + "Sensor 3 Data: " + tempBoard.getSensor3() + "\n"
                + "Sensor 4 Data: " + tempBoard.getSensor4() + "\n"
                + "Sensor 5 Data: " + tempBoard.getSensor5() + "\n"
                + "Sensor 6 Data: " + tempBoard.getSensor6() + "\n");
    }
}

}}

MyDbHandler类代码 #

public class MyDbHandler extends SQLiteOpenHelper {

    public MyDbHandler(Context context) {
        super(context, Params.DB_NAME, null, Params.DB_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String create = "CREATE TABLE " + Params.BOARD_TABLE_0 + "("
                + Params.KEY_ID + " INTEGER PRIMARY KEY," + Params.KEY_TIME
                + " TEXT,"
                + Params.KEY_SENSOR1 + " TEXT,"
                +Params.KEY_SENSOR2 + " TEXT,"
                +Params.KEY_SENSOR3 + " TEXT,"
                +Params.KEY_SENSOR4 + " TEXT,"
                + Params.KEY_SENSOR5 + " TEXT,"
                + Params.KEY_SENSOR6 + " TEXT" +")";

        Log.d("finn", "执行的SQL语句: "+ create);
        db.execSQL(create);

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }

    public void addSensorData(Board board, String table){
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(Params.KEY_TIME, board.getTime());
        values.put(Params.KEY_SENSOR1, board.getSensor1());
        values.put(Params.KEY_SENSOR2, board.getSensor2());
        values.put(Params.KEY_SENSOR3, board.getSensor3());
        values.put(Params.KEY_SENSOR4, board.getSensor4());
        values.put(Params.KEY_SENSOR5, board.getSensor5());
        values.put(Params.KEY_SENSOR6, board.getSensor6());

        db.insert(table, null, values);
        Log.d("finn", "插入成功");
        db.close();
    }

    public List<Board> getAllData(String table){
        List<Board> sensorDataList = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();

        //查询语句
        String select = "SELECT * FROM " + table;
        Cursor cursor = db.rawQuery(select, null);

        //遍历结果
        if(cursor.moveToFirst()){
            do{
                Board board = new Board();
                board.setId(Integer.parseInt(cursor.getString(0)));
                board.setTime(cursor.getString(1));
                board.setSensor1(cursor.getString(2));
                board.setSensor2(cursor.getString(3));
                board.setSensor3(cursor.getString(4));
                board.setSensor4(cursor.getString(5));
                board.setSensor5(cursor.getString(6));
                board.setSensor6(cursor.getString(7));
                sensorDataList.add(board);
            }while(cursor.moveToNext());
        }
        return sensorDataList;
    }

}

Params类代码 #

public class Params {

    public static final int DB_VERSION = 1;
    public static final String DB_NAME = "board_data_db";

    public static final String BOARD_TABLE_0 = "board_table_0";
    public static final String BOARD_TABLE_1 = "board_table_1";
    public static final String BOARD_TABLE_2 = "board_table_2";
    public static final String BOARD_TABLE_3 = "board_table_3";
    public static final String BOARD_TABLE_4 = "board_table_4";
    public static final String BOARD_TABLE_5 = "board_table_5";

    //表字段名
    public static final String KEY_ID = "id";
    public static final String KEY_TIME = "time";
    public static final String KEY_SENSOR1 = "sensor1";
    public static final String KEY_SENSOR2 = "sensor2";
    public static final String KEY_SENSOR3 = "sensor3";
    public static final String KEY_SENSOR4 = "sensor4";
    public static final String KEY_SENSOR5 = "sensor5";
    public static final String KEY_SENSOR6 = "sensor6";
}

Board类代码 #

public class Board {

    private int id;
    private String time;
    private String sensor1;
    private String sensor2;
    private String sensor3;
    private String sensor4;
    private String sensor5;
    private String sensor6;

    public Board(int id, String time, String sensor1, String sensor2, String sensor3, String sensor4, String sensor5, String sensor6) {
        this.id = id;
        this.time = time;
        this.sensor1 = sensor1;
        this.sensor2 = sensor2;
        this.sensor3 = sensor3;
        this.sensor4 = sensor4;
        this.sensor5 = sensor5;
        this.sensor6 = sensor6;
    }

    public Board(String time, String sensor1, String sensor2, String sensor3, String sensor4, String sensor5, String sensor6) {
        this.time = time;
        this.sensor1 = sensor1;
        this.sensor2 = sensor2;
        this.sensor3 = sensor3;
        this.sensor4 = sensor4;
        this.sensor5 = sensor5;
        this.sensor6 = sensor6;
    }

    public Board() {

    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getTime() {
        return time;
    }

    public void setTime(String time) {
        this.time = time;
    }

    public String getSensor1() {
        return sensor1;
    }

    public void setSensor1(String sensor1) {
        this.sensor1 = sensor1;
    }

    public String getSensor2() {
        return sensor2;
    }

    public void setSensor2(String sensor2) {
        this.sensor2 = sensor2;
    }

    public String getSensor3() {
        return sensor3;
    }

    public void setSensor3(String sensor3) {
        this.sensor3 = sensor3;
    }

    public String getSensor4() {
        return sensor4;
    }

    public void setSensor4(String sensor4) {
        this.sensor4 = sensor4;
    }

    public String getSensor5() {
        return sensor5;
    }

    public void setSensor5(String sensor5) {
        this.sensor5 = sensor5;
    }

    public String getSensor6() {
        return sensor6;
    }

    public void setSensor6(String sensor6) {
        this.sensor6 = sensor6;
    }
}

问题原因及解决方法 #

核心原因 #

真机上的数据库是旧版本，未包含sensor2等新增列。模拟器测试时通常会重新安装APP，数据库会被重新创建，所以能匹配新表结构；但真机上APP已存在，旧数据库未被更新，且onUpgrade方法未实现，不会触发表结构变更逻辑。

解决步骤 #

1. 更新数据库版本：修改Params类中的版本号，从1改为更高数值：

   public static final int DB_VERSION = 2;
   

2. 实现onUpgrade方法：
   • 开发阶段可直接删除旧表重建（会丢失旧数据）：

     @Override
     public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
         db.execSQL("DROP TABLE IF EXISTS " + Params.BOARD_TABLE_0);
         onCreate(db);
     }
     

   • 需保留旧数据则用ALTER TABLE添加列：

     @Override
     public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
         if (oldVersion < 2) {
             db.execSQL("ALTER TABLE " + Params.BOARD_TABLE_0 + " ADD COLUMN " + Params.KEY_SENSOR2 + " TEXT");
             db.execSQL("ALTER TABLE " + Params.BOARD_TABLE_0 + " ADD COLUMN " + Params.KEY_SENSOR3 + " TEXT");
             db.execSQL("ALTER TABLE " + Params.BOARD_TABLE_0 + " ADD COLUMN " + Params.KEY_SENSOR4 + " TEXT");
             db.execSQL("ALTER TABLE " + Params.BOARD_TABLE_0 + " ADD COLUMN " + Params.KEY_SENSOR5 + " TEXT");
             db.execSQL("ALTER TABLE " + Params.BOARD_TABLE_0 + " ADD COLUMN " + Params.KEY_SENSOR6 + " TEXT");
         }
     }
     

3. 验证：在真机上重新安装APP或清除APP数据后运行，确认新表结构生效。

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