Is there any practice of table sharding?

[GOODDAYDAY]

Hi all!
I notice that the code generated of table item is changeless.
So if I want run a SQL with table like table_1,table_2... is complex that I need to write one function with table item param input or something else.
I'd want to know if anyone has some good practice of table sharding by id or time or anything else.
Regards,
Goody

[GOODDAYDAY]

    default List<Object> select(String table, SelectDSLCompleter completer) {
        return MyBatis3Utils.selectList(this::selectMany, selectList, tableItem.withAlias(table), completer);
    }

this is one example I want to do, but it may be difficult to insert with sharding

[jeffgbutler]

The SqlTable class can accept a Supplier function in the constructor that can change the table name dynamically. That would work for all statements.

It's not so easy to use right now because there's no way to change that function after the table is created, and MyBatis generator doesn't generate code for it.

I'll think about a way to make it easier to use.

[GOODDAYDAY]

Thanks for your reply!
The Supplier function is just good to insert sharding like table_202211xx. When sharding by id, it will be difficult.

type	sharding by id	sharding by time
SELECT	obtain id & muti table	muti table
INSERT	obtain id & auto muti table	Supplier is OK

This table display some point of sharding table. It may provide some help.

[jeffgbutler]

I'm not sure I understand your table. Does "muti table" mean "mutate table" (change the table name)?

I'm thinking of making a change that will make it easier to do something like that. Take a look at this test in my branch: https://github.com/jeffgbutler/mybatis-dynamic-sql/blob/bcf7ed8ded025dc799c5c608bf19db74c44f3b9e/src/test/java/examples/sharding/ShardingTest.java

Especially look at these lines line that will select an instance of the table with a different name based on a sharding algorithm: https://github.com/jeffgbutler/mybatis-dynamic-sql/blob/bcf7ed8ded025dc799c5c608bf19db74c44f3b9e/src/test/java/examples/sharding/ShardingTest.java#L157-L167

Note that this does not change the table object - it creates a new instance with a different name. I'm very committed to immutable objects in the library.

Would a change like this meet your needs?

[GOODDAYDAY]

Very sorry for my late reply. This change is very suit for my needs.

Only one further thing is how to BATCH query or insert with this way?

[jeffgbutler]

@GOODDAYDAY this support is now merged into the library.

For MyBatis batch support, it is important to make sure that the inserts to the various tables are kept together in the list of inserts. You must do this on your own.

[GOODDAYDAY]

OK, Thanks a lot @jeffgbutler