如何检查数据库中是否存在表或列？

I am trying to make simple java code that will check if a table and/or a column exists in a MySQL DB. Should I use Java code to do the checking or make a SQL query string and execute that to do the checking ?

我正在尝试制作简单的java代码，以检查MySQL DB中是否存在表和/或列。我应该使用Java代码进行检查还是创建一个SQL查询字符串并执行它来进行检查？

EDIT-

编辑-

@ aleroot -

I tried using your code as shown below. I don't see any tables or columns when I run the code below. I only see this-

我尝试使用您的代码，如下所示。当我运行下面的代码时，我看不到任何表或列。我只看到这个 -

Driver Loaded.
Got Connection.

My DB has got a lot of DB's, tables and columns. I dont know why this program works properly.

我的DB有很多DB，表和列。我不知道为什么这个程序正常工作。

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

    public class Tester {
  static Connection conn;
  static Statement st;
  public static void main(String[] args) throws Exception {
    try {
  // Step 1: Load the JDBC driver.
  System.out.println("Driver Loaded.");
  // Step 2: Establish the connection to the database.
  String url = "jdbc:mysql://localhost:3306/";

  conn = DriverManager.getConnection(url, "cowboy", "123456");
  System.out.println("Got Connection.");

  st = conn.createStatement();
} catch (Exception e) {
  System.err.println("Got an exception! ");
  e.printStackTrace();
  System.exit(0);
}

DatabaseMetaData md2 = conn.getMetaData();
ResultSet rsTables = md2.getColumns(null, null, "customers", "name");
 if (rsTables.next()) {
      System.out.println("Exists !");
    }
  }

}

4 个解决方案

#1

To check if a table exist you can use DatabaseMetaData in this way :

要检查表是否存在，可以使用以下方式使用DatabaseMetaData：

DatabaseMetaData md = connection.getMetaData();
ResultSet rs = md.getTables(null, null, "table_name", null);
if (rs.next()) {
  //Table Exist
}

And to check if a column exist you can use it in a similar way :

要检查列是否存在，您可以以类似的方式使用它：

DatabaseMetaData md = connection.getMetaData();
ResultSet rs = md.getColumns(null, null, "table_name", "column_name");
 if (rs.next()) {
      //Column in table exist
    }

#2

SELECT * FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA='table_schema'
  AND TABLE_NAME='table_name' 
  AND COLUMN_NAME='column_name'

If this sql returns nothing - column not exists. You can execute this query on java side, but this depends what you use (JPA, JDBC, JDBCTemplate).

如果这个sql什么都不返回 - 列不存在。您可以在java端执行此查询，但这取决于您使用的内容（JPA，JDBC，JDBCTemplate）。

#3

Knowing that you can practically select a column from a table using the following SQL statement (in MySQL):

知道您几乎可以使用以下SQL语句从表中选择一列（在MySQL中）：

show columns from TABLE_NAME where field = 'FIELD_NAME';

You could do something like:

你可以这样做：

...
PreparedStatement preparedStatement =
  connection.prepareStatement(
    "show columns from [TABLE_NAME] where field = ?");
preparedStatement.setString(1, columName);

ResultSet resultSet = preparedStatement.executeQuery();

if (resultSet.next()) {
    System.out.println("column exists!");
} else {
    System.out.println("column doesn't exists!");
}
...

#4

In Apache Derby (SQL)

在Apache Derby（SQL）中

E.g. check if column exists:

例如。检查列是否存在：

SELECT ss.SCHEMANAME, st.TABLENAME, sc.COLUMNNAME FROM SYS.SYSSCHEMAS ss
INNER JOIN SYS.SYSTABLES st ON st.SCHEMAID = ss.SCHEMAID AND st.TABLENAME = <YOUR TABLE NAME>
INNER JOIN SYS.SYSCOLUMNS sc ON sc.REFERENCEID = st.TABLEID AND sc.COLUMNNAME = <YOUR COLUMN NAME>
WHERE ss.SCHEMANAME = <YOUR SCHEMA>

#1