Implementing Monthly Sharding with MySQL Stored Procedures and SpringBoot‑MyBatis

1. Background and Challenges

The author introduces a real‑world billing system that originates from a hardware device similar to a supermarket cash register. Although the system serves only about 1,200 users, each device generates 500‑600 transaction records per day, leading to 500,000 new rows per day and up to 1.5 million rows per month. Over several years this volume can reach billions of rows, making a single large table impractical for storage, query performance, and maintenance.

1.1 Business Scenario

Each cash‑register device runs a GUI application that calls backend APIs. The backend is a monolithic SpringBoot project using MyBatis for data access. The core requirement is to store billing records efficiently while supporting high insert rates and later queries by date or serial number.

1.2 Difficulty Analysis

Because only the billing table grows massive, splitting the database (multiple databases) would waste resources. Traditional monthly partitioning on a single table would still leave tens of millions of rows per partition, degrading query speed. Therefore the solution is to create separate tables for each month (horizontal sharding within one database).

2. Monthly Table Sharding Implementation

2.1 Creating Tables Dynamically with a Stored Procedure

The following DDL creates a monthly billing table (example for November 2022):

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 DEFAULT now() 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;

A stored procedure create_table_by_month calculates the next month (format YYYYMM ) and builds a CREATE TABLE statement for that month. It then prepares and executes the statement, finally reporting 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, "(\n",
        "    `month_bills_id` int(8) NOT NULL AUTO_INCREMENT COMMENT '账单ID',\n",
        "    `serial_number` varchar(50) NOT NULL COMMENT '流水号',\n",
        "    `bills_info` text NOT NULL COMMENT '账单详情',\n",
        "    `pay_money` decimal(10,3) NOT NULL COMMENT '支付金额',\n",
        "    `machine_serial_no` varchar(20) NOT NULL COMMENT '收银机器',\n",
        "    `bill_date` timestamp NOT NULL DEFAULT now() COMMENT '账单日期',\n",
        "    `bill_comment` varchar(100) NULL DEFAULT '无' COMMENT '账单备注',\n",
        "    PRIMARY KEY (`month_bills_id`) USING BTREE,\n",
        "    UNIQUE `serial_number` (`serial_number`),\n",
        "    KEY `bill_date` (`bill_date`)\n",
        ") ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;"
    );
    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 schedules this procedure to run every month, automatically creating the next month’s table 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();

2.2 Inserting Data into the Correct Monthly Table

The Java entity MonthBills adds a targetTable field that stores the name of the table to operate on.

public class MonthBills {
    private Integer monthBillsId;
    private String serialNumber;
    private BigDecimal payMoney;
    private String machineSerialNo;
    private Date billDate;
    private String billsInfo;
    private String billComment;
    // The table that will receive this record
    private String targetTable;
    // getters and setters omitted
}

A utility class formats dates to YYYYMM and builds the full table name.

public class TableTimeUtils {
    private static ThreadLocal
monthTL = ThreadLocal.withInitial(() -> new SimpleDateFormat("YYYYMM"));
    private static String tablePrefix = "month_bills_";

    public static String getYearMonth(Date date) {
        return monthTL.get().format(date);
    }

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

The MyBatis mapper uses the dynamic table name:

<insert id="insertSelective" parameterType="com.example.MonthBills">
    insert into ${targetTable}
    <trim prefix="(" suffix=")" suffixOverrides=",">
        <if test="monthBillsId != null">month_bills_id,</if>
        <if test="serialNumber != null">serial_number,</if>
        <if test="payMoney != null">pay_money,</if>
        <if test="machineSerialNo != null">machine_serial_no,</if>
        <if test="billDate != null">bill_date,</if>
        <if test="billComment != null">bill_comment,</if>
        <if test="billsInfo != null">bills_info,</if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides=",">
        <if test="monthBillsId != null">#{monthBillsId},</if>
        <if test="serialNumber != null">#{serialNumber},</if>
        <if test="payMoney != null">#{payMoney},</if>
        <if test="machineSerialNo != null">#{machineSerialNo},</if>
        <if test="billDate != null">#{billDate},</if>
        <if test="billComment != null">#{billComment},</if>
        <if test="billsInfo != null">#{billsInfo},</if>
    </trim>
</insert>

The service layer determines the target table before calling the mapper:

@Service
public class MonthBillsServiceImpl implements IMonthBillsService {
    @Autowired
    private MonthBillsMapper billsMapper;

