ID自增和mybatis相关
参考: https://blog.csdn.net/u010379996/article/details/113859626
shardingsphere4配置
官网源码:https://github.com/apache/shardingsphere/
文档:https://shardingsphere.apache.org/document/legacy/4.x/document/cn/overview/
sql
CREATE TABLE `t_test_1` ( `id` int(11) NOT NULL, `name` varchar(20) CHARACTER SET latin1 DEFAULT NULL, `type` tinyint(3) DEFAULT '0', `status` tinyint(3) DEFAULT '0', `update_time` datetime DEFAULT NULL, `create_time` datetime DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
pom.xml
<shardingsphere.version>4.1.0</shardingsphere.version> <dependency> <groupId>org.apache.shardingsphere</groupId> <artifactId>sharding-jdbc-spring-boot-starter</artifactId> <version>${shardingsphere.version}</version> </dependency>
配置文件(application-sharding-databases.properties)
## test默认数据源 ds-0,ds-1分片数据源 spring.shardingsphere.datasource.names=test,ds-0,ds-1 spring.shardingsphere.datasource.test.type=com.zaxxer.hikari.HikariDataSource spring.shardingsphere.datasource.test.driver-class-name=com.mysql.jdbc.Driver spring.shardingsphere.datasource.test.jdbc-url=jdbc:mysql://ip:3306/test spring.shardingsphere.datasource.test.username=root spring.shardingsphere.datasource.test.password= spring.shardingsphere.datasource.ds-0.type=com.zaxxer.hikari.HikariDataSource spring.shardingsphere.datasource.ds-0.driver-class-name=com.mysql.jdbc.Driver spring.shardingsphere.datasource.ds-0.jdbc-url=jdbc:mysql://ip:3306/demo_ds_0 spring.shardingsphere.datasource.ds-0.username=root spring.shardingsphere.datasource.ds-0.password= spring.shardingsphere.datasource.ds-1.type=com.zaxxer.hikari.HikariDataSource spring.shardingsphere.datasource.ds-1.driver-class-name=com.mysql.jdbc.Driver spring.shardingsphere.datasource.ds-1.jdbc-url=jdbc:mysql://ip:3306/demo_ds_1 spring.shardingsphere.datasource.ds-1.username=root spring.shardingsphere.datasource.ds-1.password= spring.shardingsphere.sharding.default-data-source-name=test ## 分库分表 spring.shardingsphere.sharding.tables.t_test.database-strategy.inline.sharding-column=type spring.shardingsphere.sharding.tables.t_test.database-strategy.inline.algorithm-expression=ds-$->{type % 2} spring.shardingsphere.sharding.tables.t_test.table-strategy.inline.sharding-column=status spring.shardingsphere.sharding.tables.t_test.table-strategy.inline.algorithm-expression=t_test_$->{status % 2} spring.shardingsphere.sharding.binding-tables=t_test spring.shardingsphere.sharding.tables.t_test.actual-data-nodes=ds-$->{0..1}.t_test_${0..1} spring.shardingsphere.sharding.tables.t_test.key-generator.column=id spring.shardingsphere.sharding.tables.t_test.key-generator.type=SNOWFLAKE spring.shardingsphere.sharding.tables.t_test.key-generator.props.worker.id=123 ## 打印SQL spring.shardingsphere.props.sql.show=true
t_test
@Data @TableName("t_test") public class TTestModel implements Serializable { private static final long serialVersionUID = 1L; /** id */ @TableId(type = IdType.NONE) @SequenceId(interval = 10) private Integer id; private String name; private Integer type; private Integer status; private Date updateTime; private Date createTime; } @RestController @RefreshScope public class TestController { @Autowired private ITTestService ttestService; @GetMapping("/ttest/add") public Object add() { TTestModel model = new TTestModel(); model.setName("name"); model.setType(new Random().nextInt(10)); model.setStatus(new Random().nextInt(10)); model.setUpdateTime(new Date()); model.setCreateTime(new Date()); return ResultModel.ok(ttestService.save(model)); } @GetMapping("/ttest/list") public Object list() { Map<String, Object> map = Maps.newHashMap(); return ResultModel.ok(ttestService.findListByMap(map)); } }
验证
2021-03-02 09:49:16.884 INFO 33456 --- [nio-9696-exec-2] c.p.config.interceptor.LogInterceptor : /ttest/add 2021-03-02 09:49:17.008 DEBUG 33456 --- [nio-9696-exec-2] c.p.dao.TestSequenceIdMapper.selectOne : ==> Preparing: SELECT id,name,sequence_id,update_time,create_time FROM test_sequence_id WHERE name=? 2021-03-02 09:49:17.028 DEBUG 33456 --- [nio-9696-exec-2] c.p.dao.TestSequenceIdMapper.selectOne : ==> Parameters: t_test(String) 2021-03-02 09:49:17.605 INFO 33456 --- [nio-9696-exec-2] ShardingSphere-SQL : Logic SQL: SELECT id,name,sequence_id,update_time,create_time FROM test_sequence_id WHERE name=? 2021-03-02 09:49:17.605 INFO 33456 --- [nio-9696-exec-2] ShardingSphere-SQL : SQLStatement: SelectStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@20841be6, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@9b075c9), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@9b075c9, projectionsContext=ProjectionsContext(startIndex=8, stopIndex=50, distinctRow=false, projections=[ColumnProjection(owner=null, name=id, alias=Optional.empty), ColumnProjection(owner=null, name=name, alias=Optional.empty), ColumnProjection(owner=null, name=sequence_id, alias=Optional.empty), ColumnProjection(owner=null, name=update_time, alias=Optional.empty), ColumnProjection(owner=null, name=create_time, alias=Optional.empty)]), groupByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.groupby.GroupByContext@477f53a0, orderByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.orderby.OrderByContext@4730ab53, paginationContext=org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.PaginationContext@476bc378, containsSubquery=false) 2021-03-02 09:49:17.605 INFO 33456 --- [nio-9696-exec-2] ShardingSphere-SQL : Actual SQL: test ::: SELECT id,name,sequence_id,update_time,create_time FROM test_sequence_id WHERE name=? ::: [t_test] 2021-03-02 09:49:17.654 DEBUG 33456 --- [nio-9696-exec-2] c.p.dao.TestSequenceIdMapper.selectOne : <== Total: 1 2021-03-02 09:49:17.659 DEBUG 33456 --- [nio-9696-exec-2] c.p.dao.TestSequenceIdMapper.updateById : ==> Preparing: UPDATE test_sequence_id SET name=?, sequence_id=?, update_time=?, create_time=? WHERE id=? 2021-03-02 09:49:17.660 DEBUG 33456 --- [nio-9696-exec-2] c.p.dao.TestSequenceIdMapper.updateById : ==> Parameters: t_test(String), 61(Long), 2021-03-02 09:49:17.658(Timestamp), 2021-02-26 22:00:14.0(Timestamp), 2(Integer) 2021-03-02 09:49:17.667 INFO 33456 --- [nio-9696-exec-2] ShardingSphere-SQL : Logic SQL: UPDATE test_sequence_id SET name=?, sequence_id=?, update_time=?, create_time=? WHERE id=? 2021-03-02 09:49:17.667 INFO 33456 --- [nio-9696-exec-2] ShardingSphere-SQL : SQLStatement: UpdateStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.UpdateStatement@76307d1, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@7115f9f6), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@7115f9f6) 2021-03-02 09:49:17.667 INFO 33456 --- [nio-9696-exec-2] ShardingSphere-SQL : Actual SQL: test ::: UPDATE test_sequence_id SET name=?, sequence_id=?, update_time=?, create_time=? WHERE id=? ::: [t_test, 61, 2021-03-02 09:49:17.658, 2021-02-26 22:00:14.0, 2] 2021-03-02 09:49:17.673 DEBUG 33456 --- [nio-9696-exec-2] c.p.dao.TestSequenceIdMapper.updateById : <== Updates: 1 2021-03-02 09:49:17.679 DEBUG 33456 --- [nio-9696-exec-2] com.project.dao.TTestMapper.insert : ==> Preparing: INSERT INTO t_test ( id, name, type, status, update_time, create_time ) VALUES ( ?, ?, ?, ?, ?, ? ) 2021-03-02 09:49:17.679 DEBUG 33456 --- [nio-9696-exec-2] com.project.dao.TTestMapper.insert : ==> Parameters: 51(Integer), name(String), 9(Integer), 0(Integer), 2021-03-02 09:49:16.896(Timestamp), 2021-03-02 09:49:16.896(Timestamp) 2021-03-02 09:49:17.771 INFO 33456 --- [nio-9696-exec-2] ShardingSphere-SQL : Logic SQL: INSERT INTO t_test ( id, name, type, status, update_time, create_time ) VALUES ( ?, ?, ?, ?, ?, ? ) 2021-03-02 09:49:17.771 INFO 33456 --- [nio-9696-exec-2] ShardingSphere-SQL : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@2955334f, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@7403cdfa), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@7403cdfa, columnNames=[id, name, type, status, update_time, create_time], insertValueContexts=[InsertValueContext(parametersCount=6, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=84, stopIndex=84, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=87, stopIndex=87, parameterMarkerIndex=1), ParameterMarkerExpressionSegment(startIndex=90, stopIndex=90, parameterMarkerIndex=2), ParameterMarkerExpressionSegment(startIndex=93, stopIndex=93, parameterMarkerIndex=3), ParameterMarkerExpressionSegment(startIndex=96, stopIndex=96, parameterMarkerIndex=4), ParameterMarkerExpressionSegment(startIndex=99, stopIndex=99, parameterMarkerIndex=5)], parameters=[51, name, 9, 0, 2021-03-02 09:49:16.896, 2021-03-02 09:49:16.896])], generatedKeyContext=Optional[GeneratedKeyContext(columnName=id, generated=false, generatedValues=[51])]) 2021-03-02 09:49:17.772 INFO 33456 --- [nio-9696-exec-2] ShardingSphere-SQL : Actual SQL: ds-1 ::: INSERT INTO t_test_0 ( id, name, type, status, update_time, create_time ) VALUES (?, ?, ?, ?, ?, ?) ::: [51, name, 9, 0, 2021-03-02 09:49:16.896, 2021-03-02 09:49:16.896] 2021-03-02 09:49:17.787 DEBUG 33456 --- [nio-9696-exec-2] com.project.dao.TTestMapper.insert : <== Updates: 1 2021-03-02 09:49:21.059 INFO 33456 --- [nio-9696-exec-3] c.p.config.interceptor.LogInterceptor : /ttest/list 2021-03-02 09:49:21.064 DEBUG 33456 --- [nio-9696-exec-3] com.project.dao.TTestMapper.findByMap : ==> Preparing: select * from t_test t where 1=1 2021-03-02 09:49:21.064 DEBUG 33456 --- [nio-9696-exec-3] com.project.dao.TTestMapper.findByMap : ==> Parameters: 2021-03-02 09:49:21.076 INFO 33456 --- [nio-9696-exec-3] ShardingSphere-SQL : Logic SQL: select * from t_test t where 1=1 2021-03-02 09:49:21.076 INFO 33456 --- [nio-9696-exec-3] ShardingSphere-SQL : SQLStatement: SelectStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@5dd51fbb, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@7bd6cbce), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@7bd6cbce, projectionsContext=ProjectionsContext(startIndex=7, stopIndex=7, distinctRow=false, projections=[ShorthandProjection(owner=Optional.empty, actualColumns=[ColumnProjection(owner=null, name=id, alias=Optional.empty), ColumnProjection(owner=null, name=name, alias=Optional.empty), ColumnProjection(owner=null, name=type, alias=Optional.empty), ColumnProjection(owner=null, name=status, alias=Optional.empty), ColumnProjection(owner=null, name=update_time, alias=Optional.empty), ColumnProjection(owner=null, name=create_time, alias=Optional.empty)])]), groupByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.groupby.GroupByContext@43ad9bef, orderByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.orderby.OrderByContext@4d6a1f92, paginationContext=org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.PaginationContext@3837f943, containsSubquery=false) 2021-03-02 09:49:21.076 INFO 33456 --- [nio-9696-exec-3] ShardingSphere-SQL : Actual SQL: ds-0 ::: select * from t_test_0 t where 1=1 2021-03-02 09:49:21.076 INFO 33456 --- [nio-9696-exec-3] ShardingSphere-SQL : Actual SQL: ds-0 ::: select * from t_test_1 t where 1=1 2021-03-02 09:49:21.076 INFO 33456 --- [nio-9696-exec-3] ShardingSphere-SQL : Actual SQL: ds-1 ::: select * from t_test_0 t where 1=1 2021-03-02 09:49:21.076 INFO 33456 --- [nio-9696-exec-3] ShardingSphere-SQL : Actual SQL: ds-1 ::: select * from t_test_1 t where 1=1 2021-03-02 09:49:21.124 DEBUG 33456 --- [nio-9696-exec-3] com.project.dao.TTestMapper.findByMap : <== Total: 13