如何較方便給上百張資料庫表添加表欄位

2021 年 2 月 23 日
筆記
MySQL

前言

年前和業務部門的研發小夥伴聊天，他說由於之前表設計考慮不周全，導致業務表缺少了一些欄位，他老大就把這個加表欄位的任務給他，咋一聽挺簡單的，不就加些欄位，但小夥伴煩惱的地方在於需要加這些欄位的表大概有100多張，如果單靠手動添加，那效率太低了。於是他問我有沒有啥方法，能比較方便的實現這個需求，今天就來水一下這個話題

需求分析

小夥伴的訴求在於方便的實現，那怎麼實現方便這個訴求。答案的本質就是這個實現不要讓他自己做，讓別人或者其他東西實現他訴求

方案思路

方案一: 把這個需求安排給其他人做，哈哈

方案二:寫存儲過程或者函數

方案三：通過寫sql腳本執行

本文重點講解方案三寫sql腳本執行

如何實現這個sql腳本

實現這個sql腳本的方式有很多種，本文就講下利用java程式生成這個sql腳本。生成這個sql腳本的關鍵在於DatabaseMetaData這個類，這個類是啥？它是java.sql包中的類，利用它可以獲取我們連接到的資料庫的結構、存儲等很多資訊
其更多詳細介紹可以查看如下鏈接
//www.apiref.com/java11-zh/java.sql/java/sql/DatabaseMetaData.html

核心程式碼實現

1、獲取資料庫下的所有資料庫表名

 /**
     * 獲取資料庫下的所有資料庫表名
     *
     * @return Map<String, List<String>> key為資料庫名稱，value為該資料庫下的所有表名
     */
    public Map<String, List<String>> getDbTablesNamesMap(){
        Map<String, List<String>> dbTableNamesMap = new HashMap<>();
        try {
            //獲取資料庫的元數據
            DatabaseMetaData dbMetaData = getConnection().getMetaData();
            //從元數據中獲取到所有的表名
            ResultSet rs = dbMetaData.getTables(null, null, null,new String[] { "TABLE" });
            List<String> tableNames;
            while(rs.next()) {
                String tableName = rs.getString("TABLE_NAME");
                String curTableDbName = rs.getString("TABLE_CAT");
                String tableNameType = rs.getString("TABLE_TYPE");
                //表模式（可能為空）,在oracle中獲取的是命名空間
                String tableNameSchema = rs.getString("TABLE_SCHEM");
                String tableNameRemark = rs.getString("REMARKS");
                System.out.println("表名: " + tableName + "，表所屬資料庫: " + curTableDbName + "，表類型: " + tableNameType + "，表模式: " + tableNameSchema + "，表備註: " + tableNameRemark);
                //跳過mysql自帶的系統庫
                if("sys".equalsIgnoreCase(curTableDbName)){
                    continue;
                }
                if(dbTableNamesMap.containsKey(curTableDbName)){
                    tableNames = dbTableNamesMap.get(curTableDbName);
                }else{
                    tableNames = new ArrayList<>();
                }
                tableNames.add(tableName);
                dbTableNamesMap.put(curTableDbName,tableNames);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        close();
        return dbTableNamesMap;
    }

2、拼湊要執行的sql語句

public static List<String> prepareSqlStatementWithStringFormat(boolean isForFiles) {
		String sql = "alter table %s ADD COLUMN create_time DATETIME COMMENT '創建時間',ADD COLUMN created_by_id BIGINT(20) DEFAULT NULL COMMENT '創建人id', ADD COLUMN update_time DATETIME COMMENT '修改時間', ADD COLUMN last_updated_by_id BIGINT(20) DEFAULT NULL COMMENT '修改人id'";
		List<String> sqlStatements = new ArrayList<>();
		Map<String, List<String>> dbTableNamesMap = DbUtils.INSTANCE.getDbTablesNamesMap();
		dbTableNamesMap.forEach((dbName,tableNames) -> {
			for (String tableName : tableNames) {
                String record = dbName + "." + tableName;
				String sqlStatement = String.format(sql, record);
				//如果是要寫入文件，則每條生成的sql語句，需追加分號
				if(isForFiles){
					sqlStatement = sqlStatement + ";";
				}
				System.out.println(sqlStatement);
				sqlStatements.add(sqlStatement);

			}

		});
		return sqlStatements;

	}

3、將生成的sql語句寫入文件

public static void writeSqlStatement2File(List<String> sqlStatements,String filePath){

		try {
			File file = new File(filePath);
			if(!file.exists()){
				file.createNewFile();
			}
			FileUtil.writeUtf8Lines(sqlStatements,file);
			System.out.println("資料庫腳本寫入"+filePath+"成功");
		} catch (IOException e) {
			e.printStackTrace();
			System.out.println("資料庫腳本寫入"+filePath+"失敗");
		}


	}

總結

小夥伴並沒有採用方案三的實現方法，而是採取了方案一，通過一頓飯的代價，讓我幫他實現了這個需求。最後我幫他實現整體方案是以flyway+java程式實現的sql腳本的方式實現

demo鏈接

//github.com/lyb-geek/springboot-learning/tree/master/springboot-generate-sqlscript

Tags: MySQL