1. mysql提供的表和语句

Mysql所有数据表的字段（COLUMN）信息都保存在information_schema.COLUMNS表中，其定义如下：

表定义：

CREATE TEMPORARY TABLE `COLUMNS` (
  `TABLE_CATALOG` varchar(512) NOT NULL DEFAULT '',
  `TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '',
  `TABLE_NAME` varchar(64) NOT NULL DEFAULT '',
  `COLUMN_NAME` varchar(64) NOT NULL DEFAULT '',
  `ORDINAL_POSITION` bigint(21) unsigned NOT NULL DEFAULT '0',
  `COLUMN_DEFAULT` longtext,
  `IS_NULLABLE` varchar(3) NOT NULL DEFAULT '',
  `DATA_TYPE` varchar(64) NOT NULL DEFAULT '',
  `CHARACTER_MAXIMUM_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `CHARACTER_OCTET_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `NUMERIC_PRECISION` bigint(21) unsigned DEFAULT NULL,
  `NUMERIC_SCALE` bigint(21) unsigned DEFAULT NULL,
  `DATETIME_PRECISION` bigint(21) unsigned DEFAULT NULL,
  `CHARACTER_SET_NAME` varchar(32) DEFAULT NULL,
  `COLLATION_NAME` varchar(32) DEFAULT NULL,
  `COLUMN_TYPE` longtext NOT NULL,
  `COLUMN_KEY` varchar(3) NOT NULL DEFAULT '',
  `EXTRA` varchar(30) NOT NULL DEFAULT '',
  `PRIVILEGES` varchar(80) NOT NULL DEFAULT '',
  `COLUMN_COMMENT` varchar(1024) NOT NULL DEFAULT '',
  `GENERATION_EXPRESSION` longtext NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

• 可以通过如下的sql语句去读取某个mysql某个数据库下某个表的字段名：

并且按照表结构的顺序排序。

SELECT
    COLUMN_NAME 
FROM
    INFORMATION_SCHEMA.COLUMNS 
WHERE
    TABLE_SCHEMA = 数据库名 
    AND TABLE_NAME = 表明
ORDER BY
    ORDINAL_POSITION ASC;

• 查询MySQL数据库中所有表的索引

select TABLE_NAME, INDEX_NAME, GROUP_CONCAT(COLUMN_NAME) as INDEX_COLUMN
from
information_schema.statistics
where
table_schema='库名'
GROUP BY TABLE_NAME, INDEX_NAME;

2. JAVA代码读取

public class TestDbConnection {

    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        Connection connection = null;
        //创建连接
        try {
            String dbName = "test_db";
            String tableName = "test_table1";

            //创建url-数据库为：INFORMATION_SCHEMA
            String url = "jdbc:mysql://localhost:3306/INFORMATION_SCHEMA?useUnicode=true&characterEncoding=UTF-8&useSSL=false";
            //获取连接
            connection = DriverManager.getConnection(url, "root", "123qwe");
            Statement statement = connection.createStatement();
            //构建sql
            String sql = "select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA='"
                    + dbName + "' and TABLE_NAME='" + tableName + "' order by ORDINAL_POSITION asc;";
            //执行sql
            ResultSet resultSet = statement.executeQuery(sql);
            List<String> buf = new ArrayList<>();
            //读取到List中
            while (resultSet.next()) {
                buf.add(resultSet.getString(1));
            }
            //打印数据
            System.out.println(JSON.toJSONString(buf));
        } finally {
            if (connection != null) {
                connection.close();
            }
        }
    }
}

注意：需要引入mysql的驱动包

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.35</version>
        </dependency>