写在前面：

大家好，我是强哥，一个热爱分享的技术狂。目前已有 12 年大数据与 AI 相关项目经验， 10 年推荐系统研究及实践经验。平时喜欢读书、暴走和写作。

业余时间专注于输出大数据、AI 等相关文章，目前已经输出了 40 万字的推荐系统系列精品文章，强哥的畅销书「构建企业级推荐系统：算法、工程实现与案例分析」已经出版，需要提升可以私信我呀。如果这些文章能够帮助你快速入门，实现职场升职加薪，我将不胜欢喜。

想要获得更多免费学习资料或内推信息，一定要看到文章最后喔。

内推信息

如果你正在看相关的招聘信息，请加我微信：liuq4360，我这里有很多内推资源等着你，欢迎投递简历。

免费学习资料

如果你想获得更多免费的学习资料，请关注同名公众号【数据与智能】，输入“资料”即可！

学习交流群

如果你想找到组织，和大家一起学习成长，交流经验，也可以加入我们的学习成长群。群里有老司机带你飞，另有小哥哥、小姐姐等你来勾搭！加小姐姐微信：epsila，她会带你入群。

2020 Learning SQL-中译

第十五章元数据

除了存储用户插入数据库的所有数据，数据库服务器还需要存储为存储这些数据而创建的所有数据库对象（表、视图、索引等）的信息。数据库服务器会存储这些信息其实并不奇怪。本章讨论如何以及在何处存储这些信息（称为元数据），如何访问这些信息，以及如何使用这些信息构建灵活的系统。

关于数据的数据

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

• 表名；

• 表存储信息（表空间、初始大小等）；

• 存储引擎；

• 列名；

• 列数据类型；

• 默认列值；

• 非空列约束；

• 主键列；

• 主键名称；

• 主键索引的名称；

• 索引名；

• 索引类型（B 树、位图）；

• 索引列；

• 索引列排序顺序（升序或降序）；

• 索引存储信息；

• 外键名称；

• 外键列；

• 外键的关联表/列。

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

虽已存在用于在不同服务器之间交换元数据的标准，但每个数据库服务器使用的还是不同的机制来提供元数据，例如：

• 一组视图，如 Oracle Database 的 user_tables 和 all_constraints 视图；

• 一组系统存储过程，如 SQL Server 的 sp_tables 过程或 Oracle Database 的 dbms_metadata 包；

• 一种特殊数据库，如 MySQL 的 information_schema 数据库。

SQL Server 除了带有 Sybase 系列数据库痕迹的系统存储过程，还包括一种称为 information_schema 的特殊模式，该模式在每个数据库中自动提供。MySQL 和 SQL Server 都提供了符合 ANSI SQL:2003 标准的接口，本章的剩余部分将讨论这些 information_schema 对象。

信息模式

在 information_schema 数据库（或在 SQL Server 中被称为模式(schema)）中所有可用对象都是视图。与我在前面几章使用 describe 实用工具说明各种表和视图结构的方法不同，information_schema 中的视图是可以被检索的，因此可以通过编程方式使用（将在本章后面详细介绍）。下面的示例演示如何检索 Sakila 数据库中所有表的名称：

mysql> SELECT table_name, table_type 

-> FROM information_schema.tables 

-> WHERE table_schema = 'sakila' 

-> ORDER BY 1;

+----------------------------+------------+

| TABLE_NAME | TABLE_TYPE |

+----------------------------+------------+

| actor | BASE TABLE |

| actor_info | VIEW |

| address | BASE TABLE |

| category | BASE TABLE |

| city | BASE TABLE |

| country | BASE TABLE |

| customer | BASE TABLE |

| customer_list | VIEW |

| film | BASE TABLE |

| film_actor | BASE TABLE |

| film_category | BASE TABLE |

| film_list | VIEW |

| film_text | BASE TABLE |

| inventory | BASE TABLE |

| language | BASE TABLE |

| nicer_but_slower_film_list | VIEW |

| payment | BASE TABLE |

| rental | BASE TABLE |

| sales_by_film_category | VIEW |

| sales_by_store | VIEW |

| staff | BASE TABLE |

| staff_list | VIEW |

| store | BASE TABLE |

+----------------------------+------------+

23 rows in set (0.00 sec)

如你所见，information_schema.tables 视图包括表和视图。如果要在结果中排除视图，只需向 where 子句再添加一个条件：

mysql> SELECT table_name, table_type 

-> FROM information_schema.tables 

-> WHERE table_schema = 'sakila' 

-> AND table_type = 'BASE TABLE' 

-> ORDER BY 1;

+---------------+------------+

