mysql查询表字段属性

可以通过如下SQL文进行过检索并提取关键信息，如果想要额外的信息可以查看columns表有哪些字段，自己组合

SELECT
	c.ORDINAL_POSITION AS count,
	c.COLUMN_NAME AS fieldName,
	c.COLUMN_TYPE AS fieldType,
	c.COLUMN_COMMENT AS remark,
	CASE c.IS_NULLABLE
		WHEN 'YES' THEN
			'是'
		WHEN 'NO' THEN
			'否'
		ELSE
			'是'
		END AS nullable,
        c.COLUMN_DEFAULT AS defaultValue,
        CASE c.COLUMN_KEY
		WHEN 'PRI' THEN
			'是'
		WHEN 'UNI' THEN
			'是'
		ELSE
			''
		END AS pk
FROM
	information_schema. COLUMNS AS c
WHERE
	table_name = '这里填写表名'
AND table_schema = '这里填写数据库名字'
ORDER BY
	ORDINAL_POSITION;

查询的结果如下：

count	fieldName	fieldType	remark	nullable	defaultValue	pk
1	id	int(11)	主键	否		是
2	name	varchar(10)	姓名	是		否