读取mysql表名称_JAVA动态读取mysql表的字段名&&索引

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 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的驱动包

mysql

mysql-connector-java

5.1.35