mysql - 如何将SQL表结构生成为文本表？

Question

如何将MySQL表结构导出为文本版表？

我的意思是这样的:

+-----------+-----------+------+-----+-------------------+-----------------------------+
| Field     | Type      | Null | Key | Default           | Extra                       |
+-----------+-----------+------+-----+-------------------+-----------------------------+
| EID       | int(11)   | NO   | PRI | 0                 |                             |
| MOD_EID   | int(11)   | YES  |     | NULL              |                             |
| EXIT_TIME | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-----------+-----------+------+-----+-------------------+-----------------------------+

我确定有一些工具可以像这样导出表格。有谁知道如何从 MySQL 执行此操作？

Answer 1

您可以通过 3 种方式实现。

• DESC:易用性
• SHOW CREATE TABLE : 易于使用另一个表的相同模式创建新表
• information_schema:难以使用，但功能强大。

<强>1。使用描述

DESC $DB_NAME.$TBL_NAME;

sample output
mysql> DESC jsheo_test.test;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| name   | varchar(10) | YES  |     | NULL    |       |
| age    | int(11)     | YES  |     | NULL    |       |
| spent  | int(11)     | YES  |     | NULL    |       |
| gender | char(1)     | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+

<强>2。使用 SHOW CREATE TABLE

SHOW CREATE TABLE $DB_NAME.$TBL_NAME; 

sample output
mysql> SHOW CREATE TABLE jsheo_test.test\G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `name` varchar(10) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `spent` int(11) DEFAULT NULL,
  `gender` char(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

<强>3。使用信息架构

SELECT  TABLE_NAME
   , COLUMN_NAME 
   , ORDINAL_POSITION
   , DATA_TYPE
   , IS_NULLABLE 
FROM    INFORMATION_SCHEMA.COLUMNS 
WHERE   TABLE_SCHEMA = '$DB_NAME'
   AND TABLE_NAME = '$TBL_NAME'
ORDER BY TABLE_NAME, ORDINAL_POSITION;

sample output
+------------+-------------+------------------+-----------+-------------+
| TABLE_NAME | COLUMN_NAME | ORDINAL_POSITION | DATA_TYPE | IS_NULLABLE |
+------------+-------------+------------------+-----------+-------------+
| test       | name        |                1 | varchar   | YES         |
| test       | age         |                2 | int       | YES         |
| test       | spent       |                3 | int       | YES         |
| test       | gender      |                4 | char      | YES         |
+------------+-------------+------------------+-----------+-------------+

<强>4。来自外壳

使用 -e 选项运行

$  mysql -uusername -S ~/tmp/mysql.sock -e "DESC jsheo_test.test"
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| name   | varchar(10) | YES  |     | NULL    |       |
| age    | int(11)     | YES  |     | NULL    |       |
| spent  | int(11)     | YES  |     | NULL    |       |
| gender | char(1)     | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+

否则输出格式很奇怪。如下所示。

$ echo "desc jsheo_test.test;" | mysql -uusername -S /tmp/mysql.sock
Field   Type    Null    Key     Default Extra
name    varchar(10)     YES             NULL
age     int(11) YES             NULL
spent   int(11) YES             NULL
gender  char(1) YES             NULL