高效的SQL测试查询或验证查询，可在所有（或大多数）数据库中使用 [英] Efficient SQL test query or validation query that will work across all (or most) databases
问题描述
许多数据库连接池库都提供了测试其SQL连接是否空闲的功能。例如，JDBC池库 c3p0 具有名为 preferredTestQuery ，它会在以配置的时间间隔进行连接。同样，Apache Commons DBCP具有 validationQuery 。
很多示例 查询是针对MySQL的，建议使用 SELECT 1; 作为测试查询的值。但是，此查询在某些数据库上不起作用（例如HSQLDB，对于该数据库， SELECT 1 期望 FROM 子句）
是否存在与数据库无关的查询，该查询是否同样有效，但适用于所有SQL数据库？
编辑：
如果没有（似乎是这种情况），有人可以建议一套适用于各种数据库提供程序的SQL查询吗？我的目的是根据数据库提供程序的配置以编程方式确定我可以使用的语句。
经过一番研究以及一些答案的帮助：
SELECT 1
- H2
- MySQL
- Microsoft SQL Server（根据 NimChimpsky ）
- PostgreSQL
- SQLite
从DUAL中选择1
- Oracle
从1 = 0的any_existing_table中选择1
或
从INFORMATION_SCHEMA.SYSTEM_USERS中选择1
或
立即致电（）
-
HSQLDB（已通过1.8.0.10版测试）
注意：尝试使用
WHERE 1 = 0子句在第二个查询中，但它不能用作Apache Commons DBCP的validationQuery的值，因为查询不会返回任何行
值1 或从SYSIBM.SYSDUMMY1
- Apache Derby（通过 daiscog ）
从SYSIBM.SYSDUMMY1
- DB2
从systables中选择计数（*）
- Informix
Many database connection pooling libraries provide the ability to test their SQL connections for idleness. For example, the JDBC pooling library c3p0 has a property called preferredTestQuery, which gets executed on the connection at configured intervals. Similarly, Apache Commons DBCP has validationQuery.
Many example queries I'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 1 expects a FROM clause).
Is there a database-agnostic query that's equivalently efficient but will work for all SQL databases?
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.
After a little bit of research along with help from some of the answers here:
SELECT 1
- H2
- MySQL
- Microsoft SQL Server (according to NimChimpsky)
- PostgreSQL
- SQLite
SELECT 1 FROM DUAL
- Oracle
SELECT 1 FROM any_existing_table WHERE 1=0
or
SELECT 1 FROM INFORMATION_SCHEMA.SYSTEM_USERS
or
CALL NOW()
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'svalidationQuery, since the query doesn't return any rows
VALUES 1 or SELECT 1 FROM SYSIBM.SYSDUMMY1
- Apache Derby (via daiscog)
SELECT 1 FROM SYSIBM.SYSDUMMY1
- DB2
select count(*) from systables
- Informix
这篇关于高效的SQL测试查询或验证查询，可在所有（或大多数）数据库中使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
