Geek asks and answers

Export database schema as ASCII from mySQL so that I can publish it to SO

When I need to ask a question about the mySQL schema schema, I try to "write" my table schema, but its for quite a while.

so I checked SO - but I could not find the answer: is there a way to automatically generate an ASCII style database schema from mySQL (or phpmyadmin) automatically?

those. I want to get something like this automatically:

+----------+---------+----------------------------+
|  user_ID | user    |   roles                    |
+----------+---------+----------------------------+
|        1 |   Smith |    1                       |
+----------+---------+----------------------------+
|        2 |   Jones |    1                       |
+----------+---------+----------------------------+

how do people do that? What is the best way?

+5
source share
3 answers

Not sure if phpMyAdmin can do what you want, but you can get exactly that using mysql hint. Here's how:

Assuming you only need a table table my_table.

mysql, (-u) (-p), . , bin mysql ( Windows, mysql PATH),

mysql,

, :

show databases;

:

use this_database;

, ( ):

show tables;

, / , my_table, :

describe my_table;

ASCII-- , :

+--------------------+--------------+------+-----+---------+----------------+
| Field              | Type         | Null | Key | Default | Extra          |
+--------------------+--------------+------+-----+---------+----------------+
| id                 | int(11)      | NO   | PRI | NULL    | auto_increment |
| name               | varchar(50)  | NO   |     | NULL    |                |
| location_id        | int(11)      | NO   | MUL | NULL    |                |
| manager_id         | int(11)      | NO   | MUL | NULL    |                |
| other              | varchar(100) | YES  |     | NULL    |                |
| creation_timestamp | datetime     | NO   |     | NULL    |                |
| is_active          | tinyint(1)   | NO   |     | NULL    |                |
+--------------------+--------------+------+-----+---------+----------------+

+ , , , , , , SELECT. , - :

SELECT * FROM stores LIMIT 2;

:

+----+------------+-------------+------------+-------+---------------------+-----------+
| id | name       | location_id | manager_id | other | creation_timestamp  | is_active |
+----+------------+-------------+------------+-------+---------------------+-----------+
|  1 | Good Store |           1 |          1 |       | 2012-06-17 19:14:15 |         1 |
|  2 | Neu Store  |           2 |          1 |       | 2012-06-29 05:14:24 |         1 |
+----+------------+-------------+------------+-------+---------------------+-----------+

.. mysql, , , ( ), SO, /; linux/unix , Control+Shift+C, SO. , , , copy mark , , SO.

, SO, , SO, , , {}, . , 4 .

, , automatic, , , script, - xclip mysql, , automatically: -)

+8

, . , , .

MySQL .

, tee MySQL, , , . :

mysql> tee /tmp/nice_output.txt
mysql> SELECT 1 AS one, 2, 3 FROM DUAL;
+-----+---+---+
| one | 2 | 3 |
+-----+---+---+
|   1 | 2 | 3 |
+-----+---+---+

, , :

mysql> SELECT 1 AS one, 2, 3 FROM DUAL;
+-----+---+---+
| one | 2 | 3 |
+-----+---+---+
|   1 | 2 | 3 |
+-----+---+---+
1 row in set (0.00 sec)

, .

+2

phpMyAdmin , "" "", " " " ". "". , , , .

Edit: I just noticed that you asked for a “schema”, but your illustration has both a schema and data. Although you can export both to phpMyAdmin, it will not be well formatted, as your illustration shows. Perhaps the best way would be to do the export like CREATE TABLEand INSERTthen add it to SQLFiddle ? This has the advantage that the people answering your question can easily deploy an example and easily experiment.

+1
source

More articles:


All Articles