Shulou(Shulou.com)06/01 Report--

Editor to share with you how MySQL uses Navicat to export data dictionary, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

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

This is a little trick.

Using the COLUMNS table in information_schema of mysql

And the export function in navicat to achieve fast export of data dictionary

CREATE TEMPORARYTABLE `COLUMNS` (`TABLE_ CATALOG` varchar) NOTNULL DEFAULT'', `CATALOG` varchar (64) NOTNULL DEFAULT'', `TABLE_ NAME` varchar (64) NOTNULL DEFAULT'', `COLUMN_ NAME` varchar (64) NOTNULL DEFAULT'', `ORDINAL_ POSITN` bigint (21) unsigned NOTNULL DEFAULT' 0, `COLUMN_ DEFAULT` longtext, `IS_ NULLABLE` varchar (3) NOTNULL DEFAULT'', `DATA_ TYPE` varchar (64) NOTNULL 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_ PRECIION` bigint (21) unsignedDEFAULT NULL, `CHARACTER_SET_ NAME` varchar (32) unsignedDEFAULT NULL, `COLLATION_ NAME` varchar (32) DEFAULTNULL, `COLUMN_ TYPE` longtext NOT NULL, `COLUMN_ KEY` varchar (3) NOT NULLDEFAULT', `EXTRA` varchar (30) NOT NULLDEFAULT'', `PRIVILEGES` varchar (80) NOT NULLDEFAULT', `COLUMN_ COMMENT` varchar (1024) NOT NULLDEFAULT') ENGINE=MyISAMDEFAULT CHARSET=utf8

For example, I need to export data dictionary information for guifan libraries and test libraries:

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 a button for the export function. Click to select a variety of export formats, as shown in the following figure.

Follow the prompts to export

OK, this is the data dictionary I want.

The above is all the contents of the article "how MySQL uses Navicat to Export data Dictionary". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.