如何在子查询中使用SHOW CREATE TABLE? [英] How can I use SHOW CREATE TABLE in a subquery?

问题描述

我正在尝试完成这样的事情:

I'm trying to accomplish something like this:

SELECT *
FROM   information_schema.`tables`
    JOIN (SHOW CREATE TABLE)  # <-- need help here
WHERE  table_schema LIKE 'tables\_%'

有没有一种方法可以在一个查询中做到这一点?

Is there a way to do this in one query?

推荐答案

Devart是正确的，因为您不能加入SHOW CREATE语句. 但是，根据您的确切需求，您可以通过创建自己的SHOW CREATE语句来进行欺骗.

Devart is correct that you can't join to the SHOW CREATE statement. However, depending on your exact needs, you can spoof this by creating your own SHOW CREATE statement.

如果您需要包括数据库引擎，列和表的排序规则，索引等，则代码的复杂性将增加-但是，下面的SQL将为您提供正确的表和字段以及完整的数据类型.我相信您可以通过更深入地检查information_schema.columns的内容来进一步扩展它.

The complexity of the code will increase if you need to include the database engine, column and table collations, indexes, and so forth - however the SQL below will give you the right table and fields, complete with datatypes. I'm sure you can extend it further by examining the contents of information_schema.columns in more depth.

SELECT CONCAT('CREATE TABLE `',t.TABLE_NAME,'` ',
  GROUP_CONCAT(CONCAT(c.COLUMN_NAME,' ',c.COLUMN_TYPE,' ',c.EXTRA) SEPARATOR ','),';') AS CreateStatement
FROM information_schema.tables t
INNER JOIN information_schema.columns c 
  ON t.TABLE_NAME=c.TABLE_NAME
/* WHERE STATEMENT IF NEEDED */;

示例输出:

CREATE TABLE `answers` rowid int(11) auto_increment,
  id int(11) ,username varchar(200) ,answer varchar(2000) ;

这篇关于如何在子查询中使用SHOW CREATE TABLE?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！