代码实例

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
    ,mincol
FROM (
    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
    ) c
WHERE table_schema = 'test'