(Mysql)Navicat如何导出Excel格式表结构

妖狐艹你老母 2022-01-11 05:23 499阅读 0赞

**一、点击navicat查询（新建）**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3NDkzNTU2_size_16_color_FFFFFF_t_70]  
**二、把如下查询sql语句复制粘贴进去，修改对应的数据库名称和表名称**

SELECT
    	COLUMN_NAME 列名,
    	COLUMN_TYPE 数据类型,
    	DATA_TYPE 字段类型,
    	CHARACTER_MAXIMUM_LENGTH 长度,
    	IS_NULLABLE 是否为空,
    	COLUMN_DEFAULT 默认值,
    	COLUMN_COMMENT 备注
    FROM
    	INFORMATION_SCHEMA. COLUMNS
    WHERE
    	-- senta-service-mdt2.0为数据库名称，到时候只需要修改成你要导出表结构的数据库即可
    	table_schema = 'senta-service-mdt2.0'
    AND -- s_patient_list为表名，到时候换成你要导出的表的名称
    -- 如果不写的话，默认会查询出所有表中的数据，这样可能就分不清到底哪些字段是哪张表中的了，所以还是建议写上要导出的名名称
    table_name = 's_patient_list'

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3NDkzNTU2_size_16_color_FFFFFF_t_70 1]  
**三、复制需要的表结构**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3NDkzNTU2_size_16_color_FFFFFF_t_70 2]  
**四、粘贴到excel表格中**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3NDkzNTU2_size_16_color_FFFFFF_t_70 3]

**备注：mysql INFORMATION\_SCHEMA COLUMNS 字段说明**

<table> 
 <thead> 
  <tr> 
   <th>标准名称</th> 
   <th>SHOW名称</th> 
   <th>注释</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>TABLE_CATALOG</td> 
   <td></td> 
   <td>表目录</td> 
  </tr> 
  <tr> 
   <td>TABLE_SCHEMA</td> 
   <td></td> 
   <td>表架构</td> 
  </tr> 
  <tr> 
   <td>TABLE_NAME</td> 
   <td>表名</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>COLUMN_NAME</td> 
   <td>Field</td> 
   <td>列名</td> 
  </tr> 
  <tr> 
   <td>ORDINAL_POSITION</td> 
   <td>列位置</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>COLUMN_DEFAULT</td> 
   <td>Default</td> 
   <td>默认值</td> 
  </tr> 
  <tr> 
   <td>IS_NULLABLE</td> 
   <td>Null</td> 
   <td>是否允许为空</td> 
  </tr> 
  <tr> 
   <td>DATA_TYPE</td> 
   <td>Type</td> 
   <td>数据类型</td> 
  </tr> 
  <tr> 
   <td>CHARACTER_MAXIMUM_LENGTH</td> 
   <td>Type</td> 
   <td>最大长度</td> 
  </tr> 
  <tr> 
   <td>CHARACTER_OCTET_LENGTH</td> 
   <td></td> 
   <td>八位字节长度</td> 
  </tr> 
  <tr> 
   <td>NUMERIC_PRECISION</td> 
   <td>Type</td> 
   <td>精度</td> 
  </tr> 
  <tr> 
   <td>NUMERIC_SCALE</td> 
   <td>Type</td> 
   <td>小数位</td> 
  </tr> 
  <tr> 
   <td>CHARACTER_SET_NAME</td> 
   <td></td> 
   <td>字符集</td> 
  </tr> 
  <tr> 
   <td>COLLATION_NAME</td> 
   <td>Collation</td> 
   <td>排序规则</td> 
  </tr> 
  <tr> 
   <td>COLUMN_TYPE</td> 
   <td>Type</td> 
   <td>字段类型</td> 
  </tr> 
  <tr> 
   <td>COLUMN_KEY</td> 
   <td>Key</td> 
   <td>字段主键</td> 
  </tr> 
  <tr> 
   <td>EXTRA</td> 
   <td>Extra</td> 
   <td>额外信息</td> 
  </tr> 
  <tr> 
   <td>COLUMN_COMMENT</td> 
   <td>Comment</td> 
   <td>注释</td> 
  </tr> 
 </tbody> 
</table>

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3NDkzNTU2_size_16_color_FFFFFF_t_70]: /images/20220111/783fb8c275a24910a87726ea26f41b10.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3NDkzNTU2_size_16_color_FFFFFF_t_70 1]: /images/20220111/ba8b239b08de466eaa75b637dd2ddf8d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3NDkzNTU2_size_16_color_FFFFFF_t_70 2]: /images/20220111/de69b30c15224fe89594b7a4b2e88d9f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3NDkzNTU2_size_16_color_FFFFFF_t_70 3]: /images/20220111/c81645d99e9f4af59ab820274a570f4a.png