本文共 2484 字，大约阅读时间需要 8 分钟。
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'
转载地址：http://xlwua.baihongyu.com/