| TABLE_NAME | TABLE_TYPE |

+---------------+------------+

| actor | BASE TABLE |

| address | BASE TABLE |

| category | BASE TABLE |

| city | BASE TABLE |

| country | BASE TABLE |

| customer | BASE TABLE |

| film | BASE TABLE |

| film_actor | BASE TABLE |

| film_category | BASE TABLE |

| film_text | BASE TABLE |

| inventory | BASE TABLE |

| language | BASE TABLE |

| payment | BASE TABLE |

| rental | BASE TABLE |

| staff | BASE TABLE |

| store | BASE TABLE |

+---------------+------------+

16 rows in set (0.00 sec)

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

mysql> SELECT table_name, is_updatable 

-> FROM information_schema.views 

-> WHERE table_schema = 'sakila' 

-> ORDER BY 1;

+----------------------------+--------------+

| TABLE_NAME | IS_UPDATABLE |

+----------------------------+--------------+

| actor_info | NO |

| customer_list | YES |

| film_list | NO |

| nicer_but_slower_film_list | NO |

| sales_by_film_category | NO |

| sales_by_store | NO |

| staff_list | YES |

+----------------------------+--------------+

7 rows in set (0.00 sec)

两个表的列信息和视图都可通过 columns 视图获得。以下查询显示 film 表的列信息：

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 = 'sakila' AND table_name = 'film' 

-> ORDER BY ordinal_position;

+----------------------+-----------+--------------+-----------+-----------+

| COLUMN_NAME | DATA_TYPE | char_max_len | num_prcsn | num_scale |

+----------------------+-----------+--------------+-----------+-----------+

| film_id | smallint | NULL | 5 | 0 |

| title | varchar | 255 | NULL | NULL |

| description | text | 65535 | NULL | NULL |

| release_year | year | NULL | NULL | NULL |

| language_id | tinyint | NULL | 3 | 0 |

| original_language_id | tinyint | NULL | 3 | 0 |

| rental_duration | tinyint | NULL | 3 | 0 |

| rental_rate | decimal | NULL | 4 | 2 |

| length | smallint | NULL | 5 | 0 |

| replacement_cost | decimal | NULL | 5 | 2 |

| rating | enum | 5 | NULL | NULL |

| special_features | set | 54 | NULL | NULL |

| last_update | timestamp | NULL | NULL | NULL |

+----------------------+-----------+--------------+-----------+-----------+

13 rows in set (0.00 sec)

语句中包含 ordinal_position 列的目的只是按照列添加到表中的顺序检索列。

你可以通过 information_schema.statistics 视图获得表的索引信息，下面的查询检索 rental 表中索引的信息：

mysql> SELECT index_name, non_unique, seq_in_index, column_name 

-> FROM information_schema.statistics 

-> WHERE table_schema = 'sakila' AND table_name = 'rental' 

-> ORDER BY 1, 3;

+---------------------+------------+--------------+--------------+

| INDEX_NAME | NON_UNIQUE | SEQ_IN_INDEX | COLUMN_NAME |

+---------------------+------------+--------------+--------------+

| idx_fk_customer_id | 1 | 1 | customer_id |

| idx_fk_inventory_id | 1 | 1 | inventory_id |

| idx_fk_staff_id | 1 | 1 | staff_id |

| PRIMARY | 0 | 1 | rental_id |

| rental_date | 0 | 1 | rental_date |

| rental_date | 0 | 2 | inventory_id |

| rental_date | 0 | 3 | customer_id |

+---------------------+------------+--------------+--------------+

7 rows in set (0.02 sec)

rental 表总共有五个索引，其中一个有三列（rental_date），还有一个是用于主键约束的唯一索引（PRIMARY）。

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

mysql> SELECT constraint_name, table_name, constraint_type 

-> FROM information_schema.table_constraints 

-> WHERE table_schema = 'sakila' -> ORDER BY 3,1;

+---------------------------+---------------+-----------------+

| constraint_name | table_name | constraint_type |

+---------------------------+---------------+-----------------+

| fk_address_city | address | FOREIGN KEY |

| fk_city_country | city | FOREIGN KEY |

| fk_customer_address | customer | FOREIGN KEY |

| fk_customer_store | customer | FOREIGN KEY |

| fk_film_actor_actor | film_actor | FOREIGN KEY |

| fk_film_actor_film | film_actor | FOREIGN KEY |

| fk_film_category_category | film_category | FOREIGN KEY |

| fk_film_category_film | film_category | FOREIGN KEY |

| fk_film_language | film | FOREIGN KEY |

| fk_film_language_original | film | FOREIGN KEY |

| fk_inventory_film | inventory | FOREIGN KEY |

