高效的 SQL 测试查询或验证查询，适用于所有（或大多数）数据库

Efficient SQL test query or validation query that will work across all (or most) databases

提问by Rob Hruska

Many database connection pooling libraries provide the ability to test their SQL connections for idleness. For example, the JDBC pooling library c3p0has a property called preferredTestQuery, which gets executed on the connection at configured intervals. Similarly, Apache Commons DBCP has validationQuery.

许多数据库连接池库提供了测试其 SQL 连接是否空闲的能力。例如，JDBC 池库c3p0有一个名为 的属性preferredTestQuery，它以配置的时间间隔在连接上执行。同样，Apache Commons DBCP 具有validationQuery.

Many examplequeriesI've seen are for MySQL and recommend using SELECT 1;as the value for the test query. However, this query doesn't work on some databases (e.g. HSQLDB, for which SELECT 1expects a FROMclause).

我见过的许多示例查询都是针对 MySQL 的，建议将其SELECT 1;用作测试查询的值。但是，此查询不适用于某些数据库（例如 HSQLDB，SELECT 1需要一个FROM子句）。

Is there a database-agnostic query that's equivalently efficient but will work for all SQL databases?

是否有一个与数据库无关的查询，它同样有效但适用于所有 SQL 数据库？

Edit:

编辑：

If there's not (which seems to be the case), can somebody suggest a set of SQL queries that will work for various database providers? My intention would be to programmatically determine a statement I can use based on my database provider configuration.

如果没有（似乎是这种情况），有人可以建议一组适用于各种数据库提供程序的 SQL 查询吗？我的目的是根据我的数据库提供程序配置以编程方式确定我可以使用的语句。

回答by Rob Hruska

After a little bit of research along with help from some of the answers here:

经过一些研究以及这里的一些答案的帮助：

SELECT 1

SELECT 1

• H2
• MySQL
• Microsoft SQL Server (according to NimChimpsky)
• PostgreSQL
• SQLite

• H2
• MySQL
• Microsoft SQL Server（根据NimChimpsky）
• PostgreSQL
• SQLite

---

---

SELECT 1 FROM DUAL

SELECT 1 FROM DUAL

• Oracle

• 甲骨文

---

---

SELECT 1 FROM any_existing_table WHERE 1=0

SELECT 1 FROM any_existing_table WHERE 1=0

or

或者

SELECT 1 FROM INFORMATION_SCHEMA.SYSTEM_USERS

SELECT 1 FROM INFORMATION_SCHEMA.SYSTEM_USERS

• HSQLDB (tested with version 1.8.0.10)

  Note: I tried using a WHERE 1=0clause on the second query, but it didn't work as a value for Apache Commons DBCP's validationQuery, since the query doesn't return any rows

• HSQLDB（使用版本 1.8.0.10 测试）

  注意：我尝试WHERE 1=0在第二个查询中使用子句，但它不能用作 Apache Commons DBCP's 的值validationQuery，因为该查询不返回任何行

---

---

VALUES 1or SELECT 1 FROM SYSIBM.SYSDUMMY1

VALUES 1或者 SELECT 1 FROM SYSIBM.SYSDUMMY1

• Apache Derby (via daiscog)

• Apache Derby（通过daiscog）

SELECT 1 FROM SYSIBM.SYSDUMMY1

SELECT 1 FROM SYSIBM.SYSDUMMY1

• DB2

• 数据库2

select count(*) from systables

select count(*) from systables

• Informix

• Informix

回答by Tim Büthe

If your driver is JDBC 4 compliant, there is no need for a dedicated query to test connections. Instead, there is Connection.isValidto test the connection.

如果您的驱动程序符合 JDBC 4，则不需要专门的查询来测试连接。相反，有Connection.isValid来测试连接。

JDBC 4 is part of Java 6 from 2006 and you driver should support this by now!

JDBC 4 是 2006 年 Java 6 的一部分，您的驱动程序现在应该支持它！

Famous connection pools, like HikariCP, still have a config parameter for specifying a test query but strongly discourage to use it:

