Mysql如何查询表内都有哪些字段

Question

按照表内设计的顺序排列，我用select COLUMN_NAME from information_schema.columns where table_name = '表名' and table_schema = '数据库名'在不同数据库内查出来字段排序不同，一个就按照字段设计排序，一个就是按照先按字母大小写再中文。

Answer 1

啥也不说了，直接上代码

select upper(TABLE_NAME) as TABLE_NAME,
       upper(COLUMN_NAME) as ename,
       ORDINAL_POSITION as id,
       case
         when LOCATE('__', COLUMN_COMMENT) = 0 then
          COLUMN_COMMENT
         else
          left(COLUMN_COMMENT, LOCATE('__', COLUMN_COMMENT) - 1)
       END as cname,
       upper(data_type) as types,
       case
         when IS_NULLABLE = 'YES' then
          'Y'
         else
          'N'
       end as isnullable,
       case
         WHEN data_type = 'int' then
          '20'
         else
          CHARACTER_MAXIMUM_LENGTH
       end as LENGTH
  from information_schema.COLUMNS
 where TABLE_SCHEMA = '数据库名字'
   and TABLE_NAME = '表名'
 ORDER BY ID desc;