mysql 数据表所有字段,如何打印mysql数据库中所有表的所有字段？

I'm trying to learn the structure of a mysql database I'm not familiar with. It's been years since I used mysql.

I'm looking for a way to print all the fields for all the tables in this database. At the moment, I'm using

Show Fields from

However, this slow and clunky. Is there a faster way?

解决方案

To see all tables of a specific database (like mydb), do this:

USE mydb

SHOW TABLES;

To see all fields, indexes, storage engine, table options, partition layout in mydb.mytable, do this:

USE mydb

SHOW CREATE TABLE tblname\G

To see all tables in all databases in bulk, here is a script:

MYSQL_USER=root

MYSQL_PASS=rootpassword

MYSQL_CONN="-u${MYSQL} -p${MYSQL_PASS}"

MYSQLDUMP_OPTIONS="--routines --triggers --no-data --all-databases"

mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} > MySQLSchema.sql

less MySQLSchema.sql

If you want to see a specific database (like mydb), do this:

MYSQL_USER=root

MYSQL_PASS=rootpassword

MYSQL_CONN="-u${MYSQL} -p${MYSQL_PASS}"

DBTOSHOW=mydb

MYSQLDUMP_OPTIONS="--routines --triggers --no-data --databases ${DBTOSHOW}"

mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} > MySQLSchema.sql

less MySQLSchema.sql

This should be the quickest way because accessing the information_schema database can be somewhat slow if there are a lot of busy InnoDB tables.

Give it a Try !!!