Android executeSQL 不起作用(插入表 2 从表 1 中选择 *)

Question

我必须将数据移动到一个表到另一个表。新表将与旧表一样，但没有约束（冲突替换），因此列相同。

这是我必须做的查询：INSERT INTO TABLE2 SELECT * FROM TABLE1

我

遇到了一个大问题，因为我认为我无法通过以下方式执行此类查询：

db.execSQL("INSERT INTO " + Tables.TRACKS + "_2  SELECT * FROM " + Tables.TRACKS );

execSQL 失败并显示此错误 （ db onUpgrade ）：

06-04 12:25:50.000: D/SqliteDatabaseCpp(17817): Registering sqlite logging func: /data/data/com.musixmatch.android.lyrify/databases/library.db
06-04 12:25:50.000: D/SqliteDatabaseCpp(17817): DB info: open db, path = /data/data/com.musixmatch.android.lyrify/databases , key = lafvYvq, flag = 6, file size = 101376
06-04 12:25:50.000: D/SqliteDatabaseCpp(17817): DB info: path = /data/data/com.musixmatch.android.lyrify/databases , key = lafvYvq, handle: 0x1bd8bd8, type: w, r/w: (0,1), mode: delete, disk free size: 1243 M

06-04 12:25:50.010: D/SqliteDatabaseCpp(17817): DB info: close db, path = /data/data/com.musixmatch.android.lyrify/databases , key = lafvYvq, handle = 0x1bd8bd8, type = w, r/w = (0, 0)
06-04 12:25:50.030: E/SQLiteOpenHelper(17817): Couldn't open library.db for writing (will try read-only):
06-04 12:25:50.030: E/SQLiteOpenHelper(17817): android.database.sqlite.SQLiteConstraintException: error code 19: constraint failed
06-04 12:25:50.030: E/SQLiteOpenHelper(17817):  at android.database.sqlite.SQLiteStatement.native_execute(Native Method)
06-04 12:25:50.030: E/SQLiteOpenHelper(17817):  at android.database.sqlite.SQLiteStatement.executeUpdateDelete(SQLiteStatement.java:95)
06-04 12:25:50.030: E/SQLiteOpenHelper(17817):  at android.database.sqlite.SQLiteDatabase.executeSql(SQLiteDatabase.java:2008)
06-04 12:25:50.030: E/SQLiteOpenHelper(17817):  at android.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:1948)
06-04 12:25:50.030: E/SQLiteOpenHelper(17817):  at com.designfuture.music.provider.MusicDatabase.alterTrackFragmentForFileIDSupport(MusicDatabase.java:316)
06-04 12:25:50.030: E/SQLiteOpenHelper(17817):  at com.designfuture.music.provider.MusicDatabase.onUpgrade(MusicDatabase.java:242)
06-04 12:25:50.030: E/SQLiteOpenHelper(17817):  at android.database.sqlite.SQLiteOpenHelper.getWritableDatabase(SQLiteOpenHelper.java:188)
06-04 12:25:50.030: E/SQLiteOpenHelper(17817):  at android.database.sqlite.SQLiteOpenHelper.getReadableDatabase(SQLiteOpenHelper.java:249)
06-04 12:25:50.030: E/SQLiteOpenHelper(17817):  at com.designfuture.music.provider.MusicProvider.query(MusicProvider.java:199)
06-04 12:25:50.030: E/SQLiteOpenHelper(17817):  at android.content.ContentProvider$Transport.query(ContentProvider.java:189)
06-04 12:25:50.030: E/SQLiteOpenHelper(17817):  at android.content.ContentResolver.query(ContentResolver.java:315)
06-04 12:25:50.030: E/SQLiteOpenHelper(17817):  at android.content.AsyncQueryHandler$WorkerHandler.handleMessage(AsyncQueryHandler.java:94)
06-04 12:25:50.030: E/SQLiteOpenHelper(17817):  at android.os.Handler.dispatchMessage(Handler.java:99)
06-04 12:25:50.030: E/SQLiteOpenHelper(17817):  at android.os.Looper.loop(Looper.java:156)
06-04 12:25:50.030: E/SQLiteOpenHelper(17817):  at android.os.HandlerThread.run(HandlerThread.java:60)
06-04 12:25:50.030: D/SqliteDatabaseCpp(17817): DB info: open db, path = /data/data/com.musixmatch.android.lyrify/databases , key = lafvYvq, flag = 1, file size = 101376
06-04 12:25:50.030: D/SqliteDatabaseCpp(17817): DB info: path = /data/data/com.musixmatch.android.lyrify/databases , key = lafvYvq, handle: 0x1bec760, type: r, r/w: (1,0), mode: delete, disk free size: 1243 M
06-04 12:25:50.030: D/SqliteDatabaseCpp(17817): DB info: close db, path = /data/data/com.musixmatch.android.lyrify/databases , key = lafvYvq, handle = 0x1bec760, type = r, r/w = (0, 0)
06-04 12:25:50.040: W/AsyncQuery(17817): Exception thrown during handling EVENT_ARG_QUERY
06-04 12:25:50.040: W/AsyncQuery(17817): android.database.sqlite.SQLiteException: Can't upgrade read-only database from version 7 to 9: /data/data/com.musixmatch.android.lyrify/databases/library.db
06-04 12:25:50.040: W/AsyncQuery(17817):    at android.database.sqlite.SQLiteOpenHelper.getReadableDatabase(SQLiteOpenHelper.java:262)
06-04 12:25:50.040: W/AsyncQuery(17817):    at com.designfuture.music.provider.MusicProvider.query(MusicProvider.java:199)
06-04 12:25:50.040: W/AsyncQuery(17817):    at android.content.ContentProvider$Transport.query(ContentProvider.java:189)
06-04 12:25:50.040: W/AsyncQuery(17817):    at android.content.ContentResolver.query(ContentResolver.java:315)
06-04 12:25:50.040: W/AsyncQuery(17817):    at android.content.AsyncQueryHandler$WorkerHandler.handleMessage(AsyncQueryHandler.java:94)
06-04 12:25:50.040: W/AsyncQuery(17817):    at android.os.Handler.dispatchMessage(Handler.java:99)
06-04 12:25:50.040: W/AsyncQuery(17817):    at android.os.Looper.loop(Looper.java:156)
06-04 12:25:50.040: W/AsyncQuery(17817):    at android.os.HandlerThread.run(HandlerThread.java:60)

