第15章元数据

# 第15章元数据

数据库服务器除了存储不同用户插入到数据库中的所有数据外，还需要存储关于为存储这些数据而创建的所有数据库对象（表、视图、索引等）的信息。不出所料，数据库服务器将这些信息存储在一个数据库中。本章将讨论这些被称为元数据（metadata）的信息是如何存储、存储在哪里、如何访问，以及如何利用它们构建灵活的系统。

# 关于数据的数据

元数据本质上就是关于数据的数据。每次创建一个数据库对象时，数据库服务器都需要记录各种信息。例如，如果要创建一个包含多个列、一个主键约束、三个索引和一个外键约束的表，数据库服务器就需要存储以下所有信息：

表名
表存储信息（表空间、初始大小等）
存储引擎
列名
列数据类型
列默认值
非空列约束（NOT NULL column constraints）
主键列
主键名称
主键索引名称
索引名称
索引类型（B树、位图）
索引列
索引列排序顺序（升序或降序）
索引存储信息
外键名称
外键列
外键关联的表/列

这些数据统称为数据字典（data dictionary）或系统目录（system catalog）。数据库服务器需要持久化存储这些数据，并且需要能够快速检索这些数据，以便验证和执行SQL语句。此外，数据库服务器必须保护这些数据，使其只能通过适当的机制（如alter table语句）进行修改。

虽然不同服务器之间交换元数据有相关标准，但每个数据库服务器都使用不同的机制来发布元数据，例如：

一组视图，如Oracle数据库的user_tables和all_constraints视图。
一组系统存储过程，如SQL Server的sp_tables过程或Oracle数据库的dbms_metadata包。
一个特殊的数据库，如MySQL的information_schema数据库。

除了作为Sybase遗留产物的SQL Server系统存储过程外，SQL Server还包含一个名为information_schema的特殊模式，每个数据库中都会自动提供该模式。MySQL和SQL Server都提供这个接口以符合ANSI SQL:2003标准。本章的其余部分将讨论MySQL和SQL Server中可用的information_schema对象。

# information_schema

information_schema数据库（在SQL Server中是模式）中所有可用的对象都是视图。与本书中多个章节用于展示各种表和视图结构的describe实用工具不同，information_schema中的视图可以进行查询，因此可以通过编程方式使用（本章后面会详细介绍）。下面的示例展示了如何检索bank数据库中所有表的名称：

mysql> SELECT table_name, table_type
    -> FROM information_schema.tables
    -> WHERE table_schema = 'bank'
    -> ORDER BY 1;