    @Override
    public int insert(MonthBills monthBills) {
        TableTimeUtils.getDataByTable(monthBills);
        return billsMapper.insertSelective(monthBills);
    }
}

The REST controller sets the current timestamp, generates a unique serial number (machine ID + current milliseconds), and delegates to the service.

@RestController
@RequestMapping("/bills")
public class MonthBillsAPI {
    @Autowired
    private IMonthBillsService billsService;

    @RequestMapping("/settleUp")
    public String settleUp(MonthBills monthBills) {
        monthBills.setBillDate(new Date());
        monthBills.setSerialNumber(monthBills.getMachineSerialNo() + System.currentTimeMillis());
        return billsService.insert(monthBills) > 0 ? ">>>>账单结算成功<<<<" : ">>>>账单结算失败<<<<";
    }
}

2.3 Querying by Serial Number

The serial number contains the timestamp, so the utility can extract the month and set targetTable accordingly.

public static void getTableBySerialNumber(MonthBills monthBills) {
    String timeMillis = monthBills.getSerialNumber().substring(monthBills.getSerialNumber().length() - 13);
    long millis = Long.parseLong(timeMillis);
    String yearMonth = getYearMonth(new Date(millis));
    monthBills.setTargetTable(tablePrefix + yearMonth);
}

The mapper provides a dynamic SELECT:

<select id="selectBySerialNumber" resultMap="ResultMapMonthBills" parameterType="com.example.MonthBills">
    select * from ${targetTable} where serial_number = #{serialNumber}
</select>

The service and controller simply call this method after determining the correct table.

2.4 Range Queries Across Multiple Monthly Tables

Two helper methods generate the list of tables between a start and end month and build a UNION‑ALL SQL statement that respects the boundaries of the first and last tables.

public static List
getRangeQueryByTables(String startTime, String endTime) {
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM");
    List
tables = new ArrayList<>();
    try {
        Date startDate = sdf.parse(startTime);
        Date endDate = sdf.parse(endTime);
        Calendar cal = Calendar.getInstance();
        while (!startDate.after(endDate)) {
            tables.add(tablePrefix + monthTL.get().format(startDate));
            cal.setTime(startDate);
            cal.add(Calendar.MONTH, 1);
            startDate = cal.getTime();
        }
    } catch (ParseException e) { e.printStackTrace(); }
    return tables;
}

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

The mapper executes the generated SQL and returns a list of MonthBills objects.

<select id="rangeQueryByDate" resultMap="ResultMapMonthBills" parameterType="java.lang.String">
    ${sql}
</select>

The REST endpoint accepts start and end parameters (format yyyy-MM-dd ) and returns the matching records.

@RequestMapping("/rangeQueryByTime")
public String rangeQueryByTime(@RequestParam("start") String start,
                               @RequestParam("end") String end) {
    List
bills = billsService.rangeQueryByDate(start, end);
    return bills != null ? bills.toString() : ">>>>指定的日期中没有账单数据<<<<";
}

3. Summary of In‑Database Sharding

The article demonstrates a complete workflow for monthly horizontal sharding within a single MySQL instance: automatic table creation via stored procedures and events, dynamic routing of inserts/updates/selects using a targetTable field, and strategies for querying by serial number or date range across many tables. While this approach works well for a single large table, larger scale systems may adopt finer‑grained sharding (weekly, daily) or external sharding frameworks such as Sharding‑JDBC.

Source: https://juejin.cn/post/7169098754719481864