如何使用存储过程将 MySQL 架构导出到 GitHub Markdown？

MySQL schema 可以借助存储过程导出为 GitHub markdown 格式。下面是一个使用两个存储过程的示例，它们可以链接在一起以获得所需的输出。

存储过程链

第一个存储过程准备用于报告的数据并分配一个会话号以保持输出隔离。

CREATE PROCEDURE `Reporting101a`.`describeTables_v2a`(
    IN dbName varchar(100), -- the dbname to report table structures
    OUT theSession int, -- OUT parameter for session# assigned
    IN deleteSessionRows BOOL, -- true for delete rows when done from main reporting table for this session#
    IN callTheSecondStoredProc BOOL -- TRUE = output is from Pretty output in Second Stored Proc. FALSE= not so pretty output
)
BEGIN

    DECLARE thisTable CHAR(100);

    DROP TEMPORARY TABLE IF EXISTS Reporting101a.tOutput;
    CREATE TEMPORARY TABLE Reporting101a.tOutput
    (   id int auto_increment primary key,
        tblName varchar(100) not null,
        ordVal int not null,
        cField varchar(100) not null,
        cType varchar(100) not null,
        cNull varchar(100) not null,
        cKey varchar(100) not null,
        cDefault varchar(100) null,
        cExtra varchar(100) null
    );

    INSERT Reporting101a.tOutput(tblName,ordVal,cField,cType,cNull,cKey,cDefault,cExtra)
    SELECT TABLE_NAME,ORDINAL_POSITION,COLUMN_NAME AS Field, COLUMN_TYPE AS TYPE, RPAD(IS_NULLABLE,4,' ') AS 'Null', 
    RPAD(COLUMN_KEY,3,' ') AS 'Key',RPAD(COLUMN_DEFAULT,7,' ') AS 'DEFAULT',EXTRA AS Extra
    FROM information_schema.columns WHERE table_schema = dbName ORDER BY table_name,ordinal_position; 
    -- select * from information_schema.columns WHERE table_schema = '57security' order by table_name,ordinal_position; 

    ...

END$$

第二个存储过程使用游标和特殊表生成漂亮的输出，类似于 MySQL 的 DESCRIBE 命令。

CREATE PROCEDURE `Reporting101a`.`Print_Tables_Like_Describe`(
    pSessionId INT
)
BEGIN
    DECLARE done INT DEFAULT FALSE;
    ...

    CREATE TABLE IF NOT EXISTS Reporting101a.reportOutput
    (   lineNum INT AUTO_INCREMENT PRIMARY KEY,
        sessionId INT NOT NULL,
        lineOut varchar(100) NOT NULL
    );

    ...

END$$

用法

使用所需参数调用第一个存储过程。

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

-- See **Note3**
-- Note: with `TRUE` as the 4th parameter, this is a one call deal. Meaning, you are done.
call Reporting101a.describeTables_v2a('stackoverflow',@theOutVar,false,true);

-- See **Note4**
-- Primarily used if the 4th parameter above is false
call Reporting101a.Print_Tables_Like_Describe(@theOutVar); -- loads data for prettier results in chunk format.

输出

输出将在reportOutput表中生成，并可以按如下方式检索：

select lineOut as '' from Reporting101a.reportOutput where sessionId=pSessionId order by lineNum;

以上是如何使用存储过程将 MySQL 架构导出到 GitHub Markdown？的详细内容。更多信息请关注PHP中文网其他相关文章！