| fk_inventory_store | inventory | FOREIGN KEY |

| fk_payment_customer | payment | FOREIGN KEY |

| fk_payment_rental | payment | FOREIGN KEY |

| fk_payment_staff | payment | FOREIGN KEY |

| fk_rental_customer | rental | FOREIGN KEY |

| fk_rental_inventory | rental | FOREIGN KEY |

| fk_rental_staff | rental | FOREIGN KEY |

| fk_staff_address | staff | FOREIGN KEY |

| fk_staff_store | staff | FOREIGN KEY |

| fk_store_address | store | FOREIGN KEY |

| fk_store_staff | store | FOREIGN KEY |

| PRIMARY | film | PRIMARY KEY |

| PRIMARY | film_actor | PRIMARY KEY |

| PRIMARY | staff | PRIMARY KEY |

| PRIMARY | film_category | PRIMARY KEY |

| PRIMARY | store | PRIMARY KEY |

| PRIMARY | actor | PRIMARY KEY |

| PRIMARY | film_text | PRIMARY KEY |

| PRIMARY | address | PRIMARY KEY |

| PRIMARY | inventory | PRIMARY KEY |

| PRIMARY | customer | PRIMARY KEY |

| PRIMARY | category | PRIMARY KEY |

| PRIMARY | language | PRIMARY KEY |

| PRIMARY | city | PRIMARY KEY |

| PRIMARY | payment | PRIMARY KEY |

| PRIMARY | country | PRIMARY KEY |

| PRIMARY | rental | PRIMARY KEY |

| idx_email | customer | UNIQUE |

| idx_unique_manager | store | UNIQUE |

| rental_date | rental | UNIQUE |

+---------------------------+---------------+-----------------+

41 rows in set (0.02 sec)

下表（15-1）显示了 MySQL 8.0 版中可用的 information_schema 视图：

虽然其中一些视图（如 engines、events 和 plugins）是 MySQL 特有的，但也有许多视图在 SQL Server 中也是可用的。如果你使用的是 Oracle Database，请参阅在线“Oracle Database 参考指南”，以获取有关 user_视图、all_视图、dba_视图以及 dbms_metadata 包的信息。

使用元数据

如前所述，通过 SQL 查询检索模式对象有关信息的这种能力还是蛮有趣的。本节介绍在应用程序中使用元数据的几种方法。

模式生成脚本

虽然有些项目团队包括负责监督数据库的设计和实现的全职数据库设计师，但是也有许多项目采用设计委员会的方法，允许多人创建数据库对象。经过几周或几个月的开发之后，你可能需要生成一个脚本，该脚本将创建项目团队已部署的各种表、索引、视图等。尽管有各种工具和实用程序能够生成这些类型的脚本，但你也可以自行查询 information_schema 视图并生成脚本。

作为例子，让我们构建一个脚本来创建 sakila.category 表。下面是用于构建该表的命令，它是我从构建示例数据库的脚本中提取的：

CREATE TABLE category ( 

category_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT, 

name VARCHAR(25) NOT NULL, 

last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP  

ON UPDATE CURRENT_TIMESTAMP, 

PRIMARY KEY (category_id)

)ENGINE=InnoDB DEFAULT CHARSET=utf8;

尽管使用过程语言（例如 Transact-SQL 或 Java）生成脚本会更容易，但由于这是一本关于 SQL 的书，我将编写一个单一查询来生成 create table 语句。第一步是查询 information_schema.columns 表以检索有关表中列的信息：

mysql> SELECT 'CREATE TABLE category (' 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 AND extra LIKE 'DEFAULT_GENERATED%' 

-> THEN concat(' DEFAULT ',column_default,substr(extra,18)) 

-> WHEN extra IS NOT NULL THEN concat(' ', extra) 

-> ELSE '' 

-> END, 

-> ',') txt 

-> FROM information_schema.columns 

-> WHERE table_schema = 'sakila' AND table_name = 'category' -

> ORDER BY ordinal_position 

-> ) cols 

-> UNION ALL 

-> SELECT ')';

+-----------------------------------------------------------------------+

| create_table_statement |

+-----------------------------------------------------------------------+

| CREATE TABLE category ( |

| category_id tinyint(3) unsigned not null auto_increment, |

| name varchar(25) not null , |

| last_update timestamp not null DEFAULT CURRENT_TIMESTAMP |

| on update CURRENT_TIMESTAMP, |

| ) |

+-----------------------------------------------------------------------+

5 rows in set (0.00 sec)

现在完成得差不多啦，接下来为了检索有关主键约束的信息，需要添加对 table_constraints 和 key_column_usage 视图的查询：

