Introduction to the method of MySQL exporting data dictionary using Navicat

This article brings you an introduction to the method of exporting the data dictionary of MySQL using Navicat. It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you.

The data dictionary is an important content that a DBA needs to maintain. Some people like to use excel to maintain it. I prefer to maintain it directly on the production library. This can ensure that the comments in the production library are up to date. In order to facilitate the circulation of the production database data dictionary, the content will be recorded in excel. This article records how to quickly export the data dictionary information from the production database to excel.

Here is a little trick

Use the COLUMNS table in the information_schema of mysql

and the export function in navicat to quickly export the data dictionary

CREATE TEMPORARYTABLE `COLUMNS` (
  `TABLE_CATALOG` varchar(512) NOT NULL DEFAULT'',
  `TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT'',
  `TABLE_NAME` varchar(64) NOT NULL DEFAULT '',
  `COLUMN_NAME` varchar(64) NOT NULL DEFAULT'',
  `ORDINAL_POSITION` bigint(21) unsigned NOTNULL DEFAULT '0',
  `COLUMN_DEFAULT` longtext,
  `IS_NULLABLE` varchar(3) NOT NULL DEFAULT '',
  `DATA_TYPE` varchar(64) NOT NULL DEFAULT '',
  `CHARACTER_MAXIMUM_LENGTH` bigint(21)unsigned DEFAULT NULL,
  `CHARACTER_OCTET_LENGTH` bigint(21) unsignedDEFAULT NULL,
  `NUMERIC_PRECISION` bigint(21) unsignedDEFAULT NULL,
  `NUMERIC_SCALE` bigint(21) unsigned DEFAULTNULL,
  `DATETIME_PRECISION` bigint(21) unsignedDEFAULT NULL,
  `CHARACTER_SET_NAME` varchar(32) DEFAULTNULL,
  `COLLATION_NAME` varchar(32) DEFAULT NULL,
  `COLUMN_TYPE` longtext NOT NULL,
  `COLUMN_KEY` varchar(3) NOT NULL DEFAULT '',
  `EXTRA` varchar(30) NOT NULL DEFAULT '',
  `PRIVILEGES` varchar(80) NOT NULL DEFAULT '',
  `COLUMN_COMMENT` varchar(1024) NOT NULLDEFAULT ''
) ENGINE=MyISAMDEFAULT CHARSET=utf8;

For example, I need to export the data dictionary information of the guifan library and the test library:

select TABLE_SCHEMA,TABLE_NAME,COLUMN_TYPE,COLUMN_COMMENT frominformation_schema.columns where TABLE_SCHEMA='guifan' or  TABLE_SCHEMA='test'

In the upper right corner of the query results page, there is an export function button. Click it to select multiple export formats, as shown below As shown

Follow the prompts to export

OK, this is the data dictionary I want

At this point, the data dictionary is quickly exported.

The above is the detailed content of Introduction to the method of MySQL exporting data dictionary using Navicat. For more information, please follow other related articles on the PHP Chinese website!