使用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來分析資料庫資訊，具有更好的跨資料庫特性，應用程式可以做到資料庫無關，但可能無法準确的獲得資料庫的更多細節
使用資料庫系統表來分析資料庫系統資訊會更加準确，但這種方式與底層資料庫耦合嚴重，隻能運作在特定資料庫上
![在netron provider network環境中使用metadata[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)