Implementing Monthly Horizontal Partitioning in MySQL Using Stored Procedures and Dynamic Table Creation

The article begins by describing a special hardware‑driven business that generates an enormous amount of billing records—up to 1.5 million rows per month from about 1,000 cash‑register devices—making a single massive table impractical.

Because the overall user count is low and concurrency modest, a full micro‑service architecture is unnecessary; instead the focus is on solving the data‑storage problem within a single database.

The chosen solution is an advanced form of table partitioning: a single database with horizontal tables created per month (e.g., month_bills_202210 , month_bills_202211 , …). Each table stores the same seven fields: an auto‑increment primary key, serial number, bill details, payment amount, machine serial number, bill date, and optional comment.

Key table details are defined in the following DDL, which creates the first monthly table:

CREATE TABLE `month_bills_202211` (
    `month_bills_id` int(8) NOT NULL AUTO_INCREMENT COMMENT '账单ID',
    `serial_number` varchar(50) NOT NULL COMMENT '流水号',
    `bills_info` text NOT NULL COMMENT '账单详情',
    `pay_money` decimal(10,3) NOT NULL COMMENT '支付金额',
    `machine_serial_no` varchar(20) NOT NULL COMMENT '收银机器',
    `bill_date` timestamp NOT NULL COMMENT '账单日期',
    `bill_comment` varchar(100) NULL DEFAULT '无' COMMENT '账单备注',
    PRIMARY KEY (`month_bills_id`) USING BTREE,
    UNIQUE `serial_number` (`serial_number`),
    KEY `bill_date` (`bill_date`)
) ENGINE=InnoDB CHARACTER SET=utf8 COLLATE=utf8_general_ci ROW_FORMAT=Compact;

To automate monthly table creation, a stored procedure create_table_by_month is defined. It calculates the next month (format YYYYMM ), builds a CREATE TABLE IF NOT EXISTS statement with the same schema, prepares and executes it, and reports whether the table already existed.

DELIMITER //
DROP PROCEDURE IF EXISTS create_table_by_month //
CREATE PROCEDURE `create_table_by_month`()
BEGIN
    DECLARE nextMonth varchar(20);
    DECLARE createTableSQL varchar(5210);
    DECLARE tableCount int;
    DECLARE tableName varchar(20);
    DECLARE table_prefix varchar(20);
    SELECT SUBSTR(REPLACE(DATE_ADD(CURDATE(), INTERVAL 1 MONTH), '-', ''), 1, 6) INTO @nextMonth;
    SET @table_prefix = 'month_bills_';
    SET @tableName = CONCAT(@table_prefix, @nextMonth);
    SET @createTableSQL = CONCAT('create table if not exists ', @tableName, '(...same columns as above...)');
    PREPARE create_stmt FROM @createTableSQL;
    EXECUTE create_stmt;
    DEALLOCATE PREPARE create_stmt;
    SELECT COUNT(1) INTO @tableCount FROM information_schema.`TABLES` WHERE TABLE_NAME = @tableName;
    SELECT @tableCount 'tableCount';
END //
DELIMITER ;

A MySQL event scheduler is then used to run this procedure automatically on the first day of each month, ensuring the next month’s table exists ahead of time.

CREATE EVENT `create_table_by_month_event`
ON SCHEDULE EVERY 1 MONTH
STARTS '2022-11-28 00:00:00'
ON COMPLETION PRESERVE ENABLE
DO CALL create_table_by_month();

On the application side, a SpringBoot + MyBatis monolithic service is built. The entity class MonthBills mirrors the table fields and adds a targetTable property to hold the dynamically chosen table name.

public class MonthBills {
    private Integer monthBillsId;
    private String serialNumber;
    private BigDecimal payMoney;
    private String machineSerialNo;
    private Date billDate;
    private String billsInfo;
    private String billComment;
    private String targetTable; // dynamic table name
    // getters/setters omitted
}

A utility class TableTimeUtils provides methods to format dates as YYYYMM , compute the target table from a bill’s date, extract the month from a serial number, and generate a list of tables for a date range.

public static void getDataByTable(MonthBills mb) {
    String yearMonth = getYearMonth(mb.getBillDate());
    mb.setTargetTable(tablePrefix + yearMonth);
}

The MyBatis mapper uses the ${targetTable} placeholder to direct INSERT, SELECT, UPDATE, and DELETE statements to the appropriate monthly table.

<insert id="insertSelective" parameterType="MonthBills">
    INSERT INTO ${targetTable} (...columns...)
    VALUES (...values...)
</insert>

APIs are exposed via a REST controller:

/bills/settleUp receives bill details, sets the current timestamp, generates a unique serial number (machine ID + timestamp), determines the target table, and inserts the record.

/bills/selectBySerialNumber parses the timestamp embedded in the serial number to locate the correct month table and retrieves the record.

/bills/rangeQueryByTime accepts a start and end date, uses TableTimeUtils.getRangeQuerySQL to build a UNION‑ALL query across all relevant month tables, and returns the combined result set.

For range queries, the utility first builds a list of month tables between the two dates, then constructs SQL that either uses a single BETWEEN clause (if only one table) or a series of SELECT … UNION ALL statements (first table > start, last table < end, middle tables full).

public static String getRangeQuerySQL(String start, String end) {
    List
tables = getRangeQueryByTables(start, end);
    StringBuilder sql = new StringBuilder();
    if (tables.size() == 1) {
        sql.append("select * from ").append(tables.get(0))
           .append(" where bill_date BETWEEN '").append(start)
           .append("' AND '").append(end).append("';");
    } else {
        for (String table : tables) {
            if (table.equals(tables.get(0))) {
                sql.append("select * from ").append(table)
                   .append(" where bill_date > '").append(start).append("' union all ");
            } else if (table.equals(tables.get(tables.size() - 1))) {
                sql.append("select * from ").append(table)
                   .append(" where bill_date < '").append(end).append("';");
            } else {
                sql.append("select * from ").append(table).append(" union all ");
            }
        }
    }
    return sql.toString();
}

The article concludes that monthly horizontal partitioning solves the storage and query performance problems for extremely large billing tables while keeping the architecture simple (single database, single application). It also notes that further scaling could involve finer‑grained partitions (half‑month, weekly) or full sharding frameworks such as Sharding‑JDBC when needed.