代码之家 › 专栏 › 技术社区 › Steven M

将MySQL模式转换为Github Wiki?

wiki markdown github mysql

Steven M · 技术社区 · 8 年前

我正在使用GitHub Wiki页面记录一个项目,现在我想将MySQL数据库模式导出为降价格式。有没有办法将CLI输出转换为GitHub Wiki md?理想情况下为表格。

我的输出如下:

2 回复 | 直到 8 年前

Drew 8 年前

这段代码相当长。我道歉。它由两个存储过程组成。你可能会满足于只运行第一个。第二个使用第一个的输出(表中第一个留下的数据)。您可能还希望将代码合并为一个代码。但我把它们分开了。第二个存储的proc生成类似的输出 describe myTable 。但它为全部需要这样输出的数据库中的表。

您可以通过为要报告的数据库传递参数(字符串)来使用它。

EXECUTE 您可以从任何当前数据库运行它。因此,作为一个简单的测试,不要将报告数据库设置为当前数据库,而只需按名称(用报告数据库名称限定)调用存储过程。这些都显示在下面的测试块中。

两个存储过程

CREATE SCHEMA Reporting101a;    -- See **Note1**

DROP PROCEDURE IF EXISTS `Reporting101a`.`describeTables_v2a`;
DELIMITER $$
CREATE DEFINER=`root`@`localhost` 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
    );
    DROP TEMPORARY TABLE IF EXISTS Reporting101a.tOutput2;
    CREATE TEMPORARY TABLE Reporting101a.tOutput2
    (   tblName varchar(100) primary key,
        colCount int not null,
        cFieldMaxLen int not null,
        cTypeMaxLen int not null,
        cNullMaxLen int not null,
        cKeyMaxLen int not null,
        cDefaultMaxLen int not null,
        cExtraMaxLen int not 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; 

    UPDATE Reporting101a.tOutput
    SET cExtra='     '
    WHERE cExtra='';

    UPDATE Reporting101a.tOutput
    SET cField=RPAD(cField,5,' ')
    WHERE LENGTH(cField)<5;

    INSERT Reporting101a.tOutput2(tblName,colCount,cFieldMaxLen,cTypeMaxLen,cNullMaxLen,cKeyMaxLen,cDefaultMaxLen,cExtraMaxLen)
    SELECT tblName,COUNT(*),0,0,0,0,0,0 
    FROM Reporting101a.tOutput 
    GROUP BY tblName;

    UPDATE tOutput2 t2
    JOIN
    (   SELECT tblName,MAX(LENGTH(cField)) AS mField,MAX(LENGTH(cType)) AS mType,MAX(LENGTH(cNull)) AS mNull,
        IFNULL(MAX(LENGTH(cKey)),0) AS mKey,IFNULL(MAX(LENGTH(cDefault)),0) AS mDefault,IFNULL(MAX(LENGTH(cExtra)),0) AS mExtra
        FROM Reporting101a.tOutput
        GROUP BY tblName
    ) x
    ON x.tblName=t2.tblName
    SET t2.cFieldMaxLen=x.mField,t2.cTypeMaxLen=x.mType,cNullMaxLen=x.mNull,
    cKeyMaxLen=x.mKey,cDefaultMaxLen=x.mDefault,cExtraMaxLen=x.mExtra;

    -- DROP TABLE Reporting101a.reportDataDefsSession; -- useful for quick change of structure of table
    -- note, keep above drop call remmed out ! Just use it for quick tweaks to structure
    CREATE TABLE IF NOT EXISTS Reporting101a.reportDataDefsSession
    (   -- for the sole purpose of safe session auto_inc usage
        -- Please don't delete unless you want the sessions to experience aberant behavior
        sessionId INT AUTO_INCREMENT PRIMARY KEY,
        dummy CHAR(1) NOT NULL,
        creationDT datetime not null
    );

    CREATE TABLE IF NOT EXISTS Reporting101a.reportDataDefs
    (   sessionId INT NOT NULL,
        tblName VARCHAR(100) NOT NULL,  -- Tablename
        ordVal INT NOT NULL,    -- the "position number" of the Column
        cField VARCHAR(100) NOT NULL,   -- The Column
        cType VARCHAR(100) NOT NULL,    -- Datatype
        cNull VARCHAR(100) NOT NULL,    -- Nullability
        cKey VARCHAR(100) NOT NULL, -- Key info
        cDefault VARCHAR(100) NULL, -- Default value
        cExtra VARCHAR(100) NULL,   -- Extra output
        colCount INT NOT NULL,  -- the columns here and below are de-normalize data
        cFieldMaxLen INT NOT NULL,
        cTypeMaxLen INT NOT NULL,
        cNullMaxLen INT NOT NULL,
        cKeyMaxLen INT NOT NULL,
        cDefaultMaxLen INT NOT NULL,
        cExtraMaxLen INT NOT NULL
    );

    -- For lack of a better notion, we are calling calls "sessions". The programmer calls the
    -- First Stored Proc, and we call that a session after we get a unique next incrementing number.
    -- That number is the session #. House all output with that as a column value. This allows us to 
    -- move between stored procs, have safe output, have historical snapshots, and retain the data 
    -- via a session # for later use, whatever use.
    INSERT Reporting101a.reportDataDefsSession(dummy,creationDT) VALUES ('X',now());
    SET @mySession=LAST_INSERT_ID(); -- there it is, our session # (read the above paragraph)

    INSERT Reporting101a.reportDataDefs(sessionId,tblName,ordVal,cField,cType,cNull,cKey,cDefault,cExtra,
    colCount,cFieldMaxLen,cTypeMaxLen,cNullMaxLen,cKeyMaxLen,cDefaultMaxLen,cExtraMaxLen)    
    SELECT @mySession,t1.tblName,t1.ordVal,t1.cField,t1.cType,t1.cNull,t1.cKey,t1.cDefault,t1.cExtra,
    t2.colCount,t2.cFieldMaxLen,t2.cTypeMaxLen,t2.cNullMaxLen,t2.cKeyMaxLen,t2.cDefaultMaxLen,t2.cExtraMaxLen 
    FROM Reporting101a.tOutput t1
    JOIN Reporting101a.tOutput2 t2
    ON t2.tblName=t1.tblName
    ORDER BY t1.tblName,t1.id;

    DROP TEMPORARY TABLE Reporting101a.tOutput;
    DROP TEMPORARY TABLE Reporting101a.tOutput2;
    SET theSession=@mySession; -- the OUT var that came in as a parameter

    -- ***************************************************************************
    -- ***************************************************************************
    -- Label "Some_Sort_of_Output":
    IF callTheSecondStoredProc=TRUE THEN
        -- The caller says to call the second stored proc (for Pretty Printing)
        -- This will generate output similar to `DESCRIBE myTable`
        -- But remember, it will do it  for EVERY table in referenced database
        CALL Reporting101a.`Print_Tables_Like_Describe`(@mySession);
        -- The above call just gave you output.
    ELSE
        -- The caller chose to not auto call the Pretty Printing second stored procedure.
        -- Note, the caller can easily call it right after using the OUT parameter.
        -- So our output will be a resultset of out reportDataDefs table for this session #
        SELECT * 
        FROM Reporting101a.reportDataDefs 
        WHERE sessionId=@mySession
        ORDER BY tblName,ordVal;
    END IF;
    -- ***************************************************************************
    -- ***************************************************************************

    IF deleteSessionRows=TRUE THEN
        -- The caller says output rows are NOT needed at this point. Delete them.
        -- Note, if this boolean comes in TRUE, you can't call Pretty Printing
        -- second stored procedure with the session # because the data is gone.
        --
        -- Regardless, you are getting something back from "Some_Sort_of_Output" above.
        DELETE FROM Reporting101a.reportDataDefs
        WHERE sessionId=@mySession;
    END IF;
END$$
DELIMITER ;

DROP PROCEDURE IF EXISTS `Reporting101a`.`Print_Tables_Like_Describe`;
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `Reporting101a`.`Print_Tables_Like_Describe`(
    pSessionId INT
)
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE curTable VARCHAR(100) DEFAULT '';
    DECLARE bFirst BOOL DEFAULT TRUE;

    DECLARE lv_tblName,lv_cField,lv_cType,lv_cNull,lv_cKey,lv_cDefault,lv_cExtra VARCHAR(100);
    DECLARE lv_ordVal,lv_colCount,lv_cFieldMaxLen,lv_cTypeMaxLen,lv_cNullMaxLen,lv_cKeyMaxLen,lv_cDefaultMaxLen,lv_cExtraMaxLen INT;

    DECLARE cur1 CURSOR FOR SELECT tblName,ordVal,cField,cType,cNull,cKey,cDefault,cExtra,
    colCount,cFieldMaxLen,cTypeMaxLen,cNullMaxLen,cKeyMaxLen,cDefaultMaxLen,cExtraMaxLen 
    FROM Reporting101a.reportDataDefs
    WHERE sessionId=pSessionId
    ORDER BY tblName,ordVal;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    -- Please note in the above, CURSOR stuff must come last else "Error 1337: Variable or condition decl aft curs" 

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

    -- INSERT Reporting101a.reportOutput(sessionId,lineOut)
    -- SELECT 
    -- SET curTable='';
    DELETE FROM Reporting101a.reportOutput
    WHERE sessionId=pSessionId;

    OPEN cur1;

    read_loop: LOOP
        FETCH cur1 INTO lv_tblName,lv_ordVal,lv_cField,lv_cType,lv_cNull,lv_cKey,lv_cDefault,lv_cExtra,
            lv_colCount,lv_cFieldMaxLen,lv_cTypeMaxLen,lv_cNullMaxLen,lv_cKeyMaxLen,lv_cDefaultMaxLen,lv_cExtraMaxLen ;
        IF done THEN
            LEAVE read_loop;
        END IF;
        IF lv_tblName<>curTable THEN
            IF bFirst=FALSE THEN
                INSERT Reporting101a.reportOutput(sessionId,lineOut)
                SELECT pSessionId,'';
            ELSE
                SET bFirst=FALSE;
            END IF;
            INSERT Reporting101a.reportOutput(sessionId,lineOut)
            SELECT pSessionId,lv_tblName;
            INSERT Reporting101a.reportOutput(sessionId,lineOut)
            SELECT pSessionId,CONCAT('+-', 
                REPEAT('-',GREATEST(5,lv_cFieldMaxLen)),  '-+-',
                REPEAT('-',GREATEST(4,lv_cTypeMaxLen)), '-+-',
                REPEAT('-',GREATEST(4,lv_cNullMaxLen)), '-+-',
                REPEAT('-',GREATEST(3,lv_cKeyMaxLen)),  '-+-',
                REPEAT('-',GREATEST(7,lv_cDefaultMaxLen)),  '-+-',
                REPEAT('-',GREATEST(5,lv_cExtraMaxLen)),    '-+');

            SET @dashLineNumRow=LAST_INSERT_ID();

            INSERT Reporting101a.reportOutput(sessionId,lineOut)
            SELECT pSessionId,CONCAT('| ', 
                'Field',
                REPEAT(' ',GREATEST(0,lv_cFieldMaxLen-5)),  ' | ',
                'Type',
                REPEAT(' ',GREATEST(0,lv_cTypeMaxLen-4)),   ' | ',
                'Null',
                REPEAT(' ',GREATEST(0,lv_cNullMaxLen-4)),   ' | ',
                'Key',
                REPEAT(' ',GREATEST(0,lv_cKeyMaxLen-3)),    ' | ',
                'Default',
                REPEAT(' ',GREATEST(0,lv_cDefaultMaxLen-7)),    ' | ',
                'Extra',
                REPEAT(' ',GREATEST(0,lv_cExtraMaxLen-5)),  ' |');

            INSERT Reporting101a.reportOutput(sessionId,lineOut)
            SELECT pSessionId,lineOut
            FROM Reporting101a.reportOutput
            WHERE lineNum=@dashLineNumRow;

            -- SELECT * FROM Reporting101a.reportDataDefs WHERE sessionId=24;
            SET curTable=lv_tblName;
        END IF;
        INSERT Reporting101a.reportOutput(sessionId,lineOut)
        SELECT pSessionId,
            CONCAT('| ', 
            COALESCE(lv_cField,''),
            COALESCE(REPEAT(' ',GREATEST(0,lv_cFieldMaxLen-LENGTH(lv_cField))),''),' | ',
            COALESCE(lv_cType,''),
            COALESCE(REPEAT(' ',GREATEST(0,lv_cTypeMaxLen-LENGTH(lv_cType))),''),' | ',
            COALESCE(lv_cNull,''),
            COALESCE(REPEAT(' ',GREATEST(0,lv_cNullMaxLen-LENGTH(lv_cNull))),''),' | ',
            COALESCE(lv_cKey,'   '),
            COALESCE(REPEAT(' ',GREATEST(0,lv_cKeyMaxLen-LENGTH(lv_cKey))),''),' | ',
            COALESCE(lv_cDefault,'       '),
            COALESCE(REPEAT(' ',GREATEST(0,lv_cDefaultMaxLen-LENGTH(lv_cDefault))),''),' | ',
            COALESCE(lv_cExtra,'     '),
            COALESCE(REPEAT(' ',GREATEST(0,lv_cExtraMaxLen-LENGTH(lv_cExtra))),''),' |');
        INSERT Reporting101a.reportOutput(sessionId,lineOut)
        SELECT pSessionId,lineOut
        FROM Reporting101a.reportOutput
        WHERE lineNum=@dashLineNumRow;
    END LOOP;
    CLOSE cur1;

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

测验

测试:

-- See **Note2**
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.

输出

+--------------------------------------------------------------------------------------------+
|                                                                                    |
+--------------------------------------------------------------------------------------------+
| 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   |     |         |                |                           |
| +------------+---------+------+-----+---------+----------------+                           |
| | grade      | int(11) | NO   |     |         |                |                           |
| +------------+---------+------+-----+---------+----------------+                           |
|                                                                                            |
| student                                                                                    |
| +-----------+--------------+------+-----+---------+----------------+                       |
| | Field     | Type         | Null | Key | Default | Extra          |                       |
| +-----------+--------------+------+-----+---------+----------------+                       |
| | studentId | int(11)      | NO   | PRI |         | auto_increment |                       |
| +-----------+--------------+------+-----+---------+----------------+                       |
| | fullName  | varchar(100) | NO   |     |         |                |                       |
| +-----------+--------------+------+-----+---------+----------------+                       |
|                                                                                            |
| testtable                                                                                  |
| +-----------------------------------------+---------------+------+-----+---------+-------+ |
| | Field                                   | Type          | Null | Key | Default | Extra | |
| +-----------------------------------------+---------------+------+-----+---------+-------+ |
| | noPKhere                                | int(11)       | NO   |     |         |       | |
| +-----------------------------------------+---------------+------+-----+---------+-------+ |
| | veryLongColumnName_And_Then.Some_%_More | decimal(12,2) | YES  |     |         |       | |
| +-----------------------------------------+---------------+------+-----+---------+-------+ |
| | limit                                   | int(11)       | NO   |     |         |       | |
| +-----------------------------------------+---------------+------+-----+---------+-------+ |
|                                                                                            |
| testtable2                                                                                 |
| +-------+---------+------+-----+---------+-------+                                         |
| | Field | Type    | Null | Key | Default | Extra |                                         |
| +-------+---------+------+-----+---------+-------+                                         |
| | id    | int(11) | NO   | PRI |         |       |                                         |
| +-------+---------+------+-----+---------+-------+                                         |
+--------------------------------------------------------------------------------------------+

注释1 :调用的数据库 Reporting101a 创建用于容纳两个存储过程和一些支持表。这些例程通过调用引用数据库的存储过程来启动通过使用字符串。

生成输出的数据通过专用 INFORMATION_SCHEMA 数据库的只读安全方式。因此,不涉及正在报告的数据库。

此数据库中保存有三个非临时表。

reportDataDefsSession -用于获取会话的简单表#
reportDataDefs -数据返回自
reportOutput -类似MySQL的打印打印表 DESCRIBE 它只是一个将输出放在一起的表格。它是基于会话的。

注2 :此 INT 变量包含为 OUT 参数target,写入,并允许在First存储过程准备数据后插入其他代码。它表示一个会话#,用于隔离输出以供以后报告。

一些环境(如PHP)有一些技巧,使其成为一些程序员的绊脚石。因此,如果您需要单独组合这两个存储过程,请这样做(如果您感到困惑,请让我单独进行)。

无论如何,它显示了数据或工作如何链接在一起存储过程调用。

真的我将session#作为out参数的一个主要原因是,我知道必须创建一个CURSOR来获得漂亮的输出。这需要一个 Cursor DECLARE 位于第二个存储过程的顶部。和 DECLARE s必须出现在存储过程的顶部。因此,我束手无策地走了这条路。

注释3

参数:

描述myTable
这个 国际的 OUT(输出) 参数以保持会话#
布尔值:是否希望在末尾从报告表中删除数据
布尔值:我们应该自动调用生成的漂亮打印存储过程吗 describe -类似输出。如果决定将参数4作为 FALSE ,则输出可能如下所示:

注释4 :用于需要不同输出但希望使用会话#的情况。你通常不需要这个。

jibe 8 年前

您只需要删除表输出的第一行和最后一行并替换 + 通过 | 表的降价格式。

如下所示:

|Field|Type|Null|Key|Default|Extra|
|-----|----|----|---|-------|-----|
|blockheight|int(11)|No|PRI|NULL||
...