php mysql查询表的各字段名以及备注

业务场景：可以通过程序实现动态的数据字典的功能。

原理就是页面所用的表，查询出表各字段的名字以及备注

SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT, COLUMN_COMMENT from information_schema.columns where table_schema = '数据库名' and table_name='表名';

 方法二

/**
 * 取得数据表的字段信息
 * @access public
 * @return array
 */
public function getFields($tableName) {
    $result = $this->query('SHOW FULL COLUMNS FROM ' . $tableName);
    $columns = [];
    foreach ($result as $val) {
        $val = (array) $val;
        $columns[$val['Field']] = [
            'name' => $val['Field'],
            'type' => $val['Type'],
            'notnull' => (bool) ('' === $val['Null']), // not null is empty, null is yes
            'default' => $val['Default'],
            'comment' => $val['Comment'],
            'primary' => ('pri' == strtolower($val['Key'])),
            'autoinc' => ('auto_increment' == strtolower($val['Extra'])),
        ];
    }

    return $columns;
}

insert into table value(#字段列表#) select #字段列表# from table1

select GROUP_CONCAT(COLUMN_NAME) '字段列表' from information_schema.COLUMNS where table_name = '表名' and table_schema = '数据库名' and COLUMN_NAME<>'id';