“如果不存在则创建表” - 如何检查模式？

Is there a (more or less) standard way to check not only whether a table named mytable exists, but also whether its schema is similar to what it should be? I'm experimenting with H2 database, and

是否有（或多或少）标准方法不仅检查名为mytable的表是否存在，还检查其模式是否与它应该是什么类似？我正在试验H2数据库，并且

CREATE TABLE IF NOT EXISTS mytable (....)

statements apparently only check for the table´s name. I would expect to get an exception if there's a table with the given name, but different schema.

声明显然只检查表的名称。如果有一个具有给定名称但具有不同模式的表，我希望得到一个例外。

4 个解决方案

#1

SELECT  *
FROM    INFORMATION_SCHEMA.TABLES
WHERE   TABLE_NAME      = 'TableName'
    AND TABLE_SCHEMA    = 'public'

#2

CREATE TABLE IF NOT EXISTS ... is not a standard SQL code.

CREATE TABLE IF NOT EXISTS ...不是标准的SQL代码。

The thing to do is to check if the table is already in the catalogue. For instance, in Java you may do something like: connection.getMetaData().getTables(connection.getCatalog(), null, null, null)

要做的是检查表是否已经在目录中。例如，在Java中，您可以执行以下操作：connection.getMetaData（）。getTables（connection.getCatalog（），null，null，null）

For more info see javadoc java.sql.Connection.

有关更多信息，请参阅javadoc java.sql.Connection。

#3

I am not aware of any database that has this feature natively.

我不知道任何具有此功能的数据库本地。

Have not used it (rolled my own code to do this) but maybe Apache DdlUtils can help.

没有使用它（滚动我自己的代码来做到这一点），但也许Apache DdlUtils可以提供帮助。

It is a tricky thing to do, especially if you want it to work with different database vendors. Also, there are probably different opinions about how similar the schema needs to be in order to pass. Column names, column order, column types, primary key definition: certainly. But how about constraints, the names of the constraints, table space definitions, and so on?

这是一件棘手的事情，特别是如果你想让它与不同的数据库供应商合作。此外，对于传递模式需要多么相似，可能存在不同的意见。列名，列顺序，列类型，主键定义：当然。但是约束，约束的名称，表空间定义等等呢？

#4

Twofold answer :

双重回答：

(a) The existence of a table is something that should be ensured by the installation procedure of an application, not by the application itself at run-time.

（a）应该通过应用程序的安装过程来确保表的存在，而不是应用程序本身在运行时。

(b) If you really think you have a valid reason for deviating from (a), you could try and query the catalog, which is a database consisting of tables whose structure is, more or less, prescribed by the INFORMATION_SCHEMA of the SQL standard. Which tables exist, which columns they have, which data types those columns are, which keys are declared, etc. etc., it's all in there.

（b）如果你真的认为你有正当理由偏离（a），你可以尝试查询目录，这是一个由表格组成的数据库，其结构或多或少是由SQL标准的INFORMATION_SCHEMA规定的。。存在哪些表，它们具有哪些列，这些列是哪些数据类型，声明了哪些键等等，它们都在那里。

#1

SELECT  *
FROM    INFORMATION_SCHEMA.TABLES
WHERE   TABLE_NAME      = 'TableName'
    AND TABLE_SCHEMA    = 'public'

#2