+-----------------------+------------+
| table_name            | table_type |
+-----------------------+------------+
| account               | BASE TABLE |
| branch                | BASE TABLE |
| branch_activity_vw    | VIEW       |
| business              | BASE TABLE |
| business_customer_vw  | VIEW       |
| customer              | BASE TABLE |
| customer_vw           | VIEW       |
| department            | BASE TABLE |
| employee              | BASE TABLE |
| employee_vw           | VIEW       |
| individual            | BASE TABLE |
| nh_customer_vw        | VIEW       |
| officer               | BASE TABLE |
| product               | BASE TABLE |
| product_type          | BASE TABLE |
| transaction           | BASE TABLE |
+-----------------------+------------+
16 rows in set (0.02 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

结果中除了第2章创建的各种表之外，还显示了第14章展示过的几个视图。如果想要排除视图，只需在where子句中添加另一个条件：

mysql> SELECT table_name, table_type
    -> FROM information_schema.tables
    -> WHERE table_schema = 'bank' AND table_type = 'BASE TABLE'
    -> ORDER BY 1;
+----------------+------------+
| table_name     | table_type |
+----------------+------------+
| account        | BASE TABLE |
| branch         | BASE TABLE |
| business       | BASE TABLE |
| customer       | BASE TABLE |
| department     | BASE TABLE |
| employee       | BASE TABLE |
| individual     | BASE TABLE |
| officer        | BASE TABLE |
| product        | BASE TABLE |
| product_type   | BASE TABLE |
| transaction    | BASE TABLE |
+----------------+------------+
11 rows in set (0.01 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

如果只对视图的信息感兴趣，可以查询information_schema.views。除了视图名称，还可以检索其他信息，例如一个显示视图是否可更新的标志：

mysql> SELECT table_name, is_updatable
    -> FROM information_schema.views
    -> WHERE table_schema = 'bank'
    -> ORDER BY 1;
+-----------------------+---------------+
| table_name            | is_updatable  |
+-----------------------+---------------+
| branch_activity_vw    | NO            |
| business_customer_vw  | YES           |
| customer_vw           | YES           |
| employee_vw           | YES           |
| nh_customer_vw        | YES           |
+-----------------------+---------------+
5 rows in set (1.83 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14

此外，只要查询足够短（MySQL中为4000个字符或更少），就可以使用view_definition列检索视图的底层查询。

通过columns视图可以获取表和视图的列信息。以下查询展示了account表的列信息：

mysql> SELECT column_name,
    ->        data_type,
    ->        character_maximum_length char_max_len,
    ->        numeric_precision num_prcsn,
    ->        numeric_scale num_scale
    -> FROM information_schema.columns
    -> WHERE table_schema = 'bank'
    ->   AND table_name = 'account'
    -> ORDER BY ordinal_position;
+--------------------+-----------+--------------+-----------+-----------+
| column_name        | data_type | char_max_len | num_prcsn | num_scale |
+--------------------+-----------+--------------+-----------+-----------+
| account_id         | int       | NULL         | 10        | 0         |
| product_cd         | varchar   | 10           | NULL      | NULL      |
| cust_id            | int       | NULL         | 10        | 0         |
| open_date          | date      | NULL         | NULL      | NULL      |
| close_date         | date      | NULL         | NULL      | NULL      |
| last_activity_date | date      | NULL         | NULL      | NULL      |
| status             | enum      | 6            | NULL      | NULL      |
| open_branch_id     | smallint  | NULL         | 5         | 0         |
| open_emp_id        | smallint  | NULL         | 5         | 0         |
| avail_balance      | float     | NULL         | 10        | 2         |
| pending_balance    | float     | NULL         | 10        | 2         |
+--------------------+-----------+--------------+-----------+-----------+
11 rows in set (0.02 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

ordinal_position列只是作为一种按照列添加到表中的顺序检索列的方式。

可以通过information_schema.statistics视图检索表索引的信息。以下查询检索了account表上构建的索引信息：

mysql> SELECT index_name, non_unique, seq_in_index, column_name
    -> FROM information_schema.statistics
    -> WHERE table_schema = 'bank' AND table_name = 'account'
    -> ORDER BY 1, 3;
+----------------+-------------+----------------+----------------+
| index_name     | non_unique  | seq_in_index   | column_name    |
+----------------+-------------+----------------+----------------+
| acc_bal_idx    | 1           | 1              | cust_id        |
| acc_bal_idx    | 1           | 2              | avail_balance  |
| fk_a_branch_id | 1           | 1              | open_branch_id |
| fk_a_emp_id    | 1           | 1              | open_emp_id    |
| fk_product_cd  | 1           | 1              | product_cd     |
| PRIMARY        | 0           | 1              | account_id     |
+----------------+-------------+----------------+----------------+
6 rows in set (0.09 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

account表总共有五个索引，其中一个有两列（acc_bal_idx），一个是唯一索引（PRIMARY）。

可以通过information_schema.table_constraints视图检索已创建的不同类型的约束（外键、主键、唯一约束）。以下查询检索了bank模式中的所有约束：

mysql> SELECT constraint_name, table_name, constraint_type
    -> FROM information_schema.table_constraints
    -> WHERE table_schema = 'bank'
    -> ORDER BY 3,1;
+---------------------+----------------+---------------------+
| constraint_name     | table_name     | constraint_type     |
+---------------------+----------------+---------------------+
| fk_a_branch_id      | account        | FOREIGN KEY         |
| fk_a_cust_id        | account        | FOREIGN KEY         |
| fk_a_emp_id         | account        | FOREIGN KEY         |
| fk_b_cust_id        | business       | FOREIGN KEY         |
| fk_dept_id          | employee       | FOREIGN KEY         |
| fk_exec_branch_id   | transaction    | FOREIGN KEY         |
| fk_e_branch_id      | employee       | FOREIGN KEY         |
| fk_e_emp_id         | employee       | FOREIGN KEY         |
| fk_i_cust_id        | individual     | FOREIGN KEY         |
| fk_o_cust_id        | officer        | FOREIGN KEY         |
| fk_product_cd       | account        | FOREIGN KEY         |
| fk_product_type_cd  | product        | FOREIGN KEY         |
| fk_teller_emp_id    | transaction    | FOREIGN KEY         |
| fk_t_account_id     | transaction    | FOREIGN KEY         |
| PRIMARY             | branch         | PRIMARY KEY         |
| PRIMARY             | account        | PRIMARY KEY         |
| PRIMARY             | product        | PRIMARY KEY         |
| PRIMARY             | department     | PRIMARY KEY         |
| PRIMARY             | customer       | PRIMARY KEY         |
| PRIMARY             | transaction    | PRIMARY KEY         |
| PRIMARY             | officer        | PRIMARY KEY         |
| PRIMARY             | product_type   | PRIMARY KEY         |
| PRIMARY             | employee       | PRIMARY KEY         |
| PRIMARY             | business       | PRIMARY KEY         |
| PRIMARY             | individual     | PRIMARY KEY         |
| dept_name_idx       | department     | UNIQUE              |
+---------------------+----------------+---------------------+
26 rows in set (2.28 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

表15-1展示了MySQL 6.0版本中所有可用的information_schema视图。 表15-1 information_schema视图

视图名称	提供关于…的信息
`Schemata`	数据库
`Tables`	表和视图
`Columns`	表和视图的列
`Statistics`	索引
`User_Privileges`	哪些用户对哪些模式对象拥有权限
`Schema_Privileges`	哪些用户对哪些数据库拥有权限
`Table_Privileges`	哪些用户对哪些表拥有权限
`Column_Privileges`	哪些用户对哪些表的哪些列拥有权限
`Character_Sets`	可用的字符集
`Collations`	哪些字符集可用哪些校对规则
`Collation_Character_Set_Applicability`	哪些校对规则适用于哪些字符集
`Table_Constraints`	唯一约束、外键约束和主键约束
`Key_Column_Usage`	与每个键列相关的约束
`Routines`	存储过程（过程和函数）
`Views`	视图
`Triggers`	表触发器
`Plugins`	服务器插件
`Engines`	可用的存储引擎
`Partitions`	表分区
`Events`	计划事件
`Process_List`	运行中的进程
`Referential_Constraints`	外键
`Global_Status`	服务器状态信息
`Session_Status`	会话状态信息
`Global_Variables`	服务器状态变量
`Session_Variables`	会话状态变量
`Parameters`	存储过程和函数参数
`Profiling`	用户分析信息

虽然其中一些视图（如engines、events和plugins）是MySQL特有的，但许多视图在SQL Server中也存在。如果使用的是Oracle数据库，请查阅在线《Oracle数据库参考指南》（http://www.oracle.com/pls/db111/portal.all_books ），了解有关user_、all_和dba_views的信息。

# 使用元数据

如前所述，能够通过SQL查询检索关于模式对象的信息开启了一些有趣的可能性。本节展示了在应用程序中使用元数据的几种方式。

# 模式生成脚本

虽然有些项目团队会有专职的数据库设计师来监督数据库的设计和实现，但许多项目采用 “委员会设计” 的方式，允许多人创建数据库对象。经过几周或几个月的开发后，可能需要生成一个脚本来创建团队部署的各种表、索引、视图等。虽然有各种工具和实用程序可以为生成这些脚本，但也可以查询information_schema视图并自己生成脚本。

例如，来构建一个创建bank.customer表的脚本。这是构建该表的命令，它是从构建示例数据库的脚本中提取出来的：

create table customer
(
    cust_id integer unsigned not null auto_increment,
    fed_id varchar(12) not null,
    cust_type_cd enum('I','B') not null,
    address varchar(30),
    city varchar(20),
    state varchar(20),
    postal_code varchar(10),
    constraint pk_customer primary key (cust_id)
);

1
2
3
4
5
6
7
8
9
10
11

虽然使用过程式语言（如Transact-SQL或Java）生成脚本肯定会更简单，但因为这是一本关于SQL的书，所以我打算编写一个单一查询来生成创建表的语句。第一步是查询information_schema.columns表，以检索有关表中列的信息：

mysql> SELECT 'CREATE TABLE customer (' create_table_statement
    -> UNION ALL
    -> SELECT cols.txt
    -> FROM
    ->     (SELECT concat('    ',column_name, ' ', column_type,
    ->             CASE
    ->                 WHEN is_nullable = 'NO' THEN ' not null'
    ->                 ELSE ''
    ->             END,
    ->             CASE
    ->                 WHEN extra IS NOT NULL THEN concat('   ', extra)
    ->                 ELSE ''
    ->             END,
    ->             ',') txt
    ->         FROM information_schema.columns
    ->         WHERE table_schema = 'bank' AND table_name = 'customer'
    ->         ORDER BY ordinal_position
    ->     ) cols
    -> UNION ALL
    -> SELECT ')';
+-----------------------------------------------------------------+
| create_table_statement                                          |
+-----------------------------------------------------------------+
| CREATE TABLE customer (                                         |
|     cust_id int(10) unsigned not null auto_increment,           |
|     fed_id varchar(12) not null ,                               |
|     cust_type_cd enum('I','B') not null ,                       |
|     address varchar(30) ,                                       |
|     city varchar(20) ,                                          |
|     state varchar(20) ,                                         |
|     postal_code varchar(10) ,                                   |
| )                                                               |
+-----------------------------------------------------------------+
9 rows in set (0.04 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

嗯，这让我们很接近目标了；我们只需要添加针对table_constraints和key_column_usage视图的查询，以检索有关主键约束的信息：

mysql> SELECT 'CREATE TABLE customer (' create_table_statement
    -> UNION ALL
    -> SELECT cols.txt
    -> FROM
    ->     (SELECT concat('    ',column_name, ' ', column_type,
    ->             CASE
    ->                 WHEN is_nullable = 'NO' THEN ' not null'
    ->                 ELSE ''
    ->             END,
    ->             CASE
    ->                 WHEN extra IS NOT NULL THEN concat('   ', extra)
    ->                 ELSE ''
    ->             END,
    ->             ',') txt
    ->         FROM information_schema.columns
    ->         WHERE table_schema = 'bank' AND table_name = 'customer'
    ->         ORDER BY ordinal_position
    ->     ) cols
    -> UNION ALL
    -> SELECT concat('    constraint primary key (')
    -> FROM information_schema.table_constraints
    -> WHERE table_schema = 'bank' AND table_name = 'customer'
    ->     AND constraint_type = 'PRIMARY KEY'
    -> UNION ALL
    -> SELECT cols.txt
    -> FROM
    ->     (SELECT concat(CASE WHEN ordinal_position > 1 THEN '      ,'
    ->             ELSE '        ' END, column_name) txt
    ->         FROM information_schema.key_column_usage
    ->         WHERE table_schema = 'bank' AND table_name = 'customer'
    ->             AND constraint_name = 'PRIMARY'
    ->         ORDER BY ordinal_position
    ->     ) cols
    -> UNION ALL
    -> SELECT '    )'
    -> UNION ALL
    -> SELECT ')';
+-----------------------------------------------------------------+
| create_table_statement                                          |
+-----------------------------------------------------------------+
| CREATE TABLE customer (                                         |
|     cust_id int(10) unsigned not null auto_increment,           |
|     fed_id varchar(12) not null ,                               |
|     cust_type_cd enum('I','B') not null ,                       |
|     address varchar(30) ,                                       |
|     city varchar(20) ,                                          |
|     state varchar(20) ,                                         |
|     postal_code varchar(10) ,                                   |
|     constraint primary key (                                    |
|         cust_id                                                 |
|     )                                                           |
| )                                                               |
+-----------------------------------------------------------------+
12 rows in set (0.02 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54

为了查看该语句是否格式正确，我将把查询输出粘贴到mysql工具中（我把表名改成了customer2，这样就不会与其他表冲突）：

mysql> CREATE TABLE customer2 (
    ->     cust_id int(10) unsigned not null auto_increment,
    ->     fed_id varchar(12) not null ,
    ->     cust_type_cd enum('I','B') not null ,
    ->     address varchar(30) ,
    ->     city varchar(20) ,
    ->     state varchar(20) ,
    ->     postal_code varchar(10) ,
    ->     constraint primary key (
    ->         cust_id
    ->     )
    -> );

1
2
3
4
5
6
7
8
9
10
11
12

Query OK, 0 rows affected (0.14 sec)

该语句执行时没有出错，并且bank数据库中现在有一个customer2表。为了使该查询能够为任何表生成格式正确的创建表语句，还需要做更多工作（例如处理索引和外键约束），但我把这留作练习。

# 部署验证

许多机构都允许设置数据库维护窗口，在这个窗口内可以管理现有数据库对象（如添加/删除分区），还可以部署新的模式对象（schema objects）和代码。在运行部署脚本之后，最好运行一个验证脚本，以确保新的模式对象已就位，并且具有适当的列、索引、主键等。下面这个查询返回bank模式中每个表的列数、索引数和主键约束数（0或1）：

mysql> SELECT tbl.table_name,
    ->     (SELECT count(*) FROM information_schema.columns clm
    ->         WHERE clm.table_schema = tbl.table_schema
    ->             AND clm.table_name = tbl.table_name) num_columns,
    ->     (SELECT count(*) FROM information_schema.statistics sta
    ->         WHERE sta.table_schema = tbl.table_schema
    ->             AND sta.table_name = tbl.table_name) num_indexes,
    ->     (SELECT count(*) FROM information_schema.table_constraints tc
    ->         WHERE tc.table_schema = tbl.table_schema
    ->             AND tc.table_name = tbl.table_name
    ->             AND tc.constraint_type = 'PRIMARY KEY') num_primary_keys
    -> FROM information_schema.tables tbl
    -> WHERE tbl.table_schema = 'bank' AND tbl.table_type = 'BASE TABLE'
    -> ORDER BY 1;
+----------------+--------------+--------------+------------------+
| table_name     | num_columns  | num_indexes  | num_primary_keys |
+----------------+--------------+--------------+------------------+
| account        | 11           | 6            | 1                |
| branch         | 6            | 1            | 1                |
| business       | 4            | 1            | 1                |
| customer       | 7            | 1            | 1                |
| department     | 2            | 2            | 1                |
| employee       | 9            | 4            | 1                |
| individual     | 4            | 1            | 1                |
| officer        | 7            | 2            | 1                |
| product        | 5            | 2            | 1                |
| product_type   | 2            | 1            | 1                |
| transaction    | 8            | 4            | 1                |
+----------------+--------------+--------------+------------------+
11 rows in set (13.83 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

你可以在部署前后执行这个语句，然后在宣布部署成功之前，验证两组结果之间的任何差异。

# 动态SQL生成

有些语言，如Oracle的PL/SQL和微软的Transact-SQL，是SQL语言的超集，这意味着它们在语法中既包含SQL语句，也包含常见的过程式结构，如“if - then - else”和“while”。而其他语言，如Java，虽然具备与关系数据库交互的能力，但语法中不包含SQL语句，这意味着所有SQL语句都必须包含在字符串中。

因此，包括SQL Server、Oracle Database和MySQL在内的大多数关系数据库服务器，都允许将SQL语句作为字符串提交到服务器。将字符串提交给数据库引擎，而不是使用其SQL接口，通常称为动态SQL执行。例如，Oracle的PL/SQL语言包含一个execute immediate命令，你可以使用它提交一个字符串来执行，而SQL Server包含一个名为sp_executesql的系统存储过程，用于动态执行SQL语句。

MySQL提供了prepare、execute和deallocate语句来支持动态SQL执行。下面是一个简单的示例：

mysql> SET @qry = 'SELECT cust_id, cust_type_cd, fed_id FROM customer';
Query OK, 0 rows affected (0.07 sec)
mysql> PREPARE dynsql1 FROM @qry;
Query OK, 0 rows affected (0.04 sec)
Statement prepared
mysql> EXECUTE dynsql1;

1
2
3
4
5
6

+---------+----------------+----------------+
| cust_id | cust_type_cd    | fed_id         |
+---------+----------------+----------------+
| 1       | I              | 111-11-1111     |
| 2       | I              | 222-22-2222     |
| 3       | I              | 333-33-3333     |
| 4       | I              | 444-44-4444     |
| 5       | I              | 555-55-5555     |
| 6       | I              | 666-66-6666     |
| 7       | I              | 777-77-7777     |
| 8       | I              | 888-88-8888     |
| 9       | I              | 999-99-9999     |
| 10      | B              | 04-1111111      |
| 11      | B              | 04-2222222      |
| 12      | B              | 04-3333333      |
| 13      | B              | 04-4444444      |
| 99      | I              | 04-9999999      |
+---------+----------------+----------------+
14 rows in set (0.27 sec)
mysql> DEALLOCATE PREPARE dynsql1;
Query OK, 0 rows affected (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

set语句只是将一个字符串赋值给qry变量，然后使用prepare语句将其提交给数据库引擎（进行解析、安全检查和优化）。通过调用execute执行语句后，必须使用deallocate prepare关闭该语句，这会释放执行过程中使用的任何数据库资源（例如游标）。

下一个示例展示了如何执行一个包含占位符的查询，以便在运行时指定条件：

mysql> SET @qry = 'SELECT product_cd, name, product_type_cd, date_offered, date_retired FROM product WHERE product_cd = ?';
Query OK, 0 rows affected (0.00 sec)
mysql> PREPARE dynsql2 FROM @qry;
Query OK, 0 rows affected (0.00 sec)
Statement prepared
mysql> SET @prodcd = 'CHK';
Query OK, 0 rows affected (0.00 sec)
mysql> EXECUTE dynsql2 USING @prodcd;
+-------------+---------------------+------------------+--------------+--------------+
| product_cd  | name                | product_type_cd  | date_offered | date_retired |
+-------------+---------------------+------------------+--------------+--------------+
| CHK         | checking account    | ACCOUNT          | 2004-01-01   | NULL         |
+-------------+---------------------+------------------+--------------+--------------+
1 row in set (0.01 sec)
mysql> SET @prodcd = 'SAV';
Query OK, 0 rows affected (0.00 sec)
mysql> EXECUTE dynsql2 USING @prodcd;
+-------------+------------------+------------------+--------------+--------------+
| product_cd  | name               | product_type_cd  | date_offered | date_retired |
+-------------+------------------+------------------+--------------+--------------+
| SAV         | savings account    | ACCOUNT          | 2004-01-01   | NULL         |
+-------------+------------------+------------------+--------------+--------------+
1 row in set (0.00 sec)
mysql> DEALLOCATE PREPARE dynsql2;
Query OK, 0 rows affected (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

在这个序列中，查询包含一个占位符（语句末尾的?），以便在运行时提交产品代码。该语句只准备一次，然后执行两次，一次使用产品代码'CHK'，另一次使用产品代码'SAV'，之后关闭该语句。

你可能会想，这与元数据有什么关系呢？嗯，如果你打算使用动态SQL查询表，为什么不使用元数据来构建查询字符串，而不是硬编码表定义呢？下面的示例生成与前面示例相同的动态SQL字符串，但它从information_schema.columns视图中检索列名：

mysql> SELECT concat('SELECT ',
    ->     concat_ws(',', cols.col1, cols.col2, cols.col3, cols.col4,
    ->         cols.col5, cols.col6, cols.col7, cols.col8, cols.col9),
    ->         ' FROM product WHERE product_cd = ?')
    -> INTO @qry
    -> FROM
    ->     (SELECT
    ->           max(CASE WHEN ordinal_position = 1 THEN column_name
    ->                ELSE NULL END) col1,
    ->           max(CASE WHEN ordinal_position = 2 THEN column_name
    ->                ELSE NULL END) col2,
    ->           max(CASE WHEN ordinal_position = 3 THEN column_name
    ->                ELSE NULL END) col3,
    ->           max(CASE WHEN ordinal_position = 4 THEN column_name
    ->                ELSE NULL END) col4,
    ->           max(CASE WHEN ordinal_position = 5 THEN column_name
    ->                ELSE NULL END) col5,
    ->           max(CASE WHEN ordinal_position = 6 THEN column_name
    ->                ELSE NULL END) col6,
    ->           max(CASE WHEN ordinal_position = 7 THEN column_name
    ->                ELSE NULL END) col7,
    ->           max(CASE WHEN ordinal_position = 8 THEN column_name
    ->                ELSE NULL END) col8,
    ->           max(CASE WHEN ordinal_position = 9 THEN column_name
    ->                 ELSE NULL END) col9
    ->      FROM information_schema.columns
    ->      WHERE table_schema = 'bank' AND table_name = 'product'
    ->       GROUP BY table_name
    ->     ) cols;
Query OK, 1 row affected (0.02 sec)
mysql> SELECT @qry;
+--------------------------------------------------------------------------------------------------+
| @qry                                                                                              |
+--------------------------------------------------------------------------------------------------+
| SELECT product_cd,name,product_type_cd,date_offered,date_retired FROM product WHERE product_cd = ? |
+--------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> PREPARE dynsql3 FROM @qry;
Query OK, 0 rows affected (0.01 sec)
Statement prepared
mysql> SET @prodcd = 'MM';
Query OK, 0 rows affected (0.00 sec)
mysql> EXECUTE dynsql3 USING @prodcd;
+-------------+---------------------------+------------------+--------------+--------------+
| product_cd  | name                      | product_type_cd  | date_offered | date_retired |
+-------------+---------------------------+------------------+--------------+--------------+
| MM          | money market account      | ACCOUNT          | 2004-01-01   | NULL         |
+-------------+---------------------------+------------------+--------------+--------------+
1 row in set (0.00 sec)
mysql> DEALLOCATE PREPARE dynsql3;
Query OK, 0 rows affected (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51

该查询将product表中的前九列进行透视，使用concat和concat_ws函数构建一个查询字符串，并将该字符串赋值给qry变量。然后像之前一样执行该查询字符串。

一般来说，使用包含循环结构的过程式语言（如Java、PL/SQL、Transact-SQL或MySQL的存储过程语言）来生成查询会更好。不过，我想展示一个纯SQL示例，所以不得不将检索的列数限制在一个合理的数量，在这个示例中是九列。

# 测试你的知识

以下练习旨在测试你对元数据的理解。完成后，请查看附录C获取答案。

# 练习15 - 1

编写一个查询，列出bank模式中的所有索引，并包含表名。

# 练习15 - 2

编写一个查询，生成可用于在bank.employee表上创建所有索引的输出。输出格式应为： "ALTER TABLE <table_name> ADD INDEX <index_name> (<column_list>)"

← 第14章视图附录A 示例数据库的实体关系图→

第15章 元数据

# 第15章 元数据

# 关于数据的数据

# information_schema

# 使用元数据

# 模式生成脚本

# 部署验证

# 动态SQL生成

# 测试你的知识

# 练习15 - 1

# 练习15 - 2

第15章元数据

# 第15章元数据