代码实例

SELECT table_schema    ,table_name    ,(        CASE             WHEN ORDINAL_POSITION = mincol                AND ORDINAL_POSITION < maxcol                THEN CONCAT (                        "create  table if not exists"                        ,table_schema                        ,"."                        ,table_name                        ,"(`"                        ,column_name                        ,"` "                        ,COLUMN_TYPE                        ,","                        )            WHEN ORDINAL_POSITION = mincol                AND ORDINAL_POSITION = maxcol                THEN CONCAT (                        "create  table if not exists"                        ,table_schema                        ,"."                        ,table_name                        ,"(`"                        ,column_name                        ,"` "                        ,COLUMN_TYPE                        ,");"                        )            WHEN ORDINAL_POSITION > mincol                AND ORDINAL_POSITION < maxcol                THEN CONCAT (                        "`"                        ,column_name                        ,"` "                        ,COLUMN_TYPE                        ,","                        )            WHEN ORDINAL_POSITION = maxcol                THEN CONCAT (                        "`"                        ,column_name                        ,"` "                        ,COLUMN_TYPE                        ,");"                        )            END        ) AS statement    ,ORDINAL_POSITION    ,maxcol    ,mincolFROM (    SELECT b.table_schema,b.table_name,b.ORDINAL_POSITION,b.column_name,    (case    when column_type = 'timestamp' then 'datetime'    when column_type = 'bit(1)' then 'int(1)'    else        column_type    end ) AS column_type        ,a.maxcol        ,a.mincol    FROM (        SELECT table_schema            ,table_name            ,max(ORDINAL_POSITION) maxcol            ,min(ORDINAL_POSITION) mincol        FROM information_schema.COLUMNS        GROUP BY table_schema            ,table_name        ) a    JOIN (        SELECT table_schema            ,table_name            ,ORDINAL_POSITION            ,column_name            ,COLUMN_TYPE        FROM information_schema.COLUMNS        ORDER BY table_schema            ,table_name            ,ORDINAL_POSITION ASC        ) b ON a.table_schema = b.table_schema        AND a.table_name = b.table_name    ) cWHERE table_schema = 'test'