How do I check in SQLite whether a table exists?

How do I, reliably , check in SQLite, whether a particular user table exists?

I am not asking for unreliable ways like checking if a "select *" on the table returned an error or not (is this even a good idea?).

The reason is like this:

In my program, I need to create and then populate some tables if they do not exist already.

If they do already exist, I need to update some tables.

Should I take some other path instead to signal that the tables in question have already been created - say for example, by creating/putting/setting a certain flag in my program initialization/settings file on disk or something?

Or does my approach make sense?

---

I missed that FAQ entry.

Anyway, for future reference, the complete query is:

SELECT name FROM sqlite_master WHERE type='table' AND name='{table_name}';

Where {table_name} is the name of the table to check.

Documentation section for reference: Database File Format. 2.6. Storage Of The SQL Database Schema

---

If you're using SQLite version 3.3+ you can easily create a table with:

create table if not exists TableName (col1 typ1, ..., colN typN)

In the same way, you can remove a table only if it exists by using:

drop table if exists TableName

---

A variation would be to use SELECT COUNT(*) instead of SELECT NAME, ie

SELECT count(*) FROM sqlite_master WHERE type='table' AND name='table_name';

This will return 0, if the table doesn't exist, 1 if it does. This is probably useful in your programming since a numerical result is quicker / easier to process. The following illustrates how you would do this in Android using SQLiteDatabase, Cursor, rawQuery with parameters.

boolean tableExists(SQLiteDatabase db, String tableName)
{
    if (tableName == null || db == null || !db.isOpen())
    {
        return false;
    }
    Cursor cursor = db.rawQuery("SELECT COUNT(*) FROM sqlite_master WHERE type = ? AND name = ?", new String[] {"table", tableName});
    if (!cursor.moveToFirst())
    {
        cursor.close();
        return false;
    }
    int count = cursor.getInt(0);
    cursor.close();
    return count > 0;
}

链接地址: http://www.djcxy.com/p/3338.html

上一篇: 由于索引导致记录数量增加，SQLite插入速度变慢

下一篇: 如何在SQLite中检查表是否存在？