blocks|key|500929|text|要获取所有表的名称，请使用：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|500930|SELECT+table_name+FROM+information_schema.tables;|code-block|syntax|javascript|500931|要从特定数据库中获取表的名称，请使用：|500932|SELECT+table_name+FROM+information_schema.tables
WHERE+table_schema+=+'your_database_name';|500933|现在，要回答原始问题，请使用以下查询：|500934|INSERT+INTO+table_name
++++SELECT+table_name+FROM+information_schema.tables
++++++++WHERE+table_schema+=+'your_database_name';|500935|有关详细信息，请参阅：http://dev.mysql.com/doc/refman/5.0/en/information-schema.html|offset|length|500936|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.0/en/information-schema.html^0|0|0|0|0|0|0|B|1Q|0|0^^$0|@$1|2|3|4|5|6|7|10|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|11|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|12|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|13|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|14|8|@]|9|@]|A|$]]|$1|M|3|N|5|D|7|15|8|@]|9|@]|A|$E|F]]|$1|O|3|P|5|6|7|16|8|@]|9|@$Q|17|R|18|1|19]]|A|$]]|$1|S|3|-4|5|6|7|1A|8|@]|9|@]|A|$]]]|T|$U|$5|V|W|X|A|$Y|Z]]]]

To get the name of all tables use:

<pre><code>SELECT table_name FROM information_schema.tables;
</code></pre>

To get the name of the tables from a specific database use:

<pre><code>SELECT table_name FROM information_schema.tables
WHERE table_schema = 'your_database_name';
</code></pre>

Now, to answer the original question, use this query:

<pre><code>INSERT INTO table_name
 SELECT table_name FROM information_schema.tables
 WHERE table_schema = 'your_database_name';
</code></pre>

For more details see: <a href="http://dev.mysql.com/doc/refman/5.0/en/information-schema.html" rel="noreferrer">http://dev.mysql.com/doc/refman/5.0/en/information-schema.html</a>

blocks|key|500764|text|尝试：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|500765|select+*+from+information_schema.tables|code-block|syntax|javascript|500766|请参阅：http://dev.mysql.com/doc/refman/5.0/en/information-schema.html|offset|length|500767|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.0/en/information-schema.html^0|0|0|4|1Q|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@]|9|@$I|V|J|W|1|X]]|A|$]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|L|$M|$5|N|O|P|A|$Q|R]]]]

Try:

<pre><code>select * from information_schema.tables
</code></pre>

See: <a href="http://dev.mysql.com/doc/refman/5.0/en/information-schema.html" rel="noreferrer">http://dev.mysql.com/doc/refman/5.0/en/information-schema.html</a>

blocks|key|716499|text|如果我们有多个数据库，并且需要为特定数据库选择所有表，我们可以使用TABLE_SCHEMA将数据库名称定义为：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|716500|select+table_name+from+information_schema.tables+where+TABLE_SCHEMA='dbname';|716501|entityMap^0|X|C|0|0|25|0^^$0|@$1|2|3|4|5|6|7|J|8|@$9|K|A|L|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|M|8|@$9|N|A|O|B|C]]|D|@]|E|$]]|$1|H|3|-4|5|6|7|P|8|@]|D|@]|E|$]]]|I|$]]

if we have multiple databases and we need to select all tables for a particular database we can use <code>TABLE_SCHEMA</code> to define database name as:

<code>select table_name from information_schema.tables where TABLE_SCHEMA='dbname';</code>

blocks|key|500871|text|除了使用INFORMATION_SCHEMA表之外，要使用SHOW+TABLES插入到表中，还需要使用以下命令|type|unstyled|depth|inlineStyleRanges|entityRanges|data|500872|<?php
+$sql+=+"SHOW+TABLES+FROM+$dbname";
+$result+=+mysql_query($sql);
+$arrayCount+=+0
+while+($row+=+mysql_fetch_row($result))+{
++$tableNames[$arrayCount]+=+$row[0];
++$arrayCount%2B%2B;+//only+do+this+to+make+sure+it+starts+at+index+0
+}
+foreach+($tableNames+as+&$name+{
++$query+=+"INSERT+INTO+metadata+(table_name)+VALUES+('".$name."')";
++mysql_query($query);
+}
?>|code-block|syntax|javascript|500873|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Besides using the INFORMATION_SCHEMA table, to use SHOW TABLES to insert into a table you would use the following

<pre><code>&lt;?php
 $sql = "SHOW TABLES FROM $dbname";
 $result = mysql_query($sql);
 $arrayCount = 0
 while ($row = mysql_fetch_row($result)) {
 $tableNames[$arrayCount] = $row[0];
 $arrayCount++; //only do this to make sure it starts at index 0
 }
 foreach ($tableNames as &amp;$name {
 $query = "INSERT INTO metadata (table_name) VALUES ('".$name."')";
 mysql_query($query);
 }
?&gt;
</code></pre>

blocks|key|716223|text|看看数据库information_schema中的表TABLES。它包含有关其他数据库中的表的信息。但如果你使用的是共享主机，你可能无法访问它。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|716224|entityMap^0|5|I|Q|6|0^^$0|@$1|2|3|4|5|6|7|H|8|@$9|I|A|J|B|C]|$9|K|A|L|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|M|8|@]|D|@]|E|$]]]|G|$]]

