How to Convert MySQL Schema to GitHub Wiki Markdown Using Stored Procedures?

How to Convert MySQL Schema to GitHub Wiki Markdown

Challenge:

Exporting a MySQL database schema into markdown format for documentation purposes.

Solution:

Utilizing two stored procedures to accomplish this task:

First Stored Procedure (describeTables_v2a):

This procedure extracts the schema information from the specified database, prepares it, and stores it in session-based tables.

Second Stored Procedure (Print_Tables_Like_Describe):

This procedure generates output resembling MySQL's DESCRIBE statement for all tables in the specified database.

Usage:

1. Pass the database to be reported on as a parameter to describeTables_v2a.
2. Optionally, specify whether to delete the session data and whether to automatically call Print_Tables_Like_Describe.
3. If auto-calling is enabled, the output will be generated and displayed.
4. If auto-calling is disabled, call Print_Tables_Like_Describe with the session # obtained in step 1 to generate the output.

Example:

SET @theOutVar =-1; -- A variable used as the OUT variable below

-- Call describeTables_v2a with auto-calling enabled
call Reporting101a.describeTables_v2a('stackoverflow', @theOutVar, false, true);

Output:

The output will be similar to the following:

+--------------------------------------------------------------------------------------------+
|                                                                                            |
+--------------------------------------------------------------------------------------------+
| course                                                                                         |
| +------------+--------------+------+-----+---------+-------------------+
| | Field      | Type         | Null | Key | Default | Extra            |
| +------------+--------------+------+-----+---------+-------------------+
| | courseId   | int(11)      | NO   | PRI |         | auto_increment   |
| +------------+--------------+------+-----+---------+-------------------+
| | deptId     | int(11)      | NO   | MUL |         |                  |
| +------------+--------------+------+-----+---------+-------------------+
| | courseName | varchar(100) | NO   |     |         |                  |
| +------------+--------------+------+-----+---------+-------------------+
|                                                                                              |
| dept                                                                                           |
| +----------+--------------+------+-----+---------+-------------------+
| | Field    | Type         | Null | Key | Default | Extra            |
| +----------+--------------+------+-----+---------+-------------------+
| | deptId   | int(11)      | NO   | PRI |         | auto_increment   |
| +----------+--------------+------+-----+---------+-------------------+
| | deptName | varchar(100) | NO   |     |         |                  |
| +----------+--------------+------+-----+---------+-------------------+
|                                                                                              |
| scjunction                                                                                     |
| +------------+---------+------+-----+---------+-------------------+
| | Field      | Type    | Null | Key | Default | Extra            |
| +------------+---------+------+-----+---------+-------------------+
| | id         | int(11) | NO   | PRI |         | auto_increment   |
| +------------+---------+------+-----+---------+-------------------+
| | studentId  | int(11) | NO   | MUL |         |                  |
| +------------+---------+------+-----+---------+-------------------+
| | courseId   | int(11) | NO   | MUL |         |                  |
| +------------+---------+------+-----+---------+-------------------+
| | term       | int(11) | NO   |     |         |                  |
| +------------+---------+------+-----+---------+-------------------+
| | attendance | int(11) | NO   |     |         |                  |

The above is the detailed content of How to Convert MySQL Schema to GitHub Wiki Markdown Using Stored Procedures?. For more information, please follow other related articles on the PHP Chinese website!