本文探讨了如何使用DatabaseMetaData分析数据库元数据，如表类型、数据表、主键等，并对比了它与数据库系统表（如MySQL的information_schema）在获取数据库信息方面的优缺点。
使用DatabaseMetaData分析数据库元数据
在某些时候程序需要动态地获取数据的相关信息，例如数据库里的数据表信息、列信息，此外如果希望在程序中动态地利用底层数据库所提供的特殊功能，则都需要动态分析数据库相关信息
- JDBC提供了DatabaseMetaData来封装数据库连接对应数据库的信息，从过connection提供的getMetaData()方法就可以获取数据库对应的DatabaseMetaData对象
- DatabaseMetaData接口通常有驱动程序提供实现，该接口的目的时发现如何处理底层数据库，尤其是对于需要处理多个数据库的应用程序，在多个数据库之间切换则必须利用该接口来找出底层数据库的功能，例如调用supportsCorrelatedSubqueries()方法查看是否可以使用关联子查询，调用supportsBatchUpdates()方法查看是否可以使用批量更新
- 许多DatabaseMetaData方法以ResultSet对象的形式返回查询信息，然后使用ResultSet的常规方法例如getString()和getInt()即可从这些ResultSet对象中获取数据；如果查询信息不可用，则将返回一个空ResultSet对象
- DatabaseMetaData的很多方法都需要传入一个xxxPattern模式字符串，也就是百分号代表任意多个字符，下划线代表一个字符，如果把该模式字符串的参数设置为null，则表明该参数不作为过滤条件
import java.sql.*;
import java.util.*;
import java.io.*;
public class DatabaseMetaDataTest
{
private String driver;
private String url;
private String user;
private String pass;
public void initParam(String paramFile) throws Exception
{
// 使用Properties类来加载属性文件
var props = new Properties();
props.load(new FileInputStream(paramFile));
driver = props.getProperty("driver");
url = props.getProperty("url");
user = props.getProperty("user");
pass = props.getProperty("pass");
}
public void info() throws Exception
{
// 加载驱动
Class.forName(driver);
try (
// 获取数据库连接
Connection conn = DriverManager.getConnection(url, user, pass))
{
// 获取的DatabaseMetaData对象
DatabaseMetaData dbmd = conn.getMetaData();
// 获取MySQL支持的所有表类型
ResultSet rs = dbmd.getTableTypes();
System.out.println("--MySQL支持的表类型信息--");
printResultSet(rs);
// 获取当前数据库的全部数据表
rs = dbmd.getTables("select_test", null, "%", new String[]{"TABLE"});
System.out.println("--当前数据库里的数据表信息--");
printResultSet(rs);
// 获取student_table表的主键
rs = dbmd.getPrimaryKeys("select_test", null, "student_table");
System.out.println("--student_table表的主键信息--");
printResultSet(rs);
// 获取当前数据库的全部存储过程
rs = dbmd.getProcedures("select_test", null, "%");
System.out.println("--当前数据库里的存储过程信息--");
printResultSet(rs);
// 获取teacher_table表和student_table之间的外键约束
rs = dbmd.getCrossReference("select_test", null, "teacher_table",
null, null, "student_table");
System.out.println("--teacher_table表和student_table之间"
+ "的外键约束--");
printResultSet(rs);
// 获取student_table表的全部数据列
rs = dbmd.getColumns("select_test", null, "student_table", "%");
System.out.println("--student_table表的全部数据列--");
printResultSet(rs);
}
}
public void printResultSet(ResultSet rs) throws SQLException
{
ResultSetMetaData rsmd = rs.getMetaData();
// 打印ResultSet的所有列标题
for (var i = 0; i < rsmd.getColumnCount(); i++)
{
System.out.print(rsmd.getColumnName(i + 1) + "\t");
}
System.out.print("\n");
// 打印ResultSet里的全部数据
while (rs.next())
{
for (var i = 0; i < rsmd.getColumnCount(); i++)
{
System.out.print(rs.getString(i + 1) + "\t");
}
System.out.print("\n");
}
rs.close();
}
public static void main(String[] args) throws Exception
{
var dt = new DatabaseMetaDataTest();
dt.initParam("mysql.ini");
dt.info();
}
}
这段代码通过DatabaseMetaData分析了当前Connection链接对应数据库的一些基本信息，包括当前数据库包含多少数据表，存储过程，student_table表的数据列、主键、外键等信息
set CLASSPATH=%CLASSPATH%;../mysql-connector-java-8.0.13.jar
java DatabaseMetaDataTest
cmd
使用系统表分析数据库信息
除了使用DatabaseMetaData来分析底层数据库信息之外，如果已经确定应用程序所使用的数据库系统，则可以通过数据库的系统表来分析数据库信息
系统表又称为数据字典，数据字典的数据通常由数据库系统负责维护，用户通常只能查询数据字典，而不能修改数据字典内容
几乎所有的数据库都会提供系统表供用户查询，用户可以通过查询系统表来获得数据库的相关信息，对于MySQL和SQL Server这样的数据库，他们还提供了一个系统数据库来存储这些系统表
MySQL数据库使用information_schema数据库来保存系统表，常用系统表：
- tables：存放数据库里所有数据表的信息
- schemata：存放数据库里所有数据库的实例信息
- views：存放数据库里所有视图的信息
- columns：存放数据库里所有列的信息
- triggers：存放数据库里所有触发器的信息
- routines：存放数据库里所有存储过程和函数的信息
- key_column_usage：存放数据库里所有具有约束的键信息
- table_constraints：存放数据库里全部约束的表信息
- statistics：存放数据库里全部索引的信息
mysql> use information_schema
Database changed
mysql> select * from schemata;
+--------------+--------------------+----------------------------+------------------------+----------+
| CATALOG_NAME | SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH |
+--------------+--------------------+----------------------------+------------------------+----------+
| def | information_schema | utf8 | utf8_general_ci | NULL |
| def | davieyang | latin1 | latin1_swedish_ci | NULL |
| def | mysql | latin1 | latin1_swedish_ci | NULL |
| def | performance_schema | utf8 | utf8_general_ci | NULL |
| def | sakila | latin1 | latin1_swedish_ci | NULL |
| def | sys | utf8 | utf8_general_ci | NULL |
| def | world | latin1 | latin1_swedish_ci | NULL |
+--------------+--------------------+----------------------------+------------------------+----------+
7 rows in set (0.00 sec)
mysql> select * from tables where table_schema = 'mysql';
+---------------+--------------+---------------------------+------------+--------+---------+------------+------------+----------------+-------------+--------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+--------------------+-----------------------------------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | ENGINE | VERSION | ROW_FORMAT | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | AUTO_INCREMENT | CREATE_TIME | UPDATE_TIME | CHECK_TIME | TABLE_COLLATION | CHECKSUM | CREATE_OPTIONS | TABLE_COMMENT |
+---------------+--------------+---------------------------+------------+--------+---------+------------+------------+----------------+-------------+--------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+--------------------+-----------------------------------------+
| def | mysql | columns_priv | BASE TABLE | MyISAM | 10 | Fixed | 0 | 0 | 0 | 241505530017742847 | 4096 | 0 | NULL | 2020-09-22 23:19:42 | 2020-09-22 23:19:42 | NULL | utf8_bin | NULL | | Column privileges |
| def | mysql | db | BASE TABLE | MyISAM | 10 | Fixed | 2 | 488 | 976 | 137359788634800127 | 5120 | 0 | NULL | 2020-09-22 23:19:41 | 2020-09-22 23:19:52 | NULL | utf8_bin | NULL | | Database privileges |
| def | mysql | engine_cost | BASE TABLE | InnoDB | 10 | Dynamic | 2 | 8192 | 16384 | 0 | 0 | 0 | NULL | 2020-09-22 23:19:45 | NULL | NULL | utf8_general_ci | NULL | stats_persistent=0 | |
| def | mysql | event | BASE TABLE | MyISAM | 10 | Dynamic | 0 | 0 | 0 | 281474976710655 | 2048 | 0 | NULL | 2020-09-22 23:19:44 | 2020-09-22 23:19:44 | NULL | utf8_general_ci | NULL | | Events |
| def | mysql | func | BASE TABLE | MyISAM | 10 | Fixed | 0 | 0 | 0 | 162974011515469823 | 1024 | 0 | NULL | 2020-09-22 23:19:41 | 2020-09-22 23:19:41 | NULL | utf8_bin | NULL | | User defined functions |
| def | mysql | general_log | BASE TABLE | CSV | 10 | Dynamic | 2 | 0 | 0 | 0 | 0 | 0 | NULL | NULL | NULL | NULL | utf8_general_ci | NULL | | General log |
| def | mysql | gtid_executed | BASE TABLE | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 | 0 | 0 | NULL | 2020-09-22 23:19:45 | NULL | NULL | latin1_swedish_ci | NULL | | |
| def | mysql | help_category | BASE TABLE | InnoDB | 10 | Dynamic | 43 | 381 | 16384 | 0 | 16384 | 0 | NULL | 2020-09-22 23:19:42 | NULL | NULL | utf8_general_ci | NULL | stats_persistent=0 | help categories |
| def | mysql | help_keyword | BASE TABLE | InnoDB | 10 | Dynamic | 1045 | 109 | 114688 | 0 | 114688 | 0 | NULL | 2020-09-22 23:19:42 | NULL | NULL | utf8_general_ci | NULL | stats_persistent=0 | help keywords |
| def | mysql | help_relation | BASE TABLE | InnoDB | 10 | Dynamic | 1678 | 58 | 98304 | 0 | 0 | 0 | NULL | 2020-09-22 23:19:42 | NULL | NULL | utf8_general_ci | NULL | stats_persistent=0 | keyword-topic relation |
| def | mysql | help_topic | BASE TABLE | InnoDB | 10 | Dynamic | 833 | 1907 | 1589248 | 0 | 98304 | 4194304 | NULL | 2020-09-22 23:19:42 | NULL | NULL | utf8_general_ci | NULL | stats_persistent=0 | help topics |
| def | mysql | innodb_index_stats | BASE TABLE | InnoDB | 10 | Dynamic | 153 | 321 | 49152 | 0 | 0 | 0 | NULL | 2020-09-22 23:19:44 | NULL | NULL | utf8_bin | NULL | stats_persistent=0 | |
| def | mysql | innodb_table_stats | BASE TABLE | InnoDB | 10 | Dynamic | 21 | 780 | 16384 | 0 | 0 | 0 | NULL | 2020-09-22 23:19:44 | NULL | NULL | utf8_bin | NULL | stats_persistent=0 | |
| def | mysql | ndb_binlog_index | BASE TABLE | MyISAM | 10 | Dynamic | 0 | 0 | 0 | 281474976710655 | 1024 | 0 | NULL | 2020-09-22 23:19:44 | 2020-09-22 23:19:44 | NULL | latin1_swedish_ci | NULL | | |
| def | mysql | plugin | BASE TABLE | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 | 0 | 0 | NULL | 2020-09-22 23:19:41 | NULL | NULL | utf8_general_ci | NULL | stats_persistent=0 | MySQL plugins |
| def | mysql | proc | BASE TABLE | MyISAM | 10 | Dynamic | 54 | 5770 | 311588 | 281474976710655 | 4096 | 0 | NULL | 2020-09-22 23:19:43 | 2020-09-22 23:33:49 | NULL | utf8_general_ci | NULL | | Stored Procedures |
| def | mysql | procs_priv | BASE TABLE | MyISAM | 10 | Fixed | 0 | 0 | 0 | 266275327968280575 | 4096 | 0 | NULL | 2020-09-22 23:19:43 | 2020-09-22 23:19:43 | NULL | utf8_bin | NULL | | Procedure privileges |
| def | mysql | proxies_priv | BASE TABLE | MyISAM | 10 | Fixed | 1 | 837 | 837 | 235594555506819071 | 9216 | 0 | NULL | 2020-09-22 23:19:49 | 2020-09-22 23:19:56 | NULL | utf8_bin | NULL | | User proxy privileges |
| def | mysql | server_cost | BASE TABLE | InnoDB | 10 | Dynamic | 6 | 2730 | 16384 | 0 | 0 | 0 | NULL | 2020-09-22 23:19:45 | NULL | NULL | utf8_general_ci | NULL | stats_persistent=0 | |
| def | mysql | servers | BASE TABLE | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 | 0 | 0 | NULL | 2020-09-22 23:19:42 | NULL | NULL | utf8_general_ci | NULL | stats_persistent=0 | MySQL Foreign Servers table |
| def | mysql | slave_master_info | BASE TABLE | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 | 0 | 0 | NULL | 2020-09-22 23:19:44 | NULL | NULL | utf8_general_ci | NULL | stats_persistent=0 | Master Information |
| def | mysql | slave_relay_log_info | BASE TABLE | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 | 0 | 0 | NULL | 2020-09-22 23:19:44 | NULL | NULL | utf8_general_ci | NULL | stats_persistent=0 | Relay Log Information |
| def | mysql | slave_worker_info | BASE TABLE | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 | 0 | 0 | NULL | 2020-09-22 23:19:44 | NULL | NULL | utf8_general_ci | NULL | stats_persistent=0 | Worker Information |
| def | mysql | slow_log | BASE TABLE | CSV | 10 | Dynamic | 2 | 0 | 0 | 0 | 0 | 0 | NULL | NULL | NULL | NULL | utf8_general_ci | NULL | | Slow log |
| def | mysql | tables_priv | BASE TABLE | MyISAM | 10 | Fixed | 2 | 947 | 1894 | 266556802944991231 | 9216 | 0 | NULL | 2020-09-22 23:19:42 | 2020-09-22 23:19:56 | NULL | utf8_bin | NULL | | Table privileges |
| def | mysql | time_zone | BASE TABLE | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 | 0 | 0 | 1 | 2020-09-22 23:19:43 | NULL | NULL | utf8_general_ci | NULL | stats_persistent=0 | Time zones |
| def | mysql | time_zone_leap_second | BASE TABLE | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 | 0 | 0 | NULL | 2020-09-22 23:19:43 | NULL | NULL | utf8_general_ci | NULL | stats_persistent=0 | Leap seconds information for time zones |
| def | mysql | time_zone_name | BASE TABLE | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 | 0 | 0 | NULL | 2020-09-22 23:19:43 | NULL | NULL | utf8_general_ci | NULL | stats_persistent=0 | Time zone names |
| def | mysql | time_zone_transition | BASE TABLE | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 | 0 | 0 | NULL | 2020-09-22 23:19:43 | NULL | NULL | utf8_general_ci | NULL | stats_persistent=0 | Time zone transitions |
| def | mysql | time_zone_transition_type | BASE TABLE | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 | 0 | 0 | NULL | 2020-09-22 23:19:43 | NULL | NULL | utf8_general_ci | NULL | stats_persistent=0 | Time zone transition types |
| def | mysql | user | BASE TABLE | MyISAM | 10 | Dynamic | 3 | 132 | 396 | 281474976710655 | 4096 | 0 | NULL | 2020-09-22 23:19:41 | 2020-09-25 01:11:31 | NULL | utf8_bin | NULL | | Users and global privileges |
+---------------+--------------+---------------------------+------------+--------+---------+------------+------------+----------------+-------------+--------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+--------------------+-----------------------------------------+
31 rows in set (0.88 sec)
mysql> select * from columns where table_name = 'user';
+---------------+--------------+------------+------------------------+------------------+-----------------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+-----------------+-----------------------------------+------------+-------+---------------------------------+----------------+-----------------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | ORDINAL_POSITION | COLUMN_DEFAULT | IS_NULLABLE | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH | NUMERIC_PRECISION | NUMERIC_SCALE | DATETIME_PRECISION | CHARACTER_SET_NAME | COLLATION_NAME | COLUMN_TYPE | COLUMN_KEY | EXTRA | PRIVILEGES | COLUMN_COMMENT | GENERATION_EXPRESSION |
+---------------+--------------+------------+------------------------+------------------+-----------------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+-----------------+-----------------------------------+------------+-------+---------------------------------+----------------+-----------------------+
| def | mysql | user | Host | 1 | | NO | char | 60 | 180 | NULL | NULL | NULL | utf8 | utf8_bin | char(60) | PRI | | select,insert,update,references | | |
| def | mysql | user | User | 2 | | NO | char | 32 | 96 | NULL | NULL | NULL | utf8 | utf8_bin | char(32) | PRI | | select,insert,update,references | | |
| def | mysql | user | Select_priv | 3 | N | NO | enum | 1 | 3 | NULL | NULL | NULL | utf8 | utf8_general_ci | enum('N','Y') | | | select,insert,update,references | | |
| def | mysql | user | Insert_priv | 4 | N | NO | enum | 1 | 3 | NULL | NULL | NULL | utf8 | utf8_general_ci | enum('N','Y') | | | select,insert,update,references | | |
| def | mysql | user | Update_priv | 5 | N | NO | enum | 1 | 3 | NULL | NULL | NULL | utf8 | utf8_general_ci | enum('N','Y') | | | select,insert,update,references | | |
| def | mysql | user | Delete_priv | 6 | N | NO | enum | 1 | 3 | NULL | NULL | NULL | utf8 | utf8_general_ci | enum('N','Y') | | | select,insert,update,references | | |
| def | mysql | user | Create_priv | 7 | N | NO | enum | 1 | 3 | NULL | NULL | NULL | utf8 | utf8_general_ci | enum('N','Y') | | | select,insert,update,references | | |
| def | mysql | user | Drop_priv | 8 | N | NO | enum | 1 | 3 | NULL | NULL | NULL | utf8 | utf8_general_ci | enum('N','Y') | | | select,insert,update,references | | |
| def | mysql | user | Reload_priv | 9 | N | NO | enum | 1 | 3 | NULL | NULL | NULL | utf8 | utf8_general_ci | enum('N','Y') | | | select,insert,update,references | | |
| def | mysql | user | Shutdown_priv | 10 | N | NO | enum | 1 | 3 | NULL | NULL | NULL | utf8 | utf8_general_ci | enum('N','Y') | | | select,insert,update,references | | |
| def | mysql | user | Process_priv | 11 | N | NO | enum | 1 | 3 | NULL | NULL | NULL | utf8 | utf8_general_ci | enum('N','Y') | | | select,insert,update,references | | |
| def | mysql | user | File_priv | 12 | N | NO | enum | 1 | 3 | NULL | NULL | NULL | utf8 | utf8_general_ci | enum('N','Y') | | | select,insert,update,references | | |
| def | mysql | user | Grant_priv | 13 | N | NO | enum | 1 | 3 | NULL | NULL | NULL | utf8 | utf8_general_ci | enum('N','Y') | | | select,insert,update,references | | |
| def | mysql | user | References_priv | 14 | N | NO | enum | 1 | 3 | NULL | NULL | NULL | utf8 | utf8_general_ci | enum('N','Y') | | | select,insert,update,references | | |
| def | mysql | user | Index_priv | 15 | N | NO | enum | 1 | 3 | NULL | NULL | NULL | utf8 | utf8_general_ci | enum('N','Y') | | | select,insert,update,references | | |
| def | mysql | user | Alter_priv | 16 | N | NO | enum | 1 | 3 | NULL | NULL | NULL | utf8 | utf8_general_ci | enum('N','Y') | | | select,insert,update,references | | |
| def | mysql | user | Show_db_priv | 17 | N | NO | enum | 1 | 3 | NULL | NULL | NULL | utf8 | utf8_general_ci | enum('N','Y') | | | select,insert,update,references | | |
| def | mysql | user | Super_priv | 18 | N | NO | enum | 1 | 3 | NULL | NULL | NULL | utf8 | utf8_general_ci | enum('N','Y') | | | select,insert,update,references | | |
| def | mysql | user | Create_tmp_table_priv | 19 | N | NO | enum | 1 | 3 | NULL | NULL | NULL | utf8 | utf8_general_ci | enum('N','Y') | | | select,insert,update,references | | |
| def | mysql | user | Lock_tables_priv | 20 | N | NO | enum | 1 | 3 | NULL | NULL | NULL | utf8 | utf8_general_ci | enum('N','Y') | | | select,insert,update,references | | |
| def | mysql | user | Execute_priv | 21 | N | NO | enum | 1 | 3 | NULL | NULL | NULL | utf8 | utf8_general_ci | enum('N','Y') | | | select,insert,update,references | | |
| def | mysql | user | Repl_slave_priv | 22 | N | NO | enum | 1 | 3 | NULL | NULL | NULL | utf8 | utf8_general_ci | enum('N','Y') | | | select,insert,update,references | | |
| def | mysql | user | Repl_client_priv | 23 | N | NO | enum | 1 | 3 | NULL | NULL | NULL | utf8 | utf8_general_ci | enum('N','Y') | | | select,insert,update,references | | |
| def | mysql | user | Create_view_priv | 24 | N | NO | enum | 1 | 3 | NULL | NULL | NULL | utf8 | utf8_general_ci | enum('N','Y') | | | select,insert,update,references | | |
| def | mysql | user | Show_view_priv | 25 | N | NO | enum | 1 | 3 | NULL | NULL | NULL | utf8 | utf8_general_ci | enum('N','Y') | | | select,insert,update,references | | |
| def | mysql | user | Create_routine_priv | 26 | N | NO | enum | 1 | 3 | NULL | NULL | NULL | utf8 | utf8_general_ci | enum('N','Y') | | | select,insert,update,references | | |
| def | mysql | user | Alter_routine_priv | 27 | N | NO | enum | 1 | 3 | NULL | NULL | NULL | utf8 | utf8_general_ci | enum('N','Y') | | | select,insert,update,references | | |
| def | mysql | user | Create_user_priv | 28 | N | NO | enum | 1 | 3 | NULL | NULL | NULL | utf8 | utf8_general_ci | enum('N','Y') | | | select,insert,update,references | | |
| def | mysql | user | Event_priv | 29 | N | NO | enum | 1 | 3 | NULL | NULL | NULL | utf8 | utf8_general_ci | enum('N','Y') | | | select,insert,update,references | | |
| def | mysql | user | Trigger_priv | 30 | N | NO | enum | 1 | 3 | NULL | NULL | NULL | utf8 | utf8_general_ci | enum('N','Y') | | | select,insert,update,references | | |
| def | mysql | user | Create_tablespace_priv | 31 | N | NO | enum | 1 | 3 | NULL | NULL | NULL | utf8 | utf8_general_ci | enum('N','Y') | | | select,insert,update,references | | |
| def | mysql | user | ssl_type | 32 | | NO | enum | 9 | 27 | NULL | NULL | NULL | utf8 | utf8_general_ci | enum('','ANY','X509','SPECIFIED') | | | select,insert,update,references | | |
| def | mysql | user | ssl_cipher | 33 | NULL | NO | blob | 65535 | 65535 | NULL | NULL | NULL | NULL | NULL | blob | | | select,insert,update,references | | |
| def | mysql | user | x509_issuer | 34 | NULL | NO | blob | 65535 | 65535 | NULL | NULL | NULL | NULL | NULL | blob | | | select,insert,update,references | | |
| def | mysql | user | x509_subject | 35 | NULL | NO | blob | 65535 | 65535 | NULL | NULL | NULL | NULL | NULL | blob | | | select,insert,update,references | | |
| def | mysql | user | max_questions | 36 | 0 | NO | int | NULL | NULL | 10 | 0 | NULL | NULL | NULL | int(11) unsigned | | | select,insert,update,references | | |
| def | mysql | user | max_updates | 37 | 0 | NO | int | NULL | NULL | 10 | 0 | NULL | NULL | NULL | int(11) unsigned | | | select,insert,update,references | | |
| def | mysql | user | max_connections | 38 | 0 | NO | int | NULL | NULL | 10 | 0 | NULL | NULL | NULL | int(11) unsigned | | | select,insert,update,references | | |
| def | mysql | user | max_user_connections | 39 | 0 | NO | int | NULL | NULL | 10 | 0 | NULL | NULL | NULL | int(11) unsigned | | | select,insert,update,references | | |
| def | mysql | user | plugin | 40 | mysql_native_password | NO | char | 64 | 192 | NULL | NULL | NULL | utf8 | utf8_bin | char(64) | | | select,insert,update,references | | |
| def | mysql | user | authentication_string | 41 | NULL | YES | text | 65535 | 65535 | NULL | NULL | NULL | utf8 | utf8_bin | text | | | select,insert,update,references | | |
| def | mysql | user | password_expired | 42 | N | NO | enum | 1 | 3 | NULL | NULL | NULL | utf8 | utf8_general_ci | enum('N','Y') | | | select,insert,update,references | | |
| def | mysql | user | password_last_changed | 43 | NULL | YES | timestamp | NULL | NULL | NULL | NULL | 0 | NULL | NULL | timestamp | | | select,insert,update,references | | |
| def | mysql | user | password_lifetime | 44 | NULL | YES | smallint | NULL | NULL | 5 | 0 | NULL | NULL | NULL | smallint(5) unsigned | | | select,insert,update,references | | |
| def | mysql | user | account_locked | 45 | N | NO | enum | 1 | 3 | NULL | NULL | NULL | utf8 | utf8_general_ci | enum('N','Y') | | | select,insert,update,references | | |
+---------------+--------------+------------+------------------------+------------------+-----------------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+-----------------+-----------------------------------+------------+-------+---------------------------------+----------------+-----------------------+
45 rows in set (0.01 sec)
选择合适的分析方式
通常情况下，如果使用DatabaseMetaData来分析数据库信息，具有更好的跨数据库特性，应用程序可以做到数据库无关，但可能无法准确的获得数据库的更多细节
使用数据库系统表来分析数据库系统信息会更加准确，但这种方式与底层数据库耦合严重，只能运行在特定数据库上
一般情况下，如果需要获得数据库信息，包括数据库驱动提供了哪些功能，则应该使用DatabaseMetaData来了解该数据库支持哪些功能，因为可能底层数据库支持的功能数据库驱动没有提供该功能，如果需要纯粹的分析数据库的静态对象，则利用数据库系统表更合适
为了更好的通用性，程序可以通过DatabaseMetaData的getDatabaseProductName()、getDatabaseProductVersion()方法来获取底层数据库的产品名产品版本号，还可以通过DatabaseMetaData的getDriverName()和getDriverVersion()方法获取驱动程序名和驱动程序版本号
扫一扫
1670
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