著名的连接池，如 HikariCP，仍然有一个用于指定测试查询的配置参数，但强烈建议不要使用它：

connectionTestQuery

If your driver supports JDBC4 we strongly recommend not setting this property. This is for "legacy" databases that do not support the JDBC4 Connection.isValid() API. This is the query that will be executed just before a connection is given to you from the pool to validate that the connection to the database is still alive. Again, try running the pool without this property, HikariCP will log an error if your driver is not JDBC4 compliant to let you know. Default: none

连接测试查询

如果您的驱动程序支持 JDBC4，我们强烈建议不要设置此属性。这适用于不支持 JDBC4 Connection.isValid() API 的“传统”数据库。这是将在从池中向您提供连接之前执行的查询，以验证与数据库的连接是否仍然有效。同样，尝试在没有此属性的情况下运行池，如果您的驱动程序不符合 JDBC4，HikariCP 将记录错误以通知您。默认值：无

回答by Mark Byers

Unfortunately there is no SELECT statement that will always work regardless of database.

不幸的是，无论数据库如何，都没有始终有效的 SELECT 语句。

Mostdatabases support:

大多数数据库支持：

SELECT 1

Some databases don't support this but have a table called DUAL that you can use when you don't need a table:

某些数据库不支持此功能，但有一个名为 DUAL 的表，您可以在不需要表时使用它：

SELECT 1 FROM DUAL

MySQL also supports this for compatibility reasons, but not all databases do. A workaround for databases that don't support either of the above is to create a table called DUAL that contains a single row, then the above will work.

出于兼容性原因，MySQL 也支持此功能，但并非所有数据库都支持。不支持上述任何一项的数据库的解决方法是创建一个名为 DUAL 的表，其中包含单行，然后上述方法将起作用。

HSQLDB supports neither of the above, so you can either create the DUAL table or else use:

HSQLDB 不支持上述任何一种，因此您可以创建 DUAL 表或使用：

SELECT 1 FROM any_table_that_you_know_exists_in_your_database

回答by Nathan Niesen

For tests using select count(*), it should be more efficient to use select count(1)because *can cause it to read all the column data.

对于使用 的测试，使用select count(*)它应该更有效，select count(1)因为*它会导致它读取所有列数据。

回答by Wojciechk

I use this one:

我用这个：

select max(table_catalog) as x from information_schema.tables

to check connection and ability to run queries (with 1 row as result) for postgreSQL, MySQL and MSSQL.

检查连接和为 postgreSQL、MySQL 和 MSSQL 运行查询（结果为 1 行）的能力。

回答by thinkbase

I use

我用

Select COUNT(*) As X From INFORMATION_SCHEMA.SYSTEM_USERS Where 1=0

for hsqldb 1.8.0

对于 hsqldb 1.8.0

回答by peterh

Assuming the OP wants a Java answer:

假设 OP 需要 Java 答案：

As of JDBC3 / Java 6 there's the isValid()method which should be used rather than inventing one's own method.

从 JDBC3/Java 6 开始，应该使用isValid()方法而不是发明自己的方法。

The implementer of the driver is required to execute some sort of query against the database when this method id called. You - as a mere JDBC user - do not have to know or understand what this query is. All you have to do is to trust that the creator of the JDBC driver has done his/her work properly.

调用此方法 id 时，驱动程序的实现者需要对数据库执行某种查询。您——仅仅作为一个 JDBC 用户——不必知道或理解这个查询是什么。您所要做的就是相信 JDBC 驱动程序的创建者已经正确地完成了他/她的工作。

回答by wener

How about

怎么样

SELECT user()

I use this before.MySQL, H2 is OK, I don't know others.

之前用的这个mysql，H2还可以，其他的不知道。

回答by Lars Decker

Just found out the hard way that it is

刚刚发现它是艰难的

SELECT 1 FROM DUAL

for MaxDB as well.

也适用于 MaxDB。

回答by Joby Kurian

For Oracle the high performing query will be

对于 Oracle，高性能查询将是

select 'X' from <your_small_table> where <primay_key_coulmn> = <some_value>

This is from a performance perspective.

这是从性能的角度来看的。