mysql> SELECT 'CREATE TABLE category (' 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 AND extra LIKE 'DEFAULT_GENERATED%' 

-> THEN concat(' DEFAULT ',column_default,substr(extra,18)) 

-> WHEN extra IS NOT NULL THEN concat(' ', extra) 

-> ELSE '' 

-> END, 

-> ',') txt 

-> FROM information_schema.columns 

-> WHERE table_schema = 'sakila' AND table_name = 'category' 

-> ORDER BY ordinal_position 

-> ) cols -> UNION ALL 

-> SELECT concat(' constraint primary key (') 

-> FROM information_schema.table_constraints 

-> WHERE table_schema = 'sakila' AND table_name = 'category' 

-> 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 = 'sakila' AND table_name = 'category' 

-> AND constraint_name = 'PRIMARY' 

-> ORDER BY ordinal_position 

-> ) cols 

-> UNION ALL 

-> SELECT ' )' 

-> UNION ALL 

-> SELECT ')';

+-----------------------------------------------------------------------+

| create_table_statement |

+-----------------------------------------------------------------------+

| CREATE TABLE category ( |

| category_id tinyint(3) unsigned not null auto_increment, |

| name varchar(25) not null , |

| last_update timestamp not null DEFAULT CURRENT_TIMESTAMP |

| on update CURRENT_TIMESTAMP, |

| constraint primary key ( |

| category_id |

| ) |

| ) |

+-----------------------------------------------------------------------+

8 rows in set (0.02 sec)

为了查看语句的格式是否正确，我将把查询输出粘贴到 mysql 工具中（将表名更改为 category2 防止干扰其他表）：

mysql> CREATE TABLE category2 ( 

-> category_id tinyint(3) unsigned not null auto_increment, 

-> name varchar(25) not null , 

-> last_update timestamp not null DEFAULT CURRENT_TIMESTAMP  

-> on update CURRENT_TIMESTAMP, 

-> constraint primary key ( 

-> category_id 

-> ) 

-> );

Query OK,

0 rows affected (0.61 sec)

语句执行成功，现在 Sakila 数据库中有一个 category2 表啦。为了编写查询生成格式良好的 create table 语句，还需要做更多的工作（例如处理索引和外键约束），这些将作为习题留给你们解决。

注意：如果你使用的是图形化开发工具，如 Toad、Oracle SQL Developer 或 MySQL Workbench，则无需自行编写查询，而是可以轻松地生成这些类型的脚本。但是如果你只能用 mysql 命令行客户端的话……就得自行编写查询啦。

部署验证

许多组织允许使用数据库维护窗口，可以用来管理现有的数据库对象（例如添加/删除分区），还可以部署新模式和新对象。在部署脚本运行后，最好运行一个验证脚本，以确保新的模式对象具有合适的列、索引、主键等。下面的查询返回 Sakila 模式中每个表的列数、索引数和主键约束数（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 = 'sakila' AND tbl.table_type = 'BASE TABLE' 

-> ORDER BY 1;

+---------------+-------------+-------------+------------------+

| TABLE_NAME | num_columns | num_indexes | num_primary_keys |

+---------------+-------------+-------------+------------------+

| actor | 4 | 2 | 1 |

| address | 9 | 3 | 1 |

| category | 3 | 1 | 1 |

| city | 4 | 2 | 1 |

| country | 3 | 1 | 1 |

| customer | 9 | 7 | 1 |

| film | 13 | 4 | 1 |

| film_actor | 3 | 3 | 1 |

| film_category | 3 | 3 | 1 |

| film_text | 3 | 3 | 1 |

| inventory | 4 | 4 | 1 |

| language | 3 | 1 | 1 |

| payment | 7 | 4 | 1 |

| rental | 7 | 7 | 1 |

| staff | 11 | 3 | 1 |

| store | 4 | 3 | 1 |

+---------------+-------------+-------------+------------------+

16 rows in set (0.01 sec)

可以在部署前后都执行一次此语句，然后在宣布部署成功之前验证两组结果集之间的区别。

动态 SQL 生成

有些语言（如 Oracle 的 PL/SQL 和 Microsoft 的 Transact-SQL）是 SQL 语言的超集，这意味着除了像“if-then-else”和“while”这样的常用程序结构，它们的语法中还包括 SQL 语句。还有些语言（比如 Java）包括与关系数据库连接的能力，但语法中是不允许包含 SQL 语句的，也就是说所有的 SQL 语句都必须包含在字符串中。

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

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

mysql> SET @qry = 'SELECT customer_id, first_name, last_name FROM customer';

Query OK, 0 rows affected (0.00 sec)

