mysql中查询表和字段注释

查看所有表的注释

SQL:

SELECT table_name 表名,table_comment 表说明 FROM information_schema.TABLES WHERE table_schema = 'moneta' ORDER BY table_name

结果:

MySQL [moneta]> SELECT table_name 表名,table_comment 表说明 FROM information_schema.TABLES WHERE table_schema = 'moneta' ORDER BY table_name;
+--------------------+--------------------------+
| 表名               | 表说明                   |
+--------------------+--------------------------+
| biz_rule           | 业务规则表               |
| biz_rule_action    | 业务规则动作表           |
| biz_rule_condition | 业务规则条件表           |
| buyer_tag          | 买家标签表               |
| query              | 用户query表              |
| store_rule_group   | 店铺业务规则组表         |
+--------------------+--------------------------+
6 rows in set (0.00 sec)

查询某表字段注释

SQL:

select  COLUMN_NAME 字段名, column_comment 字段说明, column_type 字段类型,column_key 约束 from information_schema.columns 
where table_schema = 'moneta' and table_name = 'query';

MySQL [moneta]> select  COLUMN_NAME 字段名, column_comment 字段说明, column_type 字段类型,column_key 约束 from information_schema.columns
    -> where table_schema = 'moneta' and table_name = 'query';
+----------------+--------------+------------------+--------+
| 字段名         | 字段说明     | 字段类型         | 约束   |
+----------------+--------------+------------------+--------+
| id             | 主键         | int(10) unsigned | PRI    |
| store_id       | 店铺id       | int(11)          | MUL    |
| buyer_nick     | 买家昵称     | varchar(128)     | MUL    |
| assistant_nick | 客服昵称     | varchar(64)      |        |
| query          | query内容    | text             |        |
| query_time     | query时间    | datetime         | MUL    |
| create_time    | 创建时间     | datetime         |        |
| update_time    | 修改时间     | datetime         |        |
+----------------+--------------+------------------+--------+
8 rows in set (0.00 sec)