如何在SQLite中检查表是否存在?

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

如何可靠地检入SQLite，是否存在特定的用户表?

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?).

我不是在要求不可靠的方法，比如检查表上的“select *”是否返回错误(这甚至是一个好主意吗?)

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?

或者我的方法有意义吗?

17 个解决方案

#1

845

I missed that FAQ entry.

我错过了FAQ条目。

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.

其中{table_name}是要检查的表的名称。

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

参考文献部分:数据库文件格式。2.6。SQL数据库模式的存储

#2

476

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

如果您正在使用SQLite版本3.3+，您可以轻松创建一个表，其中包括:

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

#3

150

A variation would be to use SELECT COUNT(*) instead of SELECT NAME, i.e.

一个变体是使用SELECT COUNT(*)而不是SELECT NAME，例如。

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.

如果表不存在，则返回0，如果表存在，则返回1。这在您的编程中可能是有用的，因为数值结果更快/更容易处理。下面演示如何在Android中使用SQLiteDatabase、Cursor、rawQuery和参数来实现这一点。

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;
}

#4

You could try:

你可以试试:

SELECT name FROM sqlite_master WHERE name='table_name'

#5

If you are getting a "table already exists" error, make changes in the SQL string as below:

如果您得到一个“表已经存在”错误，那么在SQL字符串中进行如下更改:

CREATE table IF NOT EXISTS table_name (para1,para2);

This way you can avoid the exceptions.

这样可以避免异常。

#6

SQLite table names are case insensitive, but comparison is case sensitive by default. To make this work properly in all cases you need to add COLLATE NOCASE.

SQLite表名是不区分大小写的，但是默认情况下比较是区分大小写的。为了在所有情况下都能正常工作，您需要添加COLLATE NOCASE。

SELECT name FROM sqlite_master WHERE type='table' AND name='table_name' COLLATE NOCASE

#7

See this:

看到这个:

SELECT name FROM sqlite_master
WHERE type='table'
ORDER BY name;

#8

Use:

使用:

PRAGMA table_info(your_table_name)

If the resulting table is empty then your_table_name doesn't exist.

如果结果表为空，则your_table_name不存在。

Documentation:

文档:

PRAGMA schema.table_info(table-name);

编译指示schema.table_info(表名);

This pragma returns one row for each column in the named table. Columns in the result set include the column name, data type, whether or not the column can be NULL, and the default value for the column. The "pk" column in the result set is zero for columns that are not part of the primary key, and is the index of the column in the primary key for columns that are part of the primary key.

这个pragma为指定表中的每一列返回一行。结果集中的列包括列名、数据类型、列是否可以为空以及列的默认值。结果集中的“pk”列对于不属于主键的列是零，对于属于主键的列是主键中的列的索引。

The table named in the table_info pragma can also be a view.

在table_info pragma中命名的表也可以是一个视图。

Example output:

示例输出:

cid|name|type|notnull|dflt_value|pk
0|id|INTEGER|0||1
1|json|JSON|0||0
2|name|TEXT|0||0

#9

If you're using fmdb, I think you can just import FMDatabaseAdditions and use the bool function:

如果您正在使用fmdb，我认为您可以导入fmdatabaseadded并使用bool函数:

[yourfmdbDatabase tableExists:tableName].

#10

The following code returns 1 if the table exists or 0 if the table does not exist.

如果表不存在，那么下面的代码返回1，如果表不存在，则返回1。

SELECT CASE WHEN tbl_name = "name" THEN 1 ELSE 0 END FROM sqlite_master WHERE tbl_name = "name" AND type = "table"

#11

Note that to check whether a table exists in the TEMP database, you must use sqlite_temp_master instead of sqlite_master:

注意，要检查TEMP数据库中是否存在表，必须使用sqlite_temp_master而不是sqlite_master:

SELECT name FROM sqlite_temp_master WHERE type='table' AND name='table_name';

#12

Here's the function that I used:

这是我使用的函数:

Given an SQLDatabase Object = db

给定一个SQLDatabase对象= db

public boolean exists(String table) {
    try {
         db.query("SELECT * FROM " + table);
         return true;
    } catch (SQLException e) {
         return false;
    }
}

#13

Use this code:

使用这段代码:

SELECT name FROM sqlite_master WHERE type='table' AND name='yourTableName';

If the returned array count is equal to 1 it means the table exists. Otherwise it does not exist.

如果返回的数组计数为1，则表示该表存在。否则它就不存在。

#14

Use

使用

SELECT 1 FROM table LIMIT 1;

to prevent all records from being read.

防止所有记录被读取。

#15

Using a simple SELECT query is - in my opinion - quite reliable. Most of all it can check table existence in many different database types (SQLite / MySQL).

在我看来，使用简单的SELECT查询是相当可靠的。最重要的是，它可以在许多不同的数据库类型(SQLite / MySQL)中检查表的存在。

SELECT 1 FROM table;

It makes sense when you can use other reliable mechanism for determining if the query succeeded (for example, you query a database via QSqlQuery in Qt).

当您可以使用其他可靠的机制来确定查询是否成功时(例如，您可以在Qt中通过QSqlQuery查询数据库)，这是有意义的。

#16

This is my code for SQLite Cordova:

这是我对SQLite Cordova的编码:

get_columnNames('LastUpdate', function (data) {
    if (data.length > 0) { // In data you also have columnNames
        console.log("Table full");
    }
    else {
        console.log("Table empty");
    }
});

And the other one:

另一个:

function get_columnNames(tableName, callback) {
    myDb.transaction(function (transaction) {
        var query_exec = "SELECT name, sql FROM sqlite_master WHERE type='table' AND name ='" + tableName + "'";
        transaction.executeSql(query_exec, [], function (tx, results) {
            var columnNames = [];
            var len = results.rows.length;
            if (len>0){
                var columnParts = results.rows.item(0).sql.replace(/^[^\(]+\(([^\)]+)\)/g, '$1').split(','); ///// RegEx
                for (i in columnParts) {
                    if (typeof columnParts[i] === 'string')
                        columnNames.push(columnParts[i].split(" ")[0]);
                };
                callback(columnNames);
            }
            else callback(columnNames);
        });
    });
}

#17

I thought I'd put my 2 cents to this discussion, even if it's rather old one.. This query returns scalar 1 if the table exists and 0 otherwise.

我想我应该对这次讨论发表我的看法，即使是很旧的。如果表存在，该查询返回标量1，否则返回0。

select 
    case when exists 
        (select 1 from sqlite_master WHERE type='table' and name = 'your_table') 
        then 1 
        else 0 
    end as TableExists

#1

845