mysql> PREPARE dynsql1 FROM @qry;

Query OK, 0 rows affected (0.00 sec)

Statement prepared

mysql> EXECUTE dynsql1;

+-------------+-------------+--------------+

| customer_id | first_name | last_name |

+-------------+-------------+--------------+

| 505 | RAFAEL | ABNEY |

| 504 | NATHANIEL | ADAM |

| 36 | KATHLEEN | ADAMS |

| 96 | DIANA | ALEXANDER |

...

| 31 | BRENDA | WRIGHT |

| 318 | BRIAN | WYMAN |

| 402 | LUIS | YANEZ |

| 413 | MARVIN | YEE |

| 28 | CYNTHIA | YOUNG |

+-------------+-------------+--------------+

599 rows in set (0.02 sec)

mysql> DEALLOCATE PREPARE dynsql1;

Query OK, 0 rows affected (0.00 sec)

set 语句只是简单地将一个字符串赋给变量 qry，然后使用 prepare 语句将该变量提交给数据库引擎（用于解析、安全检查和优化）。在调用 execute 执行语句后，必须使用 deallocate prepare 关闭语句，用以释放执行期间使用的所有数据库资源（比如游标）。

下一个示例演示如何执行包含占位符的查询，以达到在运行时指定条件的目的：

mysql> SET @qry = 'SELECT customer_id, first_name, last_name 

FROM customer WHERE customer_id = ?';

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 @custid = 9;

Query OK, 0 rows affected (0.00 sec)

mysql> EXECUTE dynsql2 USING @custid;

+-------------+------------+-----------+

| customer_id | first_name | last_name |

+-------------+------------+-----------+

| 9 | MARGARET | MOORE |

+-------------+------------+-----------+

1 row in set (0.00 sec)

mysql> SET @custid = 145;

Query OK, 0 rows affected (0.00 sec)

mysql> EXECUTE dynsql2 USING @custid;

+-------------+------------+-----------+

| customer_id | first_name | last_name |

+-------------+------------+-----------+

| 145 | LUCILLE | HOLMES |

+-------------+------------+-----------+

1 row in set (0.00 sec)

mysql> DEALLOCATE PREPARE dynsql2;

Query OK, 0 rows affected (0.00 sec)

这个查询包含一个占位符（就是语句末尾的？）以便在运行时提交 customer ID 值。语句一旦准备就会被执行两次，一次用于 customer ID 9，另一次用于 customer ID 145，然后语句会关闭。

你可能会想：这和元数据有什么关系？如果要使用动态 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 customer WHERE customer_id = ?') 

-> 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 = 'sakila' AND table_name = 'customer' 

-> GROUP BY table_name 

-> ) cols;

Query OK, 1 row affected (0.00 sec)

mysql> SELECT @qry;

+--------------------------------------------------------------------+

| @qry |

+--------------------------------------------------------------------+| SELECT customer_id,store_id,first_name,last_name,email, 

address_id,active,create_date,last_update  

FROM customer WHERE customer_id = ? |

+--------------------------------------------------------------------+

1 row in set (0.00 sec)

mysql> PREPARE dynsql3 FROM @qry;

Query OK, 0 rows affected (0.00 sec)

Statement prepared

mysql> SET @custid = 45;

Query OK, 0 rows affected (0.00 sec)

mysql> EXECUTE dynsql3 USING @custid;

+-------------+----------+------------+-----------+

| customer_id | store_id | first_name | last_name

+-------------+----------+------------+-----------+

| 45 | 1 | JANET | PHILLIPS

+-------------+----------+------------+-----------+ 

+-----------------------------------+------------+--------  

| email | address_id | active  

+-----------------------------------+------------+--------  

| JANET.PHILLIPS@sakilacustomer.org | 49 | 1  

+-----------------------------------+------------+--------   

+---------------------+---------------------+ 

| create_date | last_update | 

+---------------------+---------------------+

| 2006-02-14 22:04:36 | 2006-02-15 04:57:20 | 

+---------------------+---------------------+

1 row in set (0.00 sec)

mysql> DEALLOCATE PREPARE dynsql3;

Query OK, 0 rows affected (0.00 sec)

查询以 customer 表中的前九列为核心，使用 concat 和 concat_ws 函数构建查询字符串，并将该字符串分配给变量 qry，最后像以前一样执行查询字符串。

注意：通常，最好使用有循环结构的过程语言（如 Java、PL/SQL、Transact-SQL 或 MySQL 的存储过程语言）生成查询。但由于我想演示一个纯 SQL 示例，所以我不得不限制检索的列数到一个合适的数目，在本例中这个数字为 9。