MySQL 之information_schema

查看a库的所有表结构：

mysql> SELECT table_name,COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT,COLUMN_COMMENT
            FROM INFORMATION_SCHEMA.COLUMNS WHERE
         table_schema ='a';

+----------------------------------+---------------------+-----------+-------------+----------------+----------------+
| table_name                       | COLUMN_NAME         | DATA_TYPE | IS_NULLABLE | COLUMN_DEFAULT | COLUMN_COMMENT |
+----------------------------------+---------------------+-----------+-------------+----------------+----------------+
| admin_config                     | id                  | int       | NO          | NULL           |                |
| admin_config                     | key                 | varchar   | YES         | NULL           |                |
| admin_config                     | fkey                | varchar   | NO          | NULL           |                |
| admin_config                     | value               | varchar   | NO          | NULL           |                |
| admin_config                     | content             | varchar   | YES         | NULL           |                |
| admin_config                     | time_create         | int       | NO          | NULL           |                |
| admin_config                     | status              | int       | NO          | NULL           |                |
| admin_config                     | remark              | varchar   | YES         | NULL           |                |
| agent                            | id                  | int       | NO          | NULL           |                |
| agent                            | sid                 | int       | NO          | NULL           |                |
| agent                            | tid                 | int       | NO          | NULL           |                |
| agent                            | agent_start         | int       | YES         | NULL           |                |
| agent                            | agent_end           | int       | YES         | NULL           |                |
| agent                            | is_agent            | int       | YES         | NULL           |                |
| agent                            | designated_task     | varchar   | YES         | NULL           |                |
| agent                            | remark              | varchar   | YES         | NULL           |                |

 

查看库a的所有索引：

mysql> SELECT table_name,non_unique,index_name,column_name,seq_in_index
            FROM information_schema.statistics WHERE
          table_schema ='a';

 

+----------------------------------+------------+-----------------------------------------------------+-------------------+--------------+
| table_name                       | non_unique | index_name                                          | column_name       | seq_in_index |
+----------------------------------+------------+-----------------------------------------------------+-------------------+--------------+
| auth_user                        |          0 | PRIMARY                                             | id                |            1 |
| auth_user                        |          0 | username                                            | username          |            1 |
| task_list                        |          0 | PRIMARY                                             | id                |            1 |
| task_list                        |          1 | task_list_developer_3534e911c3a3de5_uniq            | group             |            1 |
| task_module_list                 |          0 | PRIMARY                                             | id                |            1 |
| task_module_list                 |          1 | task_module_list_60fb6a05                           | plan_id           |            1 |
| task_module_list                 |          1 | task_module_list_developer_55f7170e6621e545_uniq    | developer         |            1 |
| task_module_list                 |          1 | task_module_list_developer_512461dc557fcb6e_idx     | developer         |            1 |
| task_module_list                 |          1 | task_module_list_developer_512461dc557fcb6e_idx     | group             |            2 |

查看a库PRIMARY KEY之外的所有索引

mysql> SELECT table_name,non_unique,index_name,column_name,seq_in_index
            FROM information_schema.statistics WHERE
          table_schema ='a' and index_name <>'PRIMARY';

查看a库所有表信息：

mysql> SELECT table_name,ENGINE, TABLE_COMMENT FROM INFORMATION_SCHEMA.TABLES WHERE  table_schema ='a';

+----------------------------------+--------+---------------+
| table_name                       | ENGINE | TABLE_COMMENT |
+----------------------------------+--------+---------------+
| admin_config                     | InnoDB |        配置表       |
| agent                            | MyISAM |           代理表    |