如何使用MySQL检查数据库中是否已存在表？

Now I am using something like:

现在我使用的是：

dbResult = dbStatement.executeQuery("SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = '[e2]' AND table_name = '[emp101_messages]'");
while(dbResult.next())
{
    int value = -1;
    value= dbResult.getInt(1);
    System.out.println(value + " table count");
}

Is this correct way to check if a table exists in a database?

这是检查数据库中是否存在表的正确方法吗？

5 个解决方案

#1

SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'dbname'
  AND table_name = 'my_tablename';

+----------------------+
| table_name           |
+----------------------+
| my_tablename |
+----------------------+

As most of the options are already provided. Please see if this can help.

由于大多数选项已经提供。请看看这是否有帮助。

#2

Use this:

用这个：

SHOW [FULL] TABLES [{FROM | IN} db_name]
    [LIKE 'pattern' | WHERE expr]

As sometimes you don't have access to metadata.

有时您无法访问元数据。

#3

If you want just check if 1, 2 or a few more tables exists why not use: mysql> show tables like "test1";?

如果你只想检查是否存在1,2或几个表，为什么不使用：mysql> show tables like“test1”;？

#4

It is a correct way to do so. (Just puzzled by the "[]" braces around your table names -- these are most probably not part of the actual names, so need to be removed)

这是一种正确的方法。（只是对你的表名周围的“[]”括号感到困惑 - 这些很可能不是实际名称的一部分，所以需要删除）

Moreover, it is an efficient way: since you are providing a constant for both table_schema as well as for table_name, you are therefore utilizing INFORMATION_SCHEMA optimizations, in that the table is not even opened:

此外，这是一种有效的方法：因为您为table_schema和table_name提供了一个常量，所以您正在使用INFORMATION_SCHEMA优化，因为该表甚至没有打开：

explain select count(*) from information_schema.tables where table_schema='world' and table_name='City';
+----+-------------+--------+------+---------------+-------------------------+---------+------+------+---------------------------------------------------+
| id | select_type | table  | type | possible_keys | key                     | key_len | ref  | rows | Extra                                             |
+----+-------------+--------+------+---------------+-------------------------+---------+------+------+---------------------------------------------------+
|  1 | SIMPLE      | tables | ALL  | NULL          | TABLE_SCHEMA,TABLE_NAME | NULL    | NULL | NULL | Using where; Skip_open_table; Scanned 0 databases |
+----+-------------+--------+------+---------------+-------------------------+---------+------+------+---------------------------------------------------+

The "SHOW TABLES" solution offered is good, too -- and practically the same as far as Java/Python/Whatever code goes. The result is a valid ResultSet:

提供的“SHOW TABLES”解决方案也很好 - 就Java / Python /无论代码而言几乎都是一样的。结果是一个有效的ResultSet：

SHOW TABLES FROM world LIKE 'City';
+------------------------+
| Tables_in_world (City) |
+------------------------+
| City                   |
+------------------------+

But to just complete the story: it is not a standard SQL syntax - so if you're using some framework like an ORM of some sorts, you may not always be able to get by with this type of query (as I recall EJB3 will not let you do so).

但是要完成这个故事：它不是一个标准的SQL语法 - 所以如果你使用像某些类型的ORM这样的框架，你可能无法总是使用这种类型的查询（因为我记得EJB3将不要让你这样做）。

Also, it is very difficult to parse on server side, see: Reading results of SHOW statements, on server side, though this may not be a concern to you.

此外，在服务器端解析非常困难，请参阅：在服务器端读取SHOW语句的结果，尽管这可能不是您关心的问题。

#5

You can also use:

您还可以使用：

SHOW TABLES LIKE "emp101_messages";

I think it's more efficient.

我认为它更有效率。

#1

SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'dbname'
  AND table_name = 'my_tablename';

+----------------------+
| table_name           |
+----------------------+
| my_tablename |
+----------------------+