MySQL 如何通过一条语句描述数据库中的所有表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20020728/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I describe all tables in the database through one statement?
提问by Sajad
Is there any statement that can describe all tables in a database?
有没有可以描述数据库中所有表的语句？
Something like this:
像这样的东西：
describe * from myDB;
回答by Hamed Kamrava
There is no statement that describeall tables at once. But you may want to do something like this :
没有声明describe所有表一次。但你可能想做这样的事情：
SELECT * FROM information_schema.columns WHERE table_schema = 'db_name';
回答by sivi
because the other suggestions made very much mess on the screen or just did not do the trick here is a hack for a small db:
因为其他建议在屏幕上弄得非常混乱，或者只是没有做到这一点，这是一个小数据库的黑客：
describe table_a; describe table_b; describe table_c;
and so on
等等
回答by Englebert Lai Pak Fu
I am using linux way. First create a ~/.my.cnf to store the username and password for mysql. Next use the snippet below and run it in the linux terminal.
我正在使用linux方式。首先创建一个~/.my.cnf来存放mysql的用户名和密码。接下来使用下面的代码片段并在 linux 终端中运行它。
Generate the tables list and filter the header and awk to generate the column. Then, use the same method to DESC table_name.
生成表列表并过滤标题和 awk 以生成列。然后，使用相同的方法来DESC table_name。
for i in $(mysql MYDBNAME -e 'SHOW TABLES' | grep -v "Tables_in" | awk '{print }'); do echo "TABLE: $i"; mysql MYDBNAME -e "DESC $i"; done
Hope this helps.
希望这可以帮助。
回答by quantumferret
This is a variation of @AlexShaffer's excellent comment, modified to mirror what the Mac terminal's mysql monitor outputs when asked to describe a table.
这是@AlexShaffer 出色评论的变体，经过修改以反映 Mac 终端的 mysql 监视器在要求描述表时输出的内容。
USE information_schema;
SELECT TABLE_NAME 'Table', COLUMN_NAME 'Field', COLUMN_TYPE 'Type', IS_NULLABLE 'Null',
COLUMN_KEY 'Key', COLUMN_DEFAULT 'Default', EXTRA 'Extra'
FROM information_schema.columns
WHERE table_schema = 'your_db'
ORDER BY TABLE_NAME;
回答by Kamal Soni
Please create the bash script like below and it will prompt you for details.
请创建如下所示的 bash 脚本，它会提示您输入详细信息。
LINUX ONLY - BASH SCRIPT- describe-all-tables.sh
仅限 LINUX - BASH 脚本- describe-all-tables.sh
#!/bin/sh
echo ""
read -p 'MySQL db: ' DB
echo ""
read -p 'MySQL user: ' USER
echo ""
read -e -p 'MySQL host: ' -i "localhost" HOSTNAME
echo ""
read -s -p 'MySQL password: ' PASSWORD
echo ""
mysql -N -u${USER} ${DB} -p${PASSWORD} -h ${HOSTNAME} --execute="show tables" | while read table; do mysql -u${USER} -h ${HOSTNAME} ${DB} -p${PASSWORD} -v -t --execute="describe $table"; echo -e "\n"; done
USAGE- /bin/sh describe-all-tables.sh
用法- /bin/sh describe-all-tables.sh
回答by Fredrik Nyman
mysql -B -N -u root -pPASSWORD -h somehost \
-e "SELECT DISTINCT CONCAT('describe ', table_name, ';') AS query FROM information_schema.tables WHERE table_schema='DATABASE_NAME_HERE' " | \
mysql -B -N -u root -pPASSWORD -h somehost DATABASE_NAME_HERE
回答by Venu A Positive
By default, Mysql not describe all tables in the database. The main reason Database main intention Just decentralize power and take care of metadata, but not index the data.
默认情况下，Mysql 不会描述数据库中的所有表。主要原因 数据库主要意图 只是分散权力并处理元数据，而不是索引数据。
Connect to the database:mysql [-u username] [-h hostname] database-name
连接数据库：mysql [-u username] [-h hostname] database-name
To list all databases, in the MySQL prompt type:show databases
要列出所有数据库，请在 MySQL 提示中键入：show databases
Then choose the right database:use MyDB;
然后选择合适的数据库：使用MyDB；
List all tables in the database:show tables;
列出数据库中的所有表：show tables；
Describe a table:desc table-name ordescribe table-name
描述表：desc table-name或describe table-name
回答by scottalan
Not sure if there is a way to get the results to display in a "table" format as it does when running the command from the mysql prompt, but this should describe all tables formatted vertically.
不确定是否有办法让结果以“表格”格式显示，就像从 mysql 提示符运行命令一样，但这应该描述所有垂直格式化的表格。
mysql -N -uUSER -pPASSWORD DATABASE_NAME --execute="show tables" | while read table; do mysql -uUSER -pPASSWORD DATABASE_NAME --execute="describe $table \G"; done