我认为这是因为我在查询中有一个选择......这是来自Android文档：http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html#execSQL%28java.lang.String%29

执行不是 SELECT 或任何其他 SQL 的单个 SQL 语句 返回数据的语句。

如果我使用

db.rawQuery( "INSERT INTO " + Tables.TRACKS + "_2  SELECT * FROM " + Tables.TRACKS, null  );

它执行没有错误，但它不做任何事情。我认为这是因为 rawQuery 用于执行 SELECT 查询并且不会写入数据库。

更新：如果我使用普通查询选择所有记录，则通过循环将它们插入新表中一切正常，但这不是优化的方式，我不想有一个缓慢的升级应用程序

UPDATE2：

以下是这两个表之间的唯一区别：

新

表有 3 个新字符串列，它们可以为空新表不会有旧表具有的唯一（冲突替换）约束

两个表都有以下字段：BaseColumns._ID + " INTEGER PRIMARY KEY AUTOINCREMENT" 。我必须从插入中排除该字段吗？另请记住，第二个平板电脑在升级时是空的（我在升级时创建它）

那么，我该怎么做才能执行需要与我的查询同时写入和读取的查询呢？

Answer 1

由于您没有发布表格布局，因此让我们从以下内容开始：

CREATE TABLE TABLE1 (
   _id INTEGER PRIMARY KEY,
   value1 TEXT,
   value2 TEXT
);
CREATE TABLE TABLE2 (
   _id INTEGER PRIMARY KEY,
   value2 TEXT,
   value1 TEXT,
   value3 TEXT,
   value4 TEXT,
   value5 TEXT
);

要正确地将TABLE1中的值插入到TABLE2中，您必须考虑顺序和列数很重要并且必须匹配（如果您有主键，最好从 insert & select 语句中省略它们 - 不要四处复制这样的东西）。

INSERT INTO TABLE2 (value1, value2) SELECT value1, value2 FROM TABLE1