bigywater.tech
Java · 365

DatabaseMetaData:查询数据库与驱动能力

想写一份跨数据库的代码,先要问数据库自己「你支持什么」。这篇用 DatabaseMetaData 在 PostgreSQL 与 MySQL 上把同一批问题各问一遍,并检查几个声明是否属实、模式匹配有哪些陷阱。

0 个
MySQL 的 getSchemas() 返回的 schema 数(PostgreSQL 有 3 个);MySQL 用 catalog 表示数据库
407 vs 44
getTypeInfo 在 PostgreSQL 与 MySQL 上返回的类型条数
2 行
getTables("my_table") 返回的行数(_ 是通配符,同时匹配 myXtable);转义后只有 1 行

产品、驱动与术语

实测 · 本机执行输出 
  ── PostgreSQL
  产品 PostgreSQL 16.15 (Homebrew)(主 16 次 15);驱动 PostgreSQL JDBC Driver 42.7.4;JDBC 4.2;用户 lab;isReadOnly false
  getURL = jdbc:postgresql://127.0.0.1:9501/lab?user=lab
  标识符引号 “"”;搜索转义符 “\”;额外的名字字符 “”;“schema” 的术语 “schema”,“catalog” 的术语 “database”,“procedure” 的术语 “function”
  目录分隔符 “.”;catalog 出现在名字开头? true
  标识符大小写:storesLowerCase true,storesUpperCase false,storesMixedCase false,supportsMixedCaseIdentifiers false,supportsMixedCaseQuotedIdentifiers true,storesLowerCaseQuoted false
  getCatalogs:[lab, postgres, template1]
  getSchemas:3 个:[information_schema, pg_catalog, public]
  当前 connection.getCatalog() = lab;getSchema() = public
  getTableTypes:[FOREIGN TABLE, INDEX, MATERIALIZED VIEW, PARTITIONED INDEX, PARTITIONED TABLE, SEQUENCE, SYSTEM INDEX, SYSTEM TABLE, SYSTEM TOAST INDEX, SYSTEM TOAST TABLE, SYSTEM VIEW, TABLE, TEMPORARY INDEX, TEMPORARY SEQUENCE, TEMPORARY TABLE, TEMPORARY VIEW, TYPE, VIEW]
  getTypeInfo 条数:407
  getSQLKeywords 长度 1520,前 80 字符:abort,absent,access,aggregate,also,analyse,analyze,attach,backward,bit,cache,che
  getNumericFunctions 长度 122;getStringFunctions 长度 84;getTimeDateFunctions 长度 124
  ── MySQL
  产品 MySQL 26.7.0(主 26 次 7);驱动 MySQL Connector/J mysql-connector-j-9.1.0 (Revision: cf2917ea44ae2e43a4514a33771035aa99de73bf);JDBC 4.2;用户 root@localhost;isReadOnly false
  getURL = jdbc:mysql://127.0.0.1:9601/lab?user=root&allowPublicKeyRetrieval=true&useSSL=false
  标识符引号 “`”;搜索转义符 “\”;额外的名字字符 “$”;“schema” 的术语 “”,“catalog” 的术语 “database”,“procedure” 的术语 “PROCEDURE”
  目录分隔符 “.”;catalog 出现在名字开头? true
  标识符大小写:storesLowerCase false,storesUpperCase false,storesMixedCase true,supportsMixedCaseIdentifiers false,supportsMixedCaseQuotedIdentifiers false,storesLowerCaseQuoted false
  getCatalogs:[information_schema, lab, mysql, performance_schema, sys]
  getSchemas:0 个:[]
  当前 connection.getCatalog() = lab;getSchema() = null
  getTableTypes:[LOCAL TEMPORARY, SYSTEM TABLE, SYSTEM VIEW, TABLE, VIEW]
  getTypeInfo 条数:44
  getSQLKeywords 长度 1107,前 80 字符:ACCESSIBLE,ADD,ANALYZE,ASC,BEFORE,CASCADE,CHANGE,CONTINUE,DATABASE,DATABASES,DAY
  getNumericFunctions 长度 144;getStringFunctions 长度 374;getTimeDateFunctions 长度 289

能力查询与上限

实测 · 本机执行输出 
  ── PostgreSQL
  supportsTransactions=true  supportsBatchUpdates=true  supportsSavepoints=true  supportsGetGeneratedKeys=true  supportsStoredProcedures=true  supportsNamedParameters=false  supportsMultipleResultSets=true  supportsMultipleOpenResults=false  supportsMultipleTransactions=true  supportsNonNullableColumns=true  supportsOuterJoins=true  supportsFullOuterJoins=true  supportsSubqueriesInExists=true  supportsUnion=true  supportsUnionAll=true  supportsGroupBy=true  supportsOrderByUnrelated=true  supportsLikeEscapeClause=true  supportsSelectForUpdate=true  supportsPositionedUpdate=false  supportsStatementPooling=false  supportsCatalogsInTableDefinitions=false  supportsSchemasInTableDefinitions=true  supportsCatalogsInDataManipulation=false  supportsSchemasInDataManipulation=true  supportsResultSetHoldability_HOLD=true  supportsRefCursors=true  supportsSharding=false  supportsCoreSQLGrammar=false  supportsANSI92EntryLevelSQL=true  supportsDataDefinitionAndDataManipulationTransactions=true  supportsDataManipulationTransactionsOnly=false  dataDefinitionCausesTransactionCommit=false  dataDefinitionIgnoredInTransactions=false  autoCommitFailureClosesAllResultSets=false  locatorsUpdateCopy=true  nullsAreSortedHigh=true  nullsAreSortedLow=false  nullPlusNonNullIsNull=true  generatedKeyAlwaysReturned=true
  supportsTransactionIsolationLevel:NONE=false  READ_UNCOMMITTED=true  READ_COMMITTED=true  REPEATABLE_READ=true  SERIALIZABLE=true;getDefaultTransactionIsolation = 2(READ_COMMITTED)
  ResultSet 支持:FORWARD_ONLY:only=true/read_only=true/updatable=true  SCROLL_INSENSITIVE:only=true/read_only=true/updatable=true  SCROLL_SENSITIVE:only=false/read_only=false/updatable=false
  上限:getMaxConnections=8192,getMaxColumnNameLength=63,getMaxTableNameLength=63,getMaxColumnsInTable=1600,getMaxColumnsInSelect=0,getMaxStatementLength=0,getMaxRowSize=1073741824,getMaxIndexLength=0,getMaxStatements=0,getMaxBinaryLiteralLength=0(0 表示没有限制或未知)
  实测 supportsSavepoints 是否属实:setSavepoint/rollback(sp) 成功
  实测 supportsBatchUpdates:addBatch 成功;executeBatch → [0]
  实测 supportsStoredProcedures 与 supportsNamedParameters:true / false
  ── MySQL
  supportsTransactions=true  supportsBatchUpdates=true  supportsSavepoints=true  supportsGetGeneratedKeys=true  supportsStoredProcedures=true  supportsNamedParameters=false  supportsMultipleResultSets=true  supportsMultipleOpenResults=true  supportsMultipleTransactions=true  supportsNonNullableColumns=true  supportsOuterJoins=true  supportsFullOuterJoins=false  supportsSubqueriesInExists=true  supportsUnion=true  supportsUnionAll=true  supportsGroupBy=true  supportsOrderByUnrelated=false  supportsLikeEscapeClause=true  supportsSelectForUpdate=true  supportsPositionedUpdate=false  supportsStatementPooling=false  supportsCatalogsInTableDefinitions=true  supportsSchemasInTableDefinitions=false  supportsCatalogsInDataManipulation=true  supportsSchemasInDataManipulation=false  supportsResultSetHoldability_HOLD=true  supportsRefCursors=false  supportsSharding=false  supportsCoreSQLGrammar=true  supportsANSI92EntryLevelSQL=true  supportsDataDefinitionAndDataManipulationTransactions=false  supportsDataManipulationTransactionsOnly=false  dataDefinitionCausesTransactionCommit=true  dataDefinitionIgnoredInTransactions=false  autoCommitFailureClosesAllResultSets=false  locatorsUpdateCopy=true  nullsAreSortedHigh=false  nullsAreSortedLow=true  nullPlusNonNullIsNull=true  generatedKeyAlwaysReturned=true
  supportsTransactionIsolationLevel:NONE=false  READ_UNCOMMITTED=true  READ_COMMITTED=true  REPEATABLE_READ=true  SERIALIZABLE=true;getDefaultTransactionIsolation = 4(REPEATABLE_READ)
  ResultSet 支持:FORWARD_ONLY:only=true/read_only=true/updatable=true  SCROLL_INSENSITIVE:only=true/read_only=true/updatable=true  SCROLL_SENSITIVE:only=false/read_only=false/updatable=false
  上限:getMaxConnections=0,getMaxColumnNameLength=64,getMaxTableNameLength=64,getMaxColumnsInTable=512,getMaxColumnsInSelect=256,getMaxStatementLength=65531,getMaxRowSize=2147483639,getMaxIndexLength=256,getMaxStatements=0,getMaxBinaryLiteralLength=16777208(0 表示没有限制或未知)
  实测 supportsSavepoints 是否属实:setSavepoint/rollback(sp) 成功
  实测 supportsBatchUpdates:BatchUpdateException: Statement.executeUpdate() or Statement.executeLargeUpdate() cannot issue statements that produce result sets.
  实测 supportsStoredProcedures 与 supportsNamedParameters:true / false

目录对象与模式匹配

实测 · 本机执行输出 
  ── PostgreSQL
  getTables("parent"):1 行:[TABLE_CAT=null TABLE_SCHEM=public TABLE_NAME=parent TABLE_TYPE=TABLE REMARKS=null]
  getColumns(parent)(每列):
      id       TYPE_NAME=serial     DATA_TYPE=4    COLUMN_SIZE=10         DECIMAL_DIGITS=0    NULLABLE=0 IS_NULLABLE=NO  DEF=nextval('parent_id_seq'::regclass) AUTOINC=YES GENERATED=NO ORDINAL=1
      code     TYPE_NAME=varchar    DATA_TYPE=12   COLUMN_SIZE=20         DECIMAL_DIGITS=0    NULLABLE=0 IS_NULLABLE=NO  DEF=null                       AUTOINC=NO  GENERATED=NO ORDINAL=2
      price    TYPE_NAME=numeric    DATA_TYPE=2    COLUMN_SIZE=10         DECIMAL_DIGITS=2    NULLABLE=1 IS_NULLABLE=YES DEF=9.99                       AUTOINC=NO  GENERATED=NO ORDINAL=3
      note     TYPE_NAME=text       DATA_TYPE=12   COLUMN_SIZE=2147483647 DECIMAL_DIGITS=0    NULLABLE=1 IS_NULLABLE=YES DEF=null                       AUTOINC=NO  GENERATED=NO ORDINAL=4
      created  TYPE_NAME=timestamp  DATA_TYPE=93   COLUMN_SIZE=29         DECIMAL_DIGITS=6    NULLABLE=1 IS_NULLABLE=YES DEF=now()                      AUTOINC=NO  GENERATED=NO ORDINAL=5
  getPrimaryKeys(parent):1 行:[COLUMN_NAME=id KEY_SEQ=1 PK_NAME=parent_pkey]
  getIndexInfo(parent, unique=false):2 行:[INDEX_NAME=parent_code_key COLUMN_NAME=code NON_UNIQUE=f ORDINAL_POSITION=1 TYPE=3, INDEX_NAME=parent_pkey COLUMN_NAME=id NON_UNIQUE=f ORDINAL_POSITION=1 TYPE=3]
  getIndexInfo(child):3 行:[INDEX_NAME=child_pkey COLUMN_NAME=id NON_UNIQUE=f ORDINAL_POSITION=1, INDEX_NAME=idx_child_tag COLUMN_NAME=tag NON_UNIQUE=t ORDINAL_POSITION=1, INDEX_NAME=idx_child_tag COLUMN_NAME=parent_id NON_UNIQUE=t ORDINAL_POSITION=2]
  getImportedKeys(child):1 行:[PKTABLE_NAME=parent PKCOLUMN_NAME=id FKTABLE_NAME=child FKCOLUMN_NAME=parent_id FK_NAME=fk_parent UPDATE_RULE=3 DELETE_RULE=0 DEFERRABILITY=7]
  getExportedKeys(parent):1 行:[PKTABLE_NAME=parent FKTABLE_NAME=child FK_NAME=fk_parent DELETE_RULE=0]
  getTables("my_table")(下划线是通配符):2 行:[TABLE_NAME=my_table, TABLE_NAME=myxtable]
  getTables(转义后的 my\_table):1 行:[TABLE_NAME=my_table]
  getTables("MixedCase") / ("mixedcase"):1 行:[TABLE_NAME=MixedCase] / 0 行
  getTables("PARENT")(大写):0 行
  getTables(null, null, "%", null) 总数(含系统表):425 行,按类型 {INDEX=4, SEQUENCE=1, SYSTEM INDEX=124, SYSTEM TABLE=68, SYSTEM TOAST INDEX=41, SYSTEM VIEW=141, TABLE=5, null=41}
  getColumns 的列数(ResultSetMetaData):24
  ── MySQL
  getTables("parent"):1 行:[TABLE_CAT=lab TABLE_SCHEM=null TABLE_NAME=parent TABLE_TYPE=TABLE REMARKS=]
  getColumns(parent)(每列):
      id       TYPE_NAME=INT        DATA_TYPE=4    COLUMN_SIZE=10         DECIMAL_DIGITS=null NULLABLE=0 IS_NULLABLE=NO  DEF=null                       AUTOINC=YES GENERATED=NO ORDINAL=1
      code     TYPE_NAME=VARCHAR    DATA_TYPE=12   COLUMN_SIZE=20         DECIMAL_DIGITS=null NULLABLE=0 IS_NULLABLE=NO  DEF=null                       AUTOINC=NO  GENERATED=NO ORDINAL=2
      price    TYPE_NAME=DECIMAL    DATA_TYPE=3    COLUMN_SIZE=10         DECIMAL_DIGITS=2    NULLABLE=1 IS_NULLABLE=YES DEF=9.99                       AUTOINC=NO  GENERATED=NO ORDINAL=3
      note     TYPE_NAME=TEXT       DATA_TYPE=-1   COLUMN_SIZE=65535      DECIMAL_DIGITS=null NULLABLE=1 IS_NULLABLE=YES DEF=null                       AUTOINC=NO  GENERATED=NO ORDINAL=4
      created  TYPE_NAME=TIMESTAMP  DATA_TYPE=93   COLUMN_SIZE=19         DECIMAL_DIGITS=null NULLABLE=1 IS_NULLABLE=YES DEF=CURRENT_TIMESTAMP          AUTOINC=NO  GENERATED=YES ORDINAL=5
  getPrimaryKeys(parent):1 行:[COLUMN_NAME=id KEY_SEQ=1 PK_NAME=PRIMARY]
  getIndexInfo(parent, unique=false):2 行:[INDEX_NAME=code COLUMN_NAME=code NON_UNIQUE=0 ORDINAL_POSITION=1 TYPE=3, INDEX_NAME=PRIMARY COLUMN_NAME=id NON_UNIQUE=0 ORDINAL_POSITION=1 TYPE=3]
  getIndexInfo(child):4 行:[INDEX_NAME=PRIMARY COLUMN_NAME=id NON_UNIQUE=0 ORDINAL_POSITION=1, INDEX_NAME=fk_parent COLUMN_NAME=parent_id NON_UNIQUE=1 ORDINAL_POSITION=1, INDEX_NAME=idx_child_tag COLUMN_NAME=tag NON_UNIQUE=1 ORDINAL_POSITION=1, INDEX_NAME=idx_child_tag COLUMN_NAME=parent_id NON_UNIQUE=1 ORDINAL_POSITION=2]
  getImportedKeys(child):1 行:[PKTABLE_NAME=parent PKCOLUMN_NAME=id FKTABLE_NAME=child FKCOLUMN_NAME=parent_id FK_NAME=fk_parent UPDATE_RULE=1 DELETE_RULE=0 DEFERRABILITY=7]
  getExportedKeys(parent):2 行:[PKTABLE_NAME=parent FKTABLE_NAME=child FK_NAME=fk_parent DELETE_RULE=0, PKTABLE_NAME=parent FKTABLE_NAME=child FK_NAME=fk_parent DELETE_RULE=0]
  getTables("my_table")(下划线是通配符):2 行:[TABLE_NAME=my_table, TABLE_NAME=myXtable]
  getTables(转义后的 my\_table):1 行:[TABLE_NAME=my_table]
  getTables("MixedCase") / ("mixedcase"):1 行:[TABLE_NAME=MixedCase] / 1 行:[TABLE_NAME=MixedCase]
  getTables("PARENT")(大写):1 行:[TABLE_NAME=parent]
  getTables(null, null, "%", null) 总数(含系统表):345 行,按类型 {SYSTEM TABLE=155, SYSTEM VIEW=84, TABLE=6, VIEW=100}
  getColumns 的列数(ResultSetMetaData):24

元数据查询的成本

实测 · 本机执行输出 
  ── PostgreSQL
  2000 张表:getTables 2000 行 5 ms;getColumns(全部)4000 行 17 ms;逐表 getPrimaryKeys ×200 共 200 行 82 ms(平均 0.4 ms / 次)
  (对照)100 次 getDatabaseProductName 约 0 µs / 次(通常是缓存值)
  ── MySQL
  2000 张表:getTables 2000 行 6 ms;getColumns(全部)4000 行 40 ms;逐表 getPrimaryKeys ×200 共 200 行 51 ms(平均 0.3 ms / 次)
  (对照)100 次 getDatabaseProductName 约 0 µs / 次(通常是缓存值)

分步演示

DatabaseMetaData
本步的对象

    本文用到的工具与边界

    • 本机 macOS 27 arm64,JDK 21.0.12;PostgreSQL 16.15(Homebrew,自己初始化的隔离实例,端口 9501)、MySQL 26.7.0(自己初始化的隔离实例,端口 9601);驱动 PostgreSQL JDBC 42.7.4、MySQL Connector/J 9.1.0
    • 没有验证:Oracle / SQL Server / H2 / SQLite 的 DatabaseMetaData、getProcedures 与 getProcedureColumns、getFunctions、getUDTs、getBestRowIdentifier、getCrossReference、MySQL 的 getExportedKeys 重复行的原因(只观察到现象)、MySQL 上大小写行为受 lower_case_table_names 与平台影响(这里是 macOS 的默认设置)、不同驱动版本的差异
    京ICP备2026067502号-1京公网安备11010502063487号