"如果不存在则创建表" - 如何检查模式(schema)？

Question

是否有(相对而言)标准的方式来检查不仅mytable表是否存在，而且它的模式是否与应该是类似的？ 我正在尝试使用H2数据库，并

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

该语句显然只检查表的名称。如果存在具有给定名称但模式不同的表，我期望会抛出异常。

- Joonas Pulakka

---

“一个类似名称的表格”？“一个类似于应该是什么的模式”？这对于一个通用查询工具来说太模糊了。 - Thilo

+1 是因为我也想要这样的功能，不一定是通过数据库实现，而是在工具/库中。 - Thilo

@Thilo：原本应该是“给定名称的表格”，现在已经修复好了。当我说“类似于应该是什么”的模式时，我是指如果存在名为mytable的表，则它的模式必须等于我在查询中提供的内容；否则，我希望得到某种错误提示。这是一个非常明确的查询，所以我想知道是否有一种方法可以表示它（如果没有，为什么没有）。事实上，我很惊讶“IF NOT EXISTS”似乎只检查名称。 - Joonas Pulakka

不错的想法，我也想有这样的东西！虽然我从未听说过能够做到这一点的 SQL 结构。 - Pekka

Answer 1

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

- isapir

---

请您详细说明一下，这个是做什么用的？ - Joonas Pulakka

我所做的就是它所说的... 它检查数据库中是否存在这样的表。 INFORMATION_SCHEMA 是（有点）标准化的，因此该语句在大多数数据库中都可以使用。 - Thomas Mueller

它在MSSQL中也可以工作，但是“默认”模式是“dbo”。 - isapir

这个答案没有演示如何比较模式。但它更接近了。可以使用针对INFORMATION_SCHEMA.COLUMNS的查询来检查现有列是否与预期的匹配，并对此做出反应。然而，我认为使用类似于命名/编号迁移和迁移表的东西，就像EntityFramework支持的那样，会更容易且更少出错 - 特别是当您需要保留现有数据但由于模式更改而更改其存储方式时。 - binki

Answer 2

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

需要做的事情是检查目录中是否已经存在该表。例如，在 Java 中，您可以执行以下操作： connection.getMetaData().getTables(connection.getCatalog(), null, null, null)

更多信息请参见javadoc java.sql.Connection。

- Kru

---

不过，它也不检查模式（当然，使用 MetaData，您可以自己检查，但这很繁琐。JDBC MetaData 有一个可怕的 API）。 - Thilo

Answer 3

双重答案：

(a) 数据表的存在应该由应用程序的安装过程来确保，而不是在运行时由应用程序本身来确保。

(b) 如果您真的认为有一个合理的理由偏离(a)，您可以尝试查询目录。目录是一个数据库，由SQL标准的INFORMATION_SCHEMA规定表结构。其中包含哪些表存在、它们具有哪些列、这些列的数据类型、声明了哪些键等等，所有这些信息都在目录中。

- Erwin Smout

---

许多应用程序不希望给用户增加一个独立的安装程序/更新脚本，而是在内部管理数据库架构。即使它们不创建表，启动时检查表结构以确认应用程序的完整性（而不是在以后随机出现数据库错误）也是有意义的。我并不是说这种方法总是适合的，但在某些情况下，这是一个有效的需求。 - Thilo

是的，我也认为虽然a）可能是通常的方法，但它类似于“应该由应用程序的安装过程来确保C：驱动器的存在，而不是在运行时由应用程序本身来确保。” 但是一个严肃的应用程序不应该对环境做任何假设。相反，它应该进行检查。环境可能会发生变化 - 迟早会发生变化。 - Joonas Pulakka

Answer 4

我不知道有哪个数据库本身具备这个功能。

虽然我没有使用过它（我自己编写了代码来实现此功能），但也许Apache DdlUtils可以帮助你。

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

- Thilo

---

我明白了，这可能不像我想的那么简单。尽管如此，考虑到几乎任何使用任何数据库的软件都应该进行此检查，仍然很惊奇为什么没有标准解决方案。 - Joonas Pulakka

Answer 5

简单来说：

IF NOT EXISTS (SELECT 0 
           FROM INFORMATION_SCHEMA.TABLES 
           WHERE TABLE_SCHEMA = 'name_of_schema' 
           AND TABLE_NAME = 'name_of_table')
 BEGIN
     CREATE TABLE [name_of_schema].[name_of_table]
     (
       ...
     )
 END

---

更多示例，请查看这里。

- Arsen Khachaturyan