【Java】导出Mysql表表结构与注释数据字典

需求：
把mysql中所有表的字段名、数据类型、长度、注释整理成csv，做成数据字典。

import java.io.IOException;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;/*** @Desc: <p>** </p>* @Author： A Real Man* @Time: 2024/4/12 11:00*/
public class MysqlTest {public static void main(String[] args) throws SQLException {export("testdb");}public static void export(String dbName) throws SQLException {Connection connection = DriverManager.getConnection("jdbc:mysql://ip:port/testdb?serverTimezone=GMT%2B8&characterEncoding=utf-8","username", "password");try {String fgf = "分隔符";DatabaseMetaData metaData = connection.getMetaData();ResultSet tables = metaData.getTables(dbName, null, null, new String[] { "TABLE" });StringBuilder cc = new StringBuilder();while (tables.next()) {String tableName = tables.getString("TABLE_NAME");if(tableName.startsWith("不需要的表名")){continue;}System.out.println("Table: " + tableName);ResultSet columns = metaData.getColumns(null, null, tableName, null);while (columns.next()) {String columnName = columns.getString("COLUMN_NAME");String dataType = columns.getString("TYPE_NAME");int size = columns.getInt("COLUMN_SIZE");String remarks = columns.getString("REMARKS");int digit = columns.getInt("DECIMAL_DIGITS");cc.append(dbName).append(fgf).append(tableName).append(fgf).append(columnName).append(fgf).append(dataType).append(fgf).append(size).append(fgf).append(digit).append(fgf).append(remarks).append("\n");}columns.close();}tables.close();FileTool.newFile("D:/app/mysql/" + dbName + ".txt", cc.toString());} catch (IOException e) {e.printStackTrace();} finally {rconnection.close();}}
}

columns.getString(“REMARKS”) 如果你还需要取别的内容，可以打开java.sql.DatabaseMetaData源码，找到getColumns()方法的注释，里面记录了KEY；

    /*** Retrieves a description of table columns available in* the specified catalog.** <P>Only column descriptions matching the catalog, schema, table* and column name criteria are returned.  They are ordered by* <code>TABLE_CAT</code>,<code>TABLE_SCHEM</code>,* <code>TABLE_NAME</code>, and <code>ORDINAL_POSITION</code>.** <P>Each column description has the following columns:*  <OL>*  <LI><B>TABLE_CAT</B> String {@code =>} table catalog (may be <code>null</code>)*  <LI><B>TABLE_SCHEM</B> String {@code =>} table schema (may be <code>null</code>)*  <LI><B>TABLE_NAME</B> String {@code =>} table name*  <LI><B>COLUMN_NAME</B> String {@code =>} column name*  <LI><B>DATA_TYPE</B> int {@code =>} SQL type from java.sql.Types*  <LI><B>TYPE_NAME</B> String {@code =>} Data source dependent type name,*  for a UDT the type name is fully qualified*  <LI><B>COLUMN_SIZE</B> int {@code =>} column size.*  <LI><B>BUFFER_LENGTH</B> is not used.*  <LI><B>DECIMAL_DIGITS</B> int {@code =>} the number of fractional digits. Null is returned for data types where* DECIMAL_DIGITS is not applicable.*  <LI><B>NUM_PREC_RADIX</B> int {@code =>} Radix (typically either 10 or 2)*  <LI><B>NULLABLE</B> int {@code =>} is NULL allowed.*      <UL>*      <LI> columnNoNulls - might not allow <code>NULL</code> values*      <LI> columnNullable - definitely allows <code>NULL</code> values*      <LI> columnNullableUnknown - nullability unknown*      </UL>*  <LI><B>REMARKS</B> String {@code =>} comment describing column (may be <code>null</code>)*  <LI><B>COLUMN_DEF</B> String {@code =>} default value for the column, which should be interpreted as a string when the value is enclosed in single quotes (may be <code>null</code>)*  <LI><B>SQL_DATA_TYPE</B> int {@code =>} unused*  <LI><B>SQL_DATETIME_SUB</B> int {@code =>} unused*  <LI><B>CHAR_OCTET_LENGTH</B> int {@code =>} for char types the*       maximum number of bytes in the column*  <LI><B>ORDINAL_POSITION</B> int {@code =>} index of column in table*      (starting at 1)*  <LI><B>IS_NULLABLE</B> String  {@code =>} ISO rules are used to determine the nullability for a column.*       <UL>*       <LI> YES           --- if the column can include NULLs*       <LI> NO            --- if the column cannot include NULLs*       <LI> empty string  --- if the nullability for the* column is unknown*       </UL>*  <LI><B>SCOPE_CATALOG</B> String {@code =>} catalog of table that is the scope*      of a reference attribute (<code>null</code> if DATA_TYPE isn't REF)*  <LI><B>SCOPE_SCHEMA</B> String {@code =>} schema of table that is the scope*      of a reference attribute (<code>null</code> if the DATA_TYPE isn't REF)*  <LI><B>SCOPE_TABLE</B> String {@code =>} table name that this the scope*      of a reference attribute (<code>null</code> if the DATA_TYPE isn't REF)*  <LI><B>SOURCE_DATA_TYPE</B> short {@code =>} source type of a distinct type or user-generated*      Ref type, SQL type from java.sql.Types (<code>null</code> if DATA_TYPE*      isn't DISTINCT or user-generated REF)*   <LI><B>IS_AUTOINCREMENT</B> String  {@code =>} Indicates whether this column is auto incremented*       <UL>*       <LI> YES           --- if the column is auto incremented*       <LI> NO            --- if the column is not auto incremented*       <LI> empty string  --- if it cannot be determined whether the column is auto incremented*       </UL>*   <LI><B>IS_GENERATEDCOLUMN</B> String  {@code =>} Indicates whether this is a generated column*       <UL>*       <LI> YES           --- if this a generated column*       <LI> NO            --- if this not a generated column*       <LI> empty string  --- if it cannot be determined whether this is a generated column*       </UL>*  </OL>** <p>The COLUMN_SIZE column specifies the column size for the given column.* For numeric data, this is the maximum precision.  For character data, this is the length in characters.* For datetime datatypes, this is the length in characters of the String representation (assuming the* maximum allowed precision of the fractional seconds component). For binary data, this is the length in bytes.  For the ROWID datatype,* this is the length in bytes. Null is returned for data types where the* column size is not applicable.** @param catalog a catalog name; must match the catalog name as it*        is stored in the database; "" retrieves those without a catalog;*        <code>null</code> means that the catalog name should not be used to narrow*        the search* @param schemaPattern a schema name pattern; must match the schema name*        as it is stored in the database; "" retrieves those without a schema;*        <code>null</code> means that the schema name should not be used to narrow*        the search* @param tableNamePattern a table name pattern; must match the*        table name as it is stored in the database* @param columnNamePattern a column name pattern; must match the column*        name as it is stored in the database* @return <code>ResultSet</code> - each row is a column description* @exception SQLException if a database access error occurs* @see #getSearchStringEscape*/ResultSet getColumns(String catalog, String schemaPattern,String tableNamePattern, String columnNamePattern)throws SQLException;