Take a look at the table <code>TABLES</code> in the database <code>information_schema</code>. It contains information about the tables in your other databases. But if you're on shared hosting, you probably don't have access to it.

blocks|key|716591|text|SELECT+table_name+
FROM+information_schema.tables+
WHERE+table_schema+=+'DATABASE'|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|716592|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>SELECT table_name 
FROM information_schema.tables 
WHERE table_schema = 'DATABASE'
</code></pre>

blocks|key|500756|text|我认为您可以从INFORMATION_SCHEMA表中获得所需的数据。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|500757|你可以在这里找到更多信息：http://dev.mysql.com/doc/refman/5.0/en/tables-table.html|offset|length|500758|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.0/en/tables-table.html^0|0|D|1K|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|O|8|@]|9|@$D|P|E|Q|1|R]]|A|$]]|$1|F|3|-4|5|6|7|S|8|@]|9|@]|A|$]]]|G|$H|$5|I|J|K|A|$L|M]]]]

I think you can get the data you want from INFORMATION_SCHEMA TABLES.

You can find more info here: <a href="http://dev.mysql.com/doc/refman/5.0/en/tables-table.html" rel="noreferrer">http://dev.mysql.com/doc/refman/5.0/en/tables-table.html</a>

blocks|key|716638|text|MySQL+INFORMATION_SCHEMA.TABLES表包含有关表(不是临时的，但永久的)和视图的数据。列TABLE_TYPE定义这是表的记录还是视图的记录(对于表TABLE_TYPE='BASE+TABLE'和对于视图TABLE_TYPE='VIEW')。因此，如果您只想从模式(数据库)表中查看，可以使用以下查询：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|716639|SELECT+*
FROM+information_schema.tables
WHERE+table_type='BASE+TABLE'
AND+table_schema='myschema'|code-block|syntax|javascript|716640|entityMap^0|6|P|1L|A|2E|N|36|H|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]|$9|P|A|Q|B|C]|$9|R|A|S|B|C]|$9|T|A|U|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|V|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|W|8|@]|D|@]|E|$]]]|L|$]]

MySQL <code>INFORMATION_SCHEMA.TABLES</code> table contains data about both tables (not temporary but permanent ones) and views. The column <code>TABLE_TYPE</code> defines whether this is record for table or view (for tables <code>TABLE_TYPE='BASE TABLE'</code> and for views <code>TABLE_TYPE='VIEW'</code>). So if you want to see from your schema (database) tables only there's the following query :

<pre><code>SELECT *
FROM information_schema.tables
WHERE table_type='BASE TABLE'
AND table_schema='myschema'
</code></pre>

blocks|key|501204|text|还有另一种更简单的方法来获取表名|type|unstyled|depth|inlineStyleRanges|entityRanges|data|501205|SHOW+TABLES+FROM+<database_name>|code-block|syntax|javascript|501206|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

There is yet another simpler way to get table names

<pre><code>SHOW TABLES FROM &lt;database_name&gt;
</code></pre>

blocks|key|716756|text|我认为需要指出的是，如果您想选择包含特定单词的表，您可以使用SELECT+(而不是SHOW)轻松地完成此操作。下面的查询很容易将搜索范围缩小到包含"keyword“的表|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|716757|SELECT+*
FROM+information_schema.tables
WHERE+table_name+like+"%25keyword%25"|code-block|syntax|javascript|716758|entityMap^0|U|6|15|4|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]|$9|P|A|Q|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|R|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|S|8|@]|D|@]|E|$]]]|L|$]]

I think it may be helpful to point out that if you want to select tables that contain specific words you can easily do it using the <code>SELECT</code> (instead of <code>SHOW</code>). Below query easily narrows down the search to tables that contain "keyword"

<pre><code>SELECT *
FROM information_schema.tables
WHERE table_name like "%keyword%"
</code></pre>

blocks|key|501322|text|下面这个查询对我很有效。这可以显示数据库、表、列名、数据类型和列数。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|501323|**select+table_schema+Schema_Name+,table_name+TableName,column_name+ColumnName,ordinal_position+"Position",column_type+DataType,COUNT(1)+ColumnCount
FROM+information_schema.columns
GROUP+by+table_schema,table_name,column_name,ordinal_position,+column_type;**|code-block|syntax|javascript|501324|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

This below query worked for me. This can able to show the databases,tables,column names,data types and columns count.

<pre><code>**select table_schema Schema_Name ,table_name TableName,column_name ColumnName,ordinal_position "Position",column_type DataType,COUNT(1) ColumnCount
FROM information_schema.columns
GROUP by table_schema,table_name,column_name,ordinal_position, column_type;**
</code></pre>

blocks|key|716865|text|是的，使用information_schema.TABLES。即使在像Skyvia这样的云解决方案上也可以使用，如下所示：|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|716866|​|716867|📷|atomic|716868|716869|这样，您就可以简单地使用类似于上面的SELECT语句将其添加到INSERT语句中。但是要更改table_schema值，使其与实际设置的数据库名称相匹配。|716870|entityMap|0|LINK|mutability|MUTABLE|url|https://skyvia.com/query/online-mysql-query-builder|1|IMAGE|IMMUTABLE|imageUrl|https://ask.qcloudimg.com/http-save/yehe-900000/7dfa8d41a270a215024c09f5f244e143.png|imageAlt^0|Z|6|0|0|0|0|1|1|0|0|0^^$0|@$1|2|3|4|5|6|7|Z|8|@]|9|@$A|10|B|11|1|12]]|C|$]]|$1|D|3|E|5|6|7|13|8|@]|9|@]|C|$]]|$1|F|3|G|5|H|7|14|8|@]|9|@$A|15|B|16|1|17]]|C|$]]|$1|I|3|E|5|6|7|18|8|@]|9|@]|C|$]]|$1|J|3|K|5|6|7|19|8|@]|9|@]|C|$]]|$1|L|3|-4|5|6|7|1A|8|@]|9|@]|C|$]]]|M|$N|$5|O|P|Q|C|$R|S]]|T|$5|U|P|V|C|$W|X|Y|-4]]]]

Yes, using information_schema.TABLES.
This works even on a cloud solution like <a href="https://skyvia.com/query/online-mysql-query-builder" rel="nofollow noreferrer">Skyvia</a> shown below:
<a href="https://i.stack.imgur.com/q35fw.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/q35fw.png" alt="enter image description here" /></a>
With this you can simply use a SELECT statement like the above to add it up for your INSERT statement. But change the table_schema value to match the database name for your actual setup.

blocks|key|501074|text|要插入、更新和删除，请执行以下操作：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|501075|$teste+=+array('LOW_PRIORITY',+'DELAYED',+'HIGH_PRIORITY',+'IGNORE',+'INTO',+'INSERT',+'UPDATE',+'DELETE',+'QUICK',+'FROM');
$teste1+=+array("\t",+"\n",+"\r",+"\0",+"\x0B");
$strsql+=+trim(str_ireplace($teste1,+'+',+str_ireplace($teste,+'',+$strsql)));
$nomeTabela+=+substr($strsql,+0,+strpos($strsql,+'+'));

print($nomeTabela);
exit;|code-block|syntax|javascript|501076|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

To insert, update and delete do the following:

<pre><code>$teste = array('LOW_PRIORITY', 'DELAYED', 'HIGH_PRIORITY', 'IGNORE', 'INTO', 'INSERT', 'UPDATE', 'DELETE', 'QUICK', 'FROM');
$teste1 = array("\t", "\n", "\r", "\0", "\x0B");
$strsql = trim(str_ireplace($teste1, ' ', str_ireplace($teste, '', $strsql)));
$nomeTabela = substr($strsql, 0, strpos($strsql, ' '));

print($nomeTabela);
exit;
</code></pre>

In MySQL, I know I can list the tables in a database with:

<pre><code>SHOW TABLES
</code></pre>

However, I want to insert these table names into another table, for instance:

<pre><code>INSERT INTO metadata(table_name) SHOW TABLES /* does not work */
</code></pre>

Is there a way to get the table names using a standard SELECT statement, something like:

<pre><code>INSERT INTO metadata(table_name) SELECT name FROM table_names /* what should table_names be? */
</code></pre>

Get table names using SELECT statement in MySQL

数据库

MySQL

在MySQL中，我知道可以用以下命令列出数据库中的表：SHOW TABLES但是，我想将这些表名插入到另一个表中，例如：INSERT INTO metadata(table_name) SHOW TABLES /* does not work */有没有一种方法可以使用标准的SELECT语句来获取表名，例如：INSERT INTO metadata(table_name) SELECT name 

问在MySQL中使用SELECT语句获取表名
EN

回答 13

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在MySQL中使用SELECT语句获取表名EN

回答 13

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在MySQL中使用SELECT语句获取表名
EN