如何从 MySQL/phpMyAdmin 中提取数据库模式? [英] How to pull out schema of db from MySQL/phpMyAdmin?

问题描述

有什么方法可以提取 mysql 数据库的架构吗?

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

我需要提取架构，或至少提取数据库名称、表名称、列及其属性(例如 INT(2))并将其导出为 csv/excel/任何格式，最终可以在excel中编辑.

我使用:

服务器:

<前>Apache/2.2.21 (Win64) PHP/5.3.8MySQL 客户端版本:mysqlnd 5.0.8-dev - 20102224 - $Revision: 310735 $PHP 扩展:mysqli 文档

mysql:

<前>服务器:本地主机(本地主机通过 TCP/IP)服务器版本:5.5.16-log协议版本:10用户:root@localhostMySQL 字符集:UTF-8 Unicode (utf8)

谢谢！

解决方案

不确定您想要什么.您可以尝试以下方法之一:

1) 使用phpMyAdmin 的导出功能导出数据库.PMA 允许您省略数据，以便获取所有 CREATE TABLE 语句.

2) 您可以使用 mysqldump.此命令应导出 CREATE DATABASE/CREATE TABLE 查询:

mysqldump -hlocalhost -uroot -proot --all-databases --no-data >创建数据库和表.sql

3) 您可以从 mySQL 模式表中提取信息.大多数 mySQL 客户端(phpMyAdmin、HeidiSQL 等)允许您将查询结果导出为 CSV.一些有用的查询:

/** 数据库、表、类型*/SELECT TABLE_SCHEMA、TABLE_NAME、TABLE_TYPE来自 INFORMATION_SCHEMA.TABLESWHERE TABLE_SCHEMA NOT IN ('information_schema', 'performance_schema', 'mysql')ORDER BY TABLE_SCHEMA、TABLE_NAME、TABLE_TYPE/** 数据库、表、列、类型*/SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, IS_NULLABLE/* ETC */来自 INFORMATION_SCHEMA.COLUMNSWHERE TABLE_SCHEMA NOT IN ('information_schema', 'performance_schema', 'mysql')ORDER BY TABLE_SCHEMA、TABLE_NAME、ORDINAL_POSITION

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

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

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.

i use:

server:

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

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)

thanks!

解决方案

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

1) Use phpMyAdmin's export feature to export the database. PMA allows you to omit the data so get all CREATE TABLE statements.

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) 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 中提取数据库模式?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！