跨所有(或大多数)数据库工作的高效 SQL 测试查询或验证查询

许多数据库连接池库提供了测试其 SQL 连接是否空闲的能力。例如，JDBC 池库 C3p0有一个名为 preferredTestQuery的属性，该属性按配置的间隔在连接上执行。类似地，ApacheCommons DBCP 具有 validationQuery。

我看到的许多 例子查询都是用于 MySQL 的，建议使用 SELECT 1;作为测试查询的值。但是，这个查询不能在某些数据库上工作(例如，HSQLDB，对于它，SELECT 1需要一个 FROM子句)。

是否存在一个与数据库无关的查询，其效率相当，但适用于所有 SQL 数据库？

编辑:

如果没有(似乎就是这种情况) ，是否有人建议使用一组 SQL 查询来适用于各种数据库提供者？我的目的是通过编程方式确定可以基于数据库提供程序配置使用的语句。

158022 次浏览
小开

不幸的是，没有 SELECT 语句不管数据库如何都能正常工作。

大多数 数据库支持:

SELECT 1

有些数据库不支持这一点，但是有一个名为 DUAL 的表，当您不需要表时可以使用它:

SELECT 1 FROM DUAL

出于兼容性考虑，MySQL 也支持这一点，但并非所有数据库都支持。对于不支持上述任何一种方法的数据库，一种解决方案是创建一个包含单行的名为 DUAL 的表，然后使用上述方法。

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

SELECT 1 FROM any_table_that_you_know_exists_in_your_database
小开

select 1可以在 sql 服务器中工作，其他的不确定。

使用标准的 ansi sql 创建一个表，然后从该表进行查询。

小开

经过一些研究和一些答案的帮助:

SELECT 1

SELECT 1 FROM DUAL

  • 神使

SELECT 1 FROM any_existing_table WHERE 1=0

或者

SELECT 1 FROM INFORMATION_SCHEMA.SYSTEM_USERS

或者

CALL NOW()

  • HSQLDB (使用版本1.8.0.10测试)

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

VALUES 1或 SELECT 1 FROM SYSIBM.SYSDUMMY1

SELECT 1 FROM SYSIBM.SYSDUMMY1

  • DB2

select count(*) from systables

  • Informix
小开

我用这个:

select max(table_catalog) as x from information_schema.tables

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

小开

我吸毒

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

对于 hsqldb 1.8.0

小开

假设 OP 想要一个 Java 的答案:

在 JDBC3/Java6中，应该使用 IsValid ()方法，而不是发明自己的方法。

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

小开

怎么样

SELECT user()

我以前用过这个。 MySQL，H2是可以的，我不认识其他人。

小开

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

小开

如果您的驱动程序与 JDBC 4兼容，就不需要专门的查询来测试连接。而是使用 连接有效来测试连接。

JDBC 4是从2006年开始的 Java6的一部分，您的驱动程序现在应该支持它了！

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

ConnectionTestQuery

如果您的驱动程序强烈支持 JDBC4，我们 建议不要设置此属性。这是针对“遗留”数据库的 不支持 JDBC4 Connection.isValid () API 的 查询将在连接提供给您之前执行 来验证到数据库的连接是否仍然存在 再次尝试在没有这个属性 HikariCP 的情况下运行池 如果您的驱动程序不符合 JDBC4的要求，那么将记录一个错误 默认值: 没有

小开

刚刚才发现，这是一个艰难的过程

SELECT 1 FROM DUAL

还有 MaxDB。

小开

我用这个做 火鸟

select 1 from RDB$RELATION_FIELDS rows 1
小开

对于 MSSQL。

这有助于我确定链接的服务器是否存在。使用 Open Query 连接和 TRYCATCH 将错误的结果放到有用的地方。

IF OBJECT_ID('TEMPDB..#TEST_CONNECTION') IS NOT NULL DROP TABLE #TEST_CONNECTION
IF OBJECT_ID('TEMPDB..#RESULTSERROR') IS NOT NULL DROP TABLE #RESULTSERROR
IF OBJECT_ID('TEMPDB..#RESULTSGOOD') IS NOT NULL DROP TABLE #RESULTSGOOD


DECLARE @LINKEDSERVER AS VARCHAR(25)    SET @LINKEDSERVER = 'SERVER NAME GOES HERE'
DECLARE @SQL AS VARCHAR(MAX)
DECLARE @OPENQUERY AS VARCHAR(MAX)


--IF OBJECT_ID ('dbo.usp_GetErrorInfo', 'P' ) IS NOT NULL DROP PROCEDURE usp_GetErrorInfo;
--GO


---- Create procedure to retrieve error information.
--CREATE PROCEDURE dbo.usp_GetErrorInfo
--AS
--SELECT
--    ERROR_NUMBER() AS ErrorNumber
--    ,ERROR_SEVERITY() AS ErrorSeverity
--    ,ERROR_STATE() AS ErrorState
--    ,ERROR_PROCEDURE() AS ErrorProcedure
--    ,ERROR_LINE() AS ErrorLine
--    ,ERROR_MESSAGE() AS Message;
--GO




BEGIN TRY
SET @SQL='
SELECT 1
'''
--SELECT @SQL
SET @OPENQUERY = 'SELECT * INTO ##TEST_CONNECTION FROM OPENQUERY(['+ @LINKEDSERVER +'],''' + @SQL + ')'
--SELECT @OPENQUERY
EXEC(@OPENQUERY)
SELECT * INTO #TEST_CONNECTION FROM ##TEST_CONNECTION
DROP TABLE ##TEST_CONNECTION
--SELECT * FROM #TEST_CONNECTION
END TRY


BEGIN CATCH
-- Execute error retrieval routine.
IF OBJECT_ID('dbo.usp_GetErrorInfo') IS NOT NULL -- IT WILL ALWAYS HAVE SOMTHING...
BEGIN
CREATE TABLE #RESULTSERROR (
[ErrorNumber]       INT
,[ErrorSeverity]    INT
,[ErrorState]       INT
,[ErrorProcedure]   INT
,[ErrorLine]        INT
,[Message]          NVARCHAR(MAX)
)
INSERT INTO #RESULTSERROR
EXECUTE dbo.usp_GetErrorInfo
END
END CATCH


BEGIN
IF (Select ERRORNUMBER FROM #RESULTSERROR WHERE ERRORNUMBER = '1038') IS NOT NULL --'1038' FOR ME SHOWED A CONNECTION ATLEAST.
SELECT
'0' AS [ErrorNumber]
,'0'AS [ErrorSeverity]
,'0'AS [ErrorState]
,'0'AS [ErrorProcedure]
,'0'AS [ErrorLine]
, CONCAT('CONNECTION IS UP ON ', @LINKEDSERVER) AS [Message]
ELSE
SELECT * FROM #RESULTSERROR
END

Learn.microsoft.com

小开

对于 Oracle，高性能查询将是

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

这是从性能角度考虑的。

小开
最佳答案

对于 jOOQ 的 select(inline(1))查询，JOOQ 手册中关于 DUAL表的部分列出了以下内容:

-- Access
SELECT 1 FROM (SELECT count(*) dual FROM MSysResources) AS dual


-- BigQuery, CockroachDB, Exasol, H2, Ignite, MariaDB, MySQL, PostgreSQL,
-- Redshift, Snowflake, SQLite, SQL Server, Sybase ASE, Vertica
SELECT 1


-- MemSQL, Oracle
SELECT 1 FROM DUAL


-- CUBRID
SELECT 1 FROM db_root


-- Db2
SELECT 1 FROM SYSIBM.DUAL


-- Derby
SELECT 1 FROM SYSIBM.SYSDUMMY1


-- Firebird
SELECT 1 FROM RDB$DATABASE


-- HANA, Sybase SQL Anywhere
SELECT 1 FROM SYS.DUMMY


-- HSQLDB
SELECT 1 FROM (VALUES(1)) AS dual(dual)


-- Informix
SELECT 1 FROM (SELECT 1 AS dual FROM systables WHERE (tabid = 1)) AS dual


-- Ingres, Teradata
SELECT 1 FROM (SELECT 1 AS "dual") AS "dual"

提交答案