英文:
How create table automatically with liquibase
问题
我需要在数据库中为每个月创建单独的表，每个表的名称应包含年份和月份，例如：
table_name_2023_1,
table_name_2023_2,
table_name_2023_3,
....
table_name_2023_12
我需要根据需求创建这样的表，每年初，应创建整年的表。我可以使用Liquibase实现这个吗？
英文:
I need to create separate tables in DB for every month, each table should has year and month in its name, like:
table_name_2023_1,
table_name_2023_2,
table_name_2023_3,
....
table_name_2023_12
Such table I need by demand, at the beginning of each year, table should be created for full year.
Can I achieve this with liquibase?
答案1
得分: 1
Liquibase默认不支持此功能，但您可以实现一个CustomChange来封装此逻辑。 https://docs.liquibase.com/change-types/custom-change.html
或者您可以将逻辑移到Liquibase之外并使用属性。 https://docs.liquibase.com/concepts/changelogs/property-substitution.html
英文:
Liquibase doesn't do this out of the box, but you could implement a CustomChange to encapsulate this logic. https://docs.liquibase.com/change-types/custom-change.html
Or you could move the logic outside Liquibase and use a property. https://docs.liquibase.com/concepts/changelogs/property-substitution.html
答案2
得分: 1
在我的情况下，我编写了以下过程：
CREATE PROCEDURE createTablesForPeriod(replacement varchar(64), sqlScript varchar(10000), startDate DATE, monthFromCurrentDate INT)
BEGIN
SET @Start_Date = startDate;
SET @End_Date = DATE_ADD(curdate(), INTERVAL monthFromCurrentDate MONTH);
WHILE(@Start_Date < @End_Date) DO
SET @datePart = DATE_FORMAT(@Start_Date, '%Y_%c');
SET @query = REPLACE(sqlScript, replacement, @datePart);
PREPARE stmt FROM @query;
EXECUTE stmt;
SET @Start_Date = DATE_ADD(@Start_Date, INTERVAL 1 MONTH);
END WHILE;
END
//
我还创建了一个类似的创建表模板：
SET @create_table_query = CONCAT('CREATE TABLE IF
NOT EXISTS `my_table_name_%yyyy_mm%` (
....fields here
) ENGINE=InnoDB DEFAULT CHARSET=utf8;');
然后我使用参数调用它：
CALL `createTablesForPeriod`('%yyyy_mm%', @create_table_query, STR_TO_DATE('1,4,2023','%d,%m,%Y'), 12);
最后，我将最后一个脚本添加到单独的迁移变更集中，并标记为runAlways="true"。
更新：
还可以在Java类中创建一个包含所有这些逻辑的自定义变更任务。
英文:
In my case I wrote procedure:
CREATE PROCEDURE createTablesForPeriod(replacement varchar(64), sqlScript varchar(10000), startDate DATE, monthFromCurrentDate INT)
BEGIN
SET @Start_Date = startDate;
SET @End_Date = DATE_ADD(curdate(), INTERVAL monthFromCurrentDate MONTH);
WHILE(@Start_Date < @End_Date) DO
SET @datePart = DATE_FORMAT(@Start_Date, '%Y_%c');
SET @query = REPLACE(sqlScript, replacement, @datePart);
PREPARE stmt FROM @query;
EXECUTE stmt;
SET @Start_Date = DATE_ADD(@Start_Date, INTERVAL 1 MONTH);
END WHILE;
END
//
And also I created such create table template:
SET @create_table_query = CONCAT('CREATE TABLE IF
NOT EXISTS `my_tabe_name_%yyyy_mm%` (
....fields here
) ENGINE=InnoDB DEFAULT CHARSET=utf8;');
And then I call it with parameters:
CALL `createTablesForPeriod`('%yyyy_mm%', @create_table_query, STR_TO_DATE('1,4,2023','%d,%m,%Y'), 12);
Last script I added in separate migration changeSet with flag runAlways="true"
Update:
Also it is possible to create customChange task with all this logic in java class
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论