如何从MySQL / phpMyAdmin拉出db的模式？ [英] How to pull out schema of db from MySQL/phpMyAdmin?

问题描述

有没有办法拉出你的mysql数据库的模式？

is there some way of pulling out the schema of your mysql databases?

我在这些数据库中有很多数据库和表，我在本地机器中使用phpmyadmin和WAMP。

I have lots of db and tables inside these databases, and I use phpmyadmin with WAMP in my local machine.

我需要拉出模式，或至少数据库名称，表名称，列及其属性（例如 INT（2） code>）并导出为csv / excel /任何格式，最后可以在excel中编辑。

I need to pull out the schema, or atleast the database name, table name, columns and their attributes (e.g. INT(2)) and export it as csv/ excel/ whatever format which finally can be edited in excel.

我使用：

服务器：

    Apache/2.2.21 (Win64) PHP/5.3.8
    MySQL client version: mysqlnd 5.0.8-dev - 20102224 - $Revision: 310735 $
    PHP extension: mysqli Documentation

$ b b

mysql：

    Server: localhost (localhost via TCP/IP)
    Server version: 5.5.16-log
    Protocol version: 10
    User: root@localhost
    MySQL charset: UTF-8 Unicode (utf8)

谢谢！

推荐答案

您可以尝试以下方法之一：

Not sure exactly what you want. You can try one of these methods:

1）使用 phpMyAdmin 的导出功能导出数据库。

1) Use phpMyAdmin's export feature to export the database. PMA allows you to omit the data.

.com / doc / refman / 5.1 / en / mysqldump.htmlrel =noreferrer> mysqldump 。此命令应导出CREATE DATABASE / CREATE TABLE查询：

2) You can do the same using mysqldump. This command should export CREATE DATABASE/CREATE TABLE queries:

mysqldump -hlocalhost -uroot -proot --all-databases --no-data > create-database-and-tables.sql

3）来自mySQL模式表的信息。大多数mySQL客户端（phpMyAdmin，HeidiSQL等）允许您将查询结果导出为CSV。一些有用的查询：

3) You can pull information from mySQL schema tables. Most mySQL clients (phpMyAdmin, HeidiSQL etc) allow you to export result of queries as CSV. Some useful queries:

/*
 * DATABASE, TABLE, TYPE
 */
SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA NOT IN ('information_schema', 'performance_schema', 'mysql')
ORDER BY TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE

/*
 * DATABASE, TABLE, COLUMN, TYPE
 */
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, IS_NULLABLE /* ETC */
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA NOT IN ('information_schema', 'performance_schema', 'mysql')
ORDER BY TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION

这篇关于如何从MySQL / phpMyAdmin拉出db的模式？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！