sharding-jdbc+mybatis-plus 快速實(shí)現(xiàn)分庫分表

在開發(fā)過程中經(jīng)常會遇到數(shù)據(jù)量過大，再除了緩存之外，可以對數(shù)據(jù)庫進(jìn)行分庫分表。本文主要描述快速實(shí)現(xiàn)基于 sharding-jdbc 進(jìn)行分庫分表配置。以及在配置過程中踩到的一些坑陷猫。

版本：
springboot: 2.4.1
mybatis-plus-boot-starter: 2.2.0
sharding-jdbc-spring-boot-starter: 4.1.1
druid: 1.2.4

官網(wǎng)：https://shardingsphere.apache.org/document/current/cn/user-manual/shardingsphere-jdbc/
Demo gitee: https://gitee.com/old_guys/sharding-test-demo

引入依賴
基本配置及描述（多數(shù)據(jù)源，分庫分表）
JOIN 關(guān)聯(lián)查詢

采坑：

引入 druid-spring-boot-starter （使用這個(gè)會報(bào)錯(cuò)，直接使用druid依賴就不會）
如果有多個(gè)數(shù)據(jù)源绣檬，但是不配置（配置失效）的時(shí)候足陨，就會隨機(jī)獲取一個(gè)數(shù)據(jù)源進(jìn)行處理。
(也不算坑娇未，官方文檔沒有直接描述明顯) sharding-jdbc 只會根據(jù) from 后的第一張表進(jìn)行分庫分表策略墨缘，其他表都會按照第一張表的規(guī)則來，其他關(guān)聯(lián)表配置不會有效忘蟹。
不支持部分?jǐn)?shù)據(jù)庫函數(shù) 飒房，會導(dǎo)致分庫分表失效，最后按照隨機(jī)獲取數(shù)據(jù)源那種方式來進(jìn)行媚值。另外 postgres不支持 case when語句狠毯，會導(dǎo)致分庫分表失敗。

另外：

由于mybatis一個(gè)容器默認(rèn)只有一個(gè)數(shù)據(jù)庫類型褥芒，所以是不能直接支持幾種不同的數(shù)據(jù)庫同時(shí)使用的嚼松。

Caused by: java.lang.IllegalStateException: Database type inconsistent
 with 'org.apache.shardingsphere.underlying.common.database.type.dialect.PostgreSQLDatabaseType@44cffc25' 
and 'org.apache.shardingsphere.underlying.common.database.type.dialect.MySQLDatabaseType@6e041285'  
at com.google.common.base.Preconditions.checkState(Preconditions.java:173)

1. 引入依賴

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>

        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.1.1</version>
        </dependency>

        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>2.2.0</version>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.2.4</version>
        </dependency>

    </dependencies>

第一個(gè)坑 !: 引入 druid-spring-boot-starter （使用這個(gè)會報(bào)錯(cuò)，直接使用druid依賴就不會）

2. 基本配置

spring.shardingsphere.datasource.names=ds-0,ds-1,ds-2

#打開sql顯示
spring.shardingsphere.props.sql.show=true

spring.shardingsphere.datasource.ds-0.url=jdbc:mysql://127.0.0.1:3306/ds-0?useUnicode=true&characterEncoding=utf8&useSSL=false&allowMultiQueries=true&serverTimezone=GMT
#spring.shardingsphere.datasource.ds-1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds-0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds-0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds-0.username=root
spring.shardingsphere.datasource.ds-0.password=root

## mybatis不支持默認(rèn)多種數(shù)據(jù)庫相互切換
#spring.shardingsphere.datasource.ds-0.url=jdbc:postgresql://localhost:5432/test_db
##spring.shardingsphere.datasource.ds-0.type=com.zaxxer.hikari.HikariDataSource
#spring.shardingsphere.datasource.ds-0.type=com.alibaba.druid.pool.DruidDataSource
#spring.shardingsphere.datasource.ds-0.driver-class-name=org.postgresql.Driver
#spring.shardingsphere.datasource.ds-0.username=postgres
#spring.shardingsphere.datasource.ds-0.password=root


spring.shardingsphere.datasource.ds-1.url=jdbc:mysql://127.0.0.1:3306/ds-1?useUnicode=true&characterEncoding=utf8&useSSL=false&allowMultiQueries=true&serverTimezone=GMT
#spring.shardingsphere.datasource.ds-1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds-1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds-1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds-1.username=root
spring.shardingsphere.datasource.ds-1.password=root


spring.shardingsphere.datasource.ds-2.url=jdbc:mysql://127.0.0.1:3306/ds-2?useUnicode=true&characterEncoding=utf8&useSSL=false&allowMultiQueries=true&serverTimezone=GMT
#spring.shardingsphere.datasource.ds-2.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds-2.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds-2.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds-2.username=root
spring.shardingsphere.datasource.ds-2.password=root

##自定義參數(shù)
my-sharding-table.config=0,1,2,3

## demo2_test_user 如果有多個(gè)數(shù)據(jù)源锰扶，但是不配置（配置失效）的時(shí)候献酗，就會隨機(jī)獲取一個(gè)數(shù)據(jù)源進(jìn)行處理。
spring.shardingsphere.sharding.tables.demo2_test_user.actual-data-nodes=ds-0.test_user
##test_user
spring.shardingsphere.sharding.tables.test_user.actual-data-nodes=ds-$->{1..2}.test_user_$->{0..3}
spring.shardingsphere.sharding.tables.test_user.database-strategy.inline.sharding-column=scope
spring.shardingsphere.sharding.tables.test_user.database-strategy.inline.algorithm-expression=ds-$->{scope%2+1}
spring.shardingsphere.sharding.tables.test_user.table-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.test_user.table-strategy.inline.algorithm-expression=test_user_$->{id%4}
##course
spring.shardingsphere.sharding.tables.course.actual-data-nodes=ds-$->{[1,2]}.course_$->{[${my-sharding-table.config}]}
spring.shardingsphere.sharding.tables.course.database-strategy.inline.sharding-column=scope
spring.shardingsphere.sharding.tables.course.database-strategy.inline.algorithm-expression=ds-$->{scope%2+1}
spring.shardingsphere.sharding.tables.course.table-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.course.table-strategy.inline.algorithm-expression=course_$->{id%4}
##course_selected
spring.shardingsphere.sharding.tables.course_selected.actual-data-nodes=ds-$->{1..2}.course_selected_$->{0..3}
spring.shardingsphere.sharding.tables.course_selected.database-strategy.inline.sharding-column=scope
spring.shardingsphere.sharding.tables.course_selected.database-strategy.inline.algorithm-expression=ds-$->{scope%2+1}
spring.shardingsphere.sharding.tables.course_selected.table-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.course_selected.table-strategy.inline.algorithm-expression=course_selected_$->{user_id%4}

spring.shardingsphere.sharding.binding-tables[0]=test_user,course_selected,course

基本配置屬性：

	屬性	意義
1	spring.shardingsphere.datasource.names=ds-0,ds-1,ds-2	啟用數(shù)據(jù)源：ds-0坷牛，ds-1罕偎，ds-2
2	spring.shardingsphere.props.sql.show=true	打印sharding-jdbc的SQL轉(zhuǎn)換
3	spring.shardingsphere.datasource.ds-0.url=xxx spring.shardingsphere.datasource.ds-0.type=com.alibaba.druid.pool.DruidDataSource spring.shardingsphere.datasource.ds-0.driver-class-name=com.mysql.cj.jdbc.Driver spring.shardingsphere.datasource.ds-0.username=root spring.shardingsphere.datasource.ds-0.password=root	數(shù)據(jù)源配置，ds-0,ds-1,ds-2 分別進(jìn)行配置京闰。 mysql-driver：8.0 使用 com.mysql.cj.jdbc.Driver mysql-driver：5.+ 使用 com.mysql.jdbc.Driver
4	spring.shardingsphere.sharding.tables.table_xxx.actual-data-nodes=ds-0.test_user	配置表映射實(shí)際表 ds-0（上面配置數(shù)據(jù)源）.test_user（實(shí)際表名）

如果只是單純想做多數(shù)據(jù)源配置颜及，上面的配置就已經(jīng)夠了。按照類似結(jié)構(gòu)進(jìn)行配置蹂楣，就可以進(jìn)行多數(shù)據(jù)源配置俏站。
其中如果出現(xiàn)不同庫表名相同，可以通過直接改表映射名
 // 數(shù)據(jù)庫 db_1 db_2 都有表 test_user
// 寫SQL 的 from表名 為 db1_test_user
 spring.shardingsphere.sharding.tables.db1_test_user.actual-data-nodes=ds-1.test_user
// 寫SQL 的 from表名 為 db2_test_user
 spring.shardingsphere.sharding.tables.db2_test_user.actual-data-nodes=ds-2.test_user
這樣實(shí)際生成的 SQL 就是 SELECT * FROM test_user 并且會去指定的數(shù)據(jù)源進(jìn)行查詢

進(jìn)行分庫分表

	屬性	意義
5	`spring.shardingsphere.sharding.tables.test_user.actual-data-nodes=ds-$->{1..100}.test_user_$->{0..3}`	`ds-$->{1..100}從1到2的所有數(shù)字拼接成數(shù)據(jù)源痊土，如：ds-1肄扎，ds-2，ds-3`
6	`my-sharding-table.config=0,1,2,3` `spring.shardingsphere.sharding.tables.course.`actual-data-nodes`=ds-$->{[1,3]}.course_$->{[${my-sharding-table.config}]}`	`ds-$->{[1,3]}：數(shù)組赁酝，如：ds-1犯祠，ds-3` `$->{[${my-sharding-table.config}]}:從el表達(dá)式中獲取值`
7	`spring.shardingsphere.sharding.tables.test_user.`database-strategy.inline`.sharding-column=scope spring.shardingsphere.sharding.tables.test_user.`database-strategy.inline`.algorithm-expression=ds-$->{scope%2+1}`	`分庫策略：根據(jù)指定的字段，及分庫策略表達(dá)式` `$->{scope%2+1} 對scope的值進(jìn)行取mod 結(jié)果再 +1`
8	`spring.shardingsphere.sharding.tables.test_user.`table-strategy.inline`.sharding-column=id spring.shardingsphere.sharding.tables.test_user.`table-strategy.inline`.algorithm-expression=test_user_$->{id%4}`	`分表策略：根據(jù)指定的字段酌呆，及分表策略表達(dá)式` `$->{id%4} 對id的值進(jìn)行取mod 結(jié)果再 +1`
9	spring.shardingsphere.sharding.binding-tables[0]=test_user,course_selected,course	將表進(jìn)行關(guān)聯(lián)衡载，join查詢必須有關(guān)聯(lián)才可以實(shí)現(xiàn)分庫分表，不然會報(bào)找不到表或者沒有指定的分庫分表策略的錯(cuò)誤

擴(kuò)展配置

# 配置 主鍵生成
spring.shardingsphere.sharding.tables.course.key-generator.column=id
#指定course表里面主鍵id生成策略 SNOWFLAKE
spring.shardingsphere.sharding.tables.course.key-generator.type=SNOWFLAKE

因?yàn)轫?xiàng)目使用的是mybatis-plus肪笋，已經(jīng)默認(rèn)了雪花ID月劈，所以這里不配也行

此處采坑 2 ! 如果有多個(gè)數(shù)據(jù)源，但是不配置（配置失效）的時(shí)候藤乙，就會隨機(jī)獲取一個(gè)數(shù)據(jù)源進(jìn)行處理猜揪。
##  如果有多個(gè)數(shù)據(jù)源，但是不配置（配置失效）的時(shí)候坛梁，就會隨機(jī)獲取一個(gè)數(shù)據(jù)源進(jìn)行處理而姐。
spring.shardingsphere.sharding.tables.table_xxx.actual-data-nodes=ds-0.test_user

日志如下：

2021-01-12 11:44:43.326  INFO 31140 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO test_user 
 ( id,
    `name`,
    `scope` )  VALUES 
 ( ?,
    ?,
    ? )
2021-01-12 11:44:43.327  INFO 31140 --- [           main] ShardingSphere-SQL                       : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@1b3a9ef4, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@7a1371), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@7a1371, columnNames=[id, name, scope], insertValueContexts=[InsertValueContext(parametersCount=3, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=62, stopIndex=62, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=66, stopIndex=66, parameterMarkerIndex=1), ParameterMarkerExpressionSegment(startIndex=70, stopIndex=70, parameterMarkerIndex=2)], parameters=[1348838288484306946, name-0, 1])], generatedKeyContext=Optional.empty)
2021-01-12 11:44:43.327  INFO 31140 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds-2 ::: INSERT INTO test_user 
 ( id,
    `name`,
    `scope` )  VALUES 
 (?, ?, ?) ::: [1348838288484306946, name-0, 1]
2021-01-12 11:44:43.376  INFO 31140 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO test_user 
 ( id,
    `name`,
    `scope` )  VALUES 
 ( ?,
    ?,
    ? )
2021-01-12 11:44:43.376  INFO 31140 --- [           main] ShardingSphere-SQL                       : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@1b3a9ef4, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@655203e3), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@655203e3, columnNames=[id, name, scope], insertValueContexts=[InsertValueContext(parametersCount=3, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=62, stopIndex=62, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=66, stopIndex=66, parameterMarkerIndex=1), ParameterMarkerExpressionSegment(startIndex=70, stopIndex=70, parameterMarkerIndex=2)], parameters=[1348838290745036802, name-1, 1])], generatedKeyContext=Optional.empty)
2021-01-12 11:44:43.376  INFO 31140 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds-0 ::: INSERT INTO test_user 
 ( id,
    `name`,
    `scope` )  VALUES 
 (?, ?, ?) ::: [1348838290745036802, name-1, 1]
2021-01-12 11:44:43.381  INFO 31140 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO test_user 
 ( id,
    `name`,
    `scope` )  VALUES 
 ( ?,
    ?,
    ? )
2021-01-12 11:44:43.382  INFO 31140 --- [           main] ShardingSphere-SQL                       : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@1b3a9ef4, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@568f4faa), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@568f4faa, columnNames=[id, name, scope], insertValueContexts=[InsertValueContext(parametersCount=3, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=62, stopIndex=62, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=66, stopIndex=66, parameterMarkerIndex=1), ParameterMarkerExpressionSegment(startIndex=70, stopIndex=70, parameterMarkerIndex=2)], parameters=[1348838290770202626, name-2, 1])], generatedKeyContext=Optional.empty)
2021-01-12 11:44:43.382  INFO 31140 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds-0 ::: INSERT INTO test_user 
 ( id,
    `name`,
    `scope` )  VALUES 
 (?, ?, ?) ::: [1348838290770202626, name-2, 1]
2021-01-12 11:44:43.385  INFO 31140 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO test_user 
 ( id,
    `name`,
    `scope` )  VALUES 
 ( ?,
    ?,
    ? )
2021-01-12 11:44:43.385  INFO 31140 --- [           main] ShardingSphere-SQL                       : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@1b3a9ef4, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@43588265), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@43588265, columnNames=[id, name, scope], insertValueContexts=[InsertValueContext(parametersCount=3, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=62, stopIndex=62, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=66, stopIndex=66, parameterMarkerIndex=1), ParameterMarkerExpressionSegment(startIndex=70, stopIndex=70, parameterMarkerIndex=2)], parameters=[1348838290786979842, name-3, 1])], generatedKeyContext=Optional.empty)
2021-01-12 11:44:43.385  INFO 31140 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds-0 ::: INSERT INTO test_user 
 ( id,
    `name`,
    `scope` )  VALUES 
 (?, ?, ?) ::: [1348838290786979842, name-3, 1]
2021-01-12 11:44:43.387  INFO 31140 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO test_user 
 ( id,
    `name`,
    `scope` )  VALUES 
 ( ?,
    ?,
    ? )
2021-01-12 11:44:43.387  INFO 31140 --- [           main] ShardingSphere-SQL                       : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@1b3a9ef4, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@2774dcf4), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@2774dcf4, columnNames=[id, name, scope], insertValueContexts=[InsertValueContext(parametersCount=3, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=62, stopIndex=62, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=66, stopIndex=66, parameterMarkerIndex=1), ParameterMarkerExpressionSegment(startIndex=70, stopIndex=70, parameterMarkerIndex=2)], parameters=[1348838290795368449, name-4, 1])], generatedKeyContext=Optional.empty)
2021-01-12 11:44:43.387  INFO 31140 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds-1 ::: INSERT INTO test_user 
 ( id,
    `name`,
    `scope` )  VALUES 
 (?, ?, ?) ::: [1348838290795368449, name-4, 1]
2021-01-12 11:44:43.392  INFO 31140 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO test_user 
 ( id,
    `name`,
    `scope` )  VALUES 
 ( ?,
    ?,
    ? )
2021-01-12 11:44:43.392  INFO 31140 --- [           main] ShardingSphere-SQL                       : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@1b3a9ef4, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@61ab6521), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@61ab6521, columnNames=[id, name, scope], insertValueContexts=[InsertValueContext(parametersCount=3, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=62, stopIndex=62, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=66, stopIndex=66, parameterMarkerIndex=1), ParameterMarkerExpressionSegment(startIndex=70, stopIndex=70, parameterMarkerIndex=2)], parameters=[1348838290820534273, name-5, 1])], generatedKeyContext=Optional.empty)
2021-01-12 11:44:43.393  INFO 31140 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds-2 ::: INSERT INTO test_user 
 ( id,
    `name`,
    `scope` )  VALUES 
 (?, ?, ?) ::: [1348838290820534273, name-5, 1]
2021-01-12 11:44:43.396  INFO 31140 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO test_user 
 ( id,
    `name`,
    `scope` )  VALUES 
 ( ?,
    ?,
    ? )
2021-01-12 11:44:43.396  INFO 31140 --- [           main] ShardingSphere-SQL                       : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@1b3a9ef4, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@52c46334), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@52c46334, columnNames=[id, name, scope], insertValueContexts=[InsertValueContext(parametersCount=3, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=62, stopIndex=62, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=66, stopIndex=66, parameterMarkerIndex=1), ParameterMarkerExpressionSegment(startIndex=70, stopIndex=70, parameterMarkerIndex=2)], parameters=[1348838290828922881, name-6, 1])], generatedKeyContext=Optional.empty)
2021-01-12 11:44:43.396  INFO 31140 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds-0 ::: INSERT INTO test_user 
 ( id,
    `name`,
    `scope` )  VALUES 
 (?, ?, ?) ::: [1348838290828922881, name-6, 1]
2021-01-12 11:44:43.399  INFO 31140 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO test_user 
 ( id,
    `name`,
    `scope` )  VALUES 
 ( ?,
    ?,
    ? )
2021-01-12 11:44:43.399  INFO 31140 --- [           main] ShardingSphere-SQL                       : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@1b3a9ef4, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@2b458cd6), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@2b458cd6, columnNames=[id, name, scope], insertValueContexts=[InsertValueContext(parametersCount=3, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=62, stopIndex=62, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=66, stopIndex=66, parameterMarkerIndex=1), ParameterMarkerExpressionSegment(startIndex=70, stopIndex=70, parameterMarkerIndex=2)], parameters=[1348838290845700098, name-7, 1])], generatedKeyContext=Optional.empty)
2021-01-12 11:44:43.400  INFO 31140 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds-0 ::: INSERT INTO test_user 
 ( id,
    `name`,
    `scope` )  VALUES 
 (?, ?, ?) ::: [1348838290845700098, name-7, 1]
2021-01-12 11:44:43.402  INFO 31140 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO test_user 
 ( id,
    `name`,
    `scope` )  VALUES 
 ( ?,
    ?,
    ? )
2021-01-12 11:44:43.402  INFO 31140 --- [           main] ShardingSphere-SQL                       : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@1b3a9ef4, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@227a933d), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@227a933d, columnNames=[id, name, scope], insertValueContexts=[InsertValueContext(parametersCount=3, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=62, stopIndex=62, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=66, stopIndex=66, parameterMarkerIndex=1), ParameterMarkerExpressionSegment(startIndex=70, stopIndex=70, parameterMarkerIndex=2)], parameters=[1348838290862477314, name-8, 1])], generatedKeyContext=Optional.empty)
2021-01-12 11:44:43.402  INFO 31140 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds-1 ::: INSERT INTO test_user 
 ( id,
    `name`,
    `scope` )  VALUES 
 (?, ?, ?) ::: [1348838290862477314, name-8, 1]
2021-01-12 11:44:43.405  INFO 31140 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO test_user 
 ( id,
    `name`,
    `scope` )  VALUES 
 ( ?,
    ?,
    ? )
2021-01-12 11:44:43.405  INFO 31140 --- [           main] ShardingSphere-SQL                       : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@1b3a9ef4, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@dd737ea), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@dd737ea, columnNames=[id, name, scope], insertValueContexts=[InsertValueContext(parametersCount=3, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=62, stopIndex=62, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=66, stopIndex=66, parameterMarkerIndex=1), ParameterMarkerExpressionSegment(startIndex=70, stopIndex=70, parameterMarkerIndex=2)], parameters=[1348838290870865921, name-9, 1])], generatedKeyContext=Optional.empty)
2021-01-12 11:44:43.406  INFO 31140 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds-0 ::: INSERT INTO test_user 
 ( id,
    `name`,
    `scope` )  VALUES 
 (?, ?, ?) ::: [1348838290870865921, name-9, 1]

JOIN 關(guān)聯(lián)查詢

## 進(jìn)行表關(guān)聯(lián)
spring.shardingsphere.sharding.binding-tables[0]=test_user,course_selected,course

不配置：分片字段會無效，表進(jìn)行笛卡爾集划咐，

       : Logic SQL: SELECT * FROM course a,course_selected b WHERE a.id = b.course_id
2021-01-13 14:30:20.742  INFO 30632 --- [           main] ShardingSphere-SQL                       : SQLStatement: SelectStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@3ef2b8e5, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@49190ed6), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@49190ed6, 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=scope, alias=Optional.empty), ColumnProjection(owner=null, name=id, alias=Optional.empty), ColumnProjection(owner=null, name=scope, alias=Optional.empty), ColumnProjection(owner=null, name=user_id, alias=Optional.empty), ColumnProjection(owner=null, name=course_id, alias=Optional.empty)])]), groupByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.groupby.GroupByContext@5d717f19, orderByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.orderby.OrderByContext@18715bb, paginationContext=org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.PaginationContext@2a19a0fe, containsSubquery=false)
2021-01-13 14:30:20.742  INFO 30632 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds-1 ::: SELECT * FROM course_3 a,course_selected_0 b WHERE a.id = b.course_id
2021-01-13 14:30:20.742  INFO 30632 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds-1 ::: SELECT * FROM course_3 a,course_selected_1 b WHERE a.id = b.course_id
2021-01-13 14:30:20.742  INFO 30632 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds-1 ::: SELECT * FROM course_3 a,course_selected_2 b WHERE a.id = b.course_id
2021-01-13 14:30:20.742  INFO 30632 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds-1 ::: SELECT * FROM course_3 a,course_selected_3 b WHERE a.id = b.course_id
2021-01-13 14:30:20.742  INFO 30632 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds-1 ::: SELECT * FROM course_2 a,course_selected_0 b WHERE a.id = b.course_id
2021-01-13 14:30:20.742  INFO 30632 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds-1 ::: SELECT * FROM course_2 a,course_selected_1 b WHERE a.id = b.course_id
2021-01-13 14:30:20.742  INFO 30632 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds-1 ::: SELECT * FROM course_2 a,course_selected_2 b WHERE a.id = b.course_id
2021-01-13 14:30:20.742  INFO 30632 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds-1 ::: SELECT * FROM course_2 a,course_selected_3 b WHERE a.id = b.course_id
2021-01-13 14:30:20.742  INFO 30632 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds-1 ::: SELECT * FROM course_1 a,course_selected_0 b WHERE a.id = b.course_id
2021-01-13 14:30:20.743  INFO 30632 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds-1 ::: SELECT * FROM course_1 a,course_selected_1 b WHERE a.id = b.course_id
2021-01-13 14:30:20.743  INFO 30632 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds-1 ::: SELECT * FROM course_1 a,course_selected_2 b WHERE a.id = b.course_id
2021-01-13 14:30:20.743  INFO 30632 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds-1 ::: SELECT * FROM course_1 a,course_selected_3 b WHERE a.id = b.course_id
2021-01-13 14:30:20.743  INFO 30632 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds-1 ::: SELECT * FROM course_0 a,course_selected_0 b WHERE a.id = b.course_id
2021-01-13 14:30:20.743  INFO 30632 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds-1 ::: SELECT * FROM course_0 a,course_selected_1 b WHERE a.id = b.course_id
2021-01-13 14:30:20.743  INFO 30632 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds-1 ::: SELECT * FROM course_0 a,course_selected_2 b WHERE a.id = b.course_id
2021-01-13 14:30:20.743  INFO 30632 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds-1 ::: SELECT * FROM course_0 a,course_selected_3 b WHERE a.id = b.course_id
2021-01-13 14:30:20.743  INFO 30632 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds-2 ::: SELECT * FROM course_3 a,course_selected_0 b WHERE a.id = b.course_id
2021-01-13 14:30:20.743  INFO 30632 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds-2 ::: SELECT * FROM course_3 a,course_selected_1 b WHERE a.id = b.course_id
2021-01-13 14:30:20.743  INFO 30632 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds-2 ::: SELECT * FROM course_3 a,course_selected_2 b WHERE a.id = b.course_id
2021-01-13 14:30:20.743  INFO 30632 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds-2 ::: SELECT * FROM course_3 a,course_selected_3 b WHERE a.id = b.course_id
2021-01-13 14:30:20.743  INFO 30632 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds-2 ::: SELECT * FROM course_2 a,course_selected_0 b WHERE a.id = b.course_id
2021-01-13 14:30:20.743  INFO 30632 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds-2 ::: SELECT * FROM course_2 a,course_selected_1 b WHERE a.id = b.course_id
2021-01-13 14:30:20.743  INFO 30632 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds-2 ::: SELECT * FROM course_2 a,course_selected_2 b WHERE a.id = b.course_id
2021-01-13 14:30:20.743  INFO 30632 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds-2 ::: SELECT * FROM course_2 a,course_selected_3 b WHERE a.id = b.course_id
2021-01-13 14:30:20.743  INFO 30632 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds-2 ::: SELECT * FROM course_1 a,course_selected_0 b WHERE a.id = b.course_id
2021-01-13 14:30:20.743  INFO 30632 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds-2 ::: SELECT * FROM course_1 a,course_selected_1 b WHERE a.id = b.course_id
2021-01-13 14:30:20.743  INFO 30632 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds-2 ::: SELECT * FROM course_1 a,course_selected_2 b WHERE a.id = b.course_id
2021-01-13 14:30:20.743  INFO 30632 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds-2 ::: SELECT * FROM course_1 a,course_selected_3 b WHERE a.id = b.course_id
2021-01-13 14:30:20.743  INFO 30632 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds-2 ::: SELECT * FROM course_0 a,course_selected_0 b WHERE a.id = b.course_id
2021-01-13 14:30:20.743  INFO 30632 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds-2 ::: SELECT * FROM course_0 a,course_selected_1 b WHERE a.id = b.course_id
2021-01-13 14:30:20.743  INFO 30632 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds-2 ::: SELECT * FROM course_0 a,course_selected_2 b WHERE a.id = b.course_id
2021-01-13 14:30:20.743  INFO 30632 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds-2 ::: SELECT * FROM course_0 a,course_selected_3 b WHERE a.id = b.course_id

配置：分片字段生效拴念，進(jìn)行分庫分表查

2021-01-13 14:32:45.756  INFO 19256 --- [           main] ShardingSphere-SQL                       : Logic SQL: SELECT * FROM course a,course_selected b WHERE a.id = b.course_id
2021-01-13 14:32:45.756  INFO 19256 --- [           main] ShardingSphere-SQL                       : SQLStatement: SelectStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@2321e482, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@467ef400), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@467ef400, 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=scope, alias=Optional.empty), ColumnProjection(owner=null, name=id, alias=Optional.empty), ColumnProjection(owner=null, name=scope, alias=Optional.empty), ColumnProjection(owner=null, name=user_id, alias=Optional.empty), ColumnProjection(owner=null, name=course_id, alias=Optional.empty)])]), groupByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.groupby.GroupByContext@13fe5bb7, orderByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.orderby.OrderByContext@4276ad40, paginationContext=org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.PaginationContext@6e5f5478, containsSubquery=false)
2021-01-13 14:32:45.756  INFO 19256 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds-1 ::: SELECT * FROM course_0 a,course_selected_0 b WHERE a.id = b.course_id
2021-01-13 14:32:45.756  INFO 19256 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds-1 ::: SELECT * FROM course_1 a,course_selected_1 b WHERE a.id = b.course_id
2021-01-13 14:32:45.756  INFO 19256 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds-1 ::: SELECT * FROM course_2 a,course_selected_2 b WHERE a.id = b.course_id
2021-01-13 14:32:45.756  INFO 19256 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds-1 ::: SELECT * FROM course_3 a,course_selected_3 b WHERE a.id = b.course_id
2021-01-13 14:32:45.756  INFO 19256 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds-2 ::: SELECT * FROM course_0 a,course_selected_0 b WHERE a.id = b.course_id
2021-01-13 14:32:45.756  INFO 19256 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds-2 ::: SELECT * FROM course_1 a,course_selected_1 b WHERE a.id = b.course_id
2021-01-13 14:32:45.756  INFO 19256 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds-2 ::: SELECT * FROM course_2 a,course_selected_2 b WHERE a.id = b.course_id
2021-01-13 14:32:45.756  INFO 19256 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds-2 ::: SELECT * FROM course_3 a,course_selected_3 b WHERE a.id = b.course_id

采坑3，sharding-jdbc 只會根據(jù) from 后的第一張表進(jìn)行分庫分表策略褐缠，其他表都會按照第一張表的規(guī)則來政鼠，所以其他關(guān)聯(lián)表，就算不配置分庫分表的策略队魏，依然有效公般。配置如下，效果一致胡桨。

##course
spring.shardingsphere.sharding.tables.course.actual-data-nodes=ds-$->{[1,2]}.course_$->{[${my-sharding-table.config}]}

##course_selected
spring.shardingsphere.sharding.tables.course_selected.actual-data-nodes=ds-$->{1..2}.course_selected_$->{0..3}
spring.shardingsphere.sharding.tables.course_selected.database-strategy.inline.sharding-column=scope
spring.shardingsphere.sharding.tables.course_selected.database-strategy.inline.algorithm-expression=ds-$->{scope%2+1}
spring.shardingsphere.sharding.tables.course_selected.table-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.course_selected.table-strategy.inline.algorithm-expression=course_selected_$->{user_id%4}

spring.shardingsphere.sharding.binding-tables[0]=test_user,course_selected,course

采坑4 官帘！不支持部分?jǐn)?shù)據(jù)庫函數(shù) ，會導(dǎo)致分庫分表失效昧谊，最后按照隨機(jī)獲取數(shù)據(jù)源那種方式來進(jìn)行刽虹。另外postgres不支持 case when語句，會導(dǎo)致分庫分表失敗呢诬，但是MYSQL不會

MySQL 使用 case when 正常進(jìn)行分庫分表

2021-01-14 14:08:33.100  INFO 8648 --- [           main] ShardingSphere-SQL                       : Logic SQL: SELECT a.id,name,b.course_id, CASE a.scope WHEN 0 THEN 100 ELSE 200 END scope FROM test_user a JOIN course_selected b ON a.id = b.user_id
2021-01-14 14:08:33.100  INFO 8648 --- [           main] ShardingSphere-SQL                       : SQLStatement: SelectStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@5c4714ef, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@c94bd18), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@c94bd18, projectionsContext=ProjectionsContext(startIndex=7, stopIndex=76, distinctRow=false, projections=[ColumnProjection(owner=a, name=id, alias=Optional.empty), ColumnProjection(owner=null, name=name, alias=Optional.empty), ColumnProjection(owner=b, name=course_id, alias=Optional.empty), ExpressionProjection(expression=CASEa.scopeWHEN0THEN100ELSE200END, alias=Optional[scope])]), groupByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.groupby.GroupByContext@71fb8301, orderByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.orderby.OrderByContext@7cdfa824, paginationContext=org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.PaginationContext@18db3b3c, containsSubquery=false)
2021-01-14 14:08:33.100  INFO 8648 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds-1 ::: SELECT a.id,name,b.course_id, CASE a.scope WHEN 0 THEN 100 ELSE 200 END scope FROM test_user_0 a JOIN course_selected_0 b ON a.id = b.user_id
2021-01-14 14:08:33.100  INFO 8648 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds-1 ::: SELECT a.id,name,b.course_id, CASE a.scope WHEN 0 THEN 100 ELSE 200 END scope FROM test_user_1 a JOIN course_selected_1 b ON a.id = b.user_id
2021-01-14 14:08:33.100  INFO 8648 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds-1 ::: SELECT a.id,name,b.course_id, CASE a.scope WHEN 0 THEN 100 ELSE 200 END scope FROM test_user_2 a JOIN course_selected_2 b ON a.id = b.user_id
2021-01-14 14:08:33.100  INFO 8648 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds-1 ::: SELECT a.id,name,b.course_id, CASE a.scope WHEN 0 THEN 100 ELSE 200 END scope FROM test_user_3 a JOIN course_selected_3 b ON a.id = b.user_id
2021-01-14 14:08:33.100  INFO 8648 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds-2 ::: SELECT a.id,name,b.course_id, CASE a.scope WHEN 0 THEN 100 ELSE 200 END scope FROM test_user_0 a JOIN course_selected_0 b ON a.id = b.user_id
2021-01-14 14:08:33.100  INFO 8648 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds-2 ::: SELECT a.id,name,b.course_id, CASE a.scope WHEN 0 THEN 100 ELSE 200 END scope FROM test_user_1 a JOIN course_selected_1 b ON a.id = b.user_id
2021-01-14 14:08:33.100  INFO 8648 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds-2 ::: SELECT a.id,name,b.course_id, CASE a.scope WHEN 0 THEN 100 ELSE 200 END scope FROM test_user_2 a JOIN course_selected_2 b ON a.id = b.user_id
2021-01-14 14:08:33.100  INFO 8648 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds-2 ::: SELECT a.id,name,b.course_id, CASE a.scope WHEN 0 THEN 100 ELSE 200 END scope FROM test_user_3 a JOIN course_selected_3 b ON a.id = b.user_id

Postgres 使用 case when 涌哲，無法使用分庫表策略，報(bào)找不到表錯(cuò)誤

Logic SQL: SELECT a.id,name,b.course_id, CASE a.scope WHEN 0 THEN 100 ELSE 200 END scope FROM test_user a JOIN course_selected b ON a.id = b.user_id
2021-01-14 14:06:15.031  INFO 3808 --- [           main] ShardingSphere-SQL                       : SQLStatement: SelectStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@57c6feea, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@3b57f915), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@3b57f915, projectionsContext=ProjectionsContext(startIndex=7, stopIndex=70, distinctRow=false, projections=[ColumnProjection(owner=a, name=id, alias=Optional.empty), ColumnProjection(owner=null, name=name, alias=Optional.empty), ColumnProjection(owner=b, name=course_id, alias=Optional.empty), ExpressionProjection(expression=CASEa.scopeWHEN0THEN100ELSE200END, alias=Optional[END])]), groupByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.groupby.GroupByContext@39c7fb0b, orderByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.orderby.OrderByContext@645dc557, paginationContext=org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.PaginationContext@48c5698, containsSubquery=false)
2021-01-14 14:06:15.031  INFO 3808 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds-2 ::: SELECT a.id,name,b.course_id, CASE a.scope WHEN 0 THEN 100 ELSE 200 END scope FROM test_user a JOIN course_selected b ON a.id = b.user_id

org.springframework.jdbc.BadSqlGrammarException: 
### Error querying database.  Cause: org.postgresql.util.PSQLException: 錯(cuò)誤: 關(guān)系 "test_user" 不存在
  位置：84
### The error may exist in com/example/sharding/modules/demo1/dao/jpas/TestUserMapper.java (best guess)
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: SELECT a.id,name,b.course_id, CASE a.scope WHEN 0 THEN 100 ELSE 200 END scope FROM test_user a JOIN course_selected b ON a.id = b.user_id
### Cause: org.postgresql.util.PSQLException: 錯(cuò)誤: 關(guān)系 "test_user" 不存在
  位置：84
; bad SQL grammar []; nested exception is org.postgresql.util.PSQLException: 錯(cuò)誤: 關(guān)系 "test_user" 不存在
  位置：84

    at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:239)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:70)
    at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73)
    at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446)
    at com.sun.proxy.$Proxy66.selectList(Unknown Source)
    at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:230)
    at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:139)
    at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:76)
    at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:59)
    at com.sun.proxy.$Proxy85.findCaseWhen(Unknown Source)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:344)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:198)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:137)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:215)
    at com.sun.proxy.$Proxy86.findCaseWhen(Unknown Source)
    at com.example.sharding.dao.DefaultShardingTests.testCaseWhen(DefaultShardingTests.java:47)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.junit.platform.commons.util.ReflectionUtils.invokeMethod(ReflectionUtils.java:688)
    at org.junit.jupiter.engine.execution.MethodInvocation.proceed(MethodInvocation.java:60)
    at org.junit.jupiter.engine.execution.InvocationInterceptorChain$ValidatingInvocation.proceed(InvocationInterceptorChain.java:131)
    at org.junit.jupiter.engine.extension.TimeoutExtension.intercept(TimeoutExtension.java:149)
    at org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestableMethod(TimeoutExtension.java:140)
    at org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestMethod(TimeoutExtension.java:84)
    at org.junit.jupiter.engine.execution.ExecutableInvoker$ReflectiveInterceptorCall.lambda$ofVoidMethod$0(ExecutableInvoker.java:115)
    at org.junit.jupiter.engine.execution.ExecutableInvoker.lambda$invoke$0(ExecutableInvoker.java:105)
    at org.junit.jupiter.engine.execution.InvocationInterceptorChain$InterceptedInvocation.proceed(InvocationInterceptorChain.java:106)
    at org.junit.jupiter.engine.execution.InvocationInterceptorChain.proceed(InvocationInterceptorChain.java:64)
    at org.junit.jupiter.engine.execution.InvocationInterceptorChain.chainAndInvoke(InvocationInterceptorChain.java:45)
    at org.junit.jupiter.engine.execution.InvocationInterceptorChain.invoke(InvocationInterceptorChain.java:37)
    at org.junit.jupiter.engine.execution.ExecutableInvoker.invoke(ExecutableInvoker.java:104)
    at org.junit.jupiter.engine.execution.ExecutableInvoker.invoke(ExecutableInvoker.java:98)
    at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.lambda$invokeTestMethod$6(TestMethodTestDescriptor.java:210)
    at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
    at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.invokeTestMethod(TestMethodTestDescriptor.java:206)
    at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.execute(TestMethodTestDescriptor.java:131)
    at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.execute(TestMethodTestDescriptor.java:65)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$5(NodeTestTask.java:139)
    at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$7(NodeTestTask.java:129)
    at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:137)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:127)
    at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:126)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:84)
    at java.util.ArrayList.forEach(ArrayList.java:1257)
    at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.invokeAll(SameThreadHierarchicalTestExecutorService.java:38)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$5(NodeTestTask.java:143)
    at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$7(NodeTestTask.java:129)
    at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:137)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:127)
    at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:126)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:84)
    at java.util.ArrayList.forEach(ArrayList.java:1257)
    at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.invokeAll(SameThreadHierarchicalTestExecutorService.java:38)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$5(NodeTestTask.java:143)
    at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$7(NodeTestTask.java:129)
    at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:137)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:127)
    at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:126)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:84)
    at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.submit(SameThreadHierarchicalTestExecutorService.java:32)
    at org.junit.platform.engine.support.hierarchical.HierarchicalTestExecutor.execute(HierarchicalTestExecutor.java:57)
    at org.junit.platform.engine.support.hierarchical.HierarchicalTestEngine.execute(HierarchicalTestEngine.java:51)
    at org.junit.platform.launcher.core.EngineExecutionOrchestrator.execute(EngineExecutionOrchestrator.java:108)
    at org.junit.platform.launcher.core.EngineExecutionOrchestrator.execute(EngineExecutionOrchestrator.java:88)
    at org.junit.platform.launcher.core.EngineExecutionOrchestrator.lambda$execute$0(EngineExecutionOrchestrator.java:54)
    at org.junit.platform.launcher.core.EngineExecutionOrchestrator.withInterceptedStreams(EngineExecutionOrchestrator.java:67)
    at org.junit.platform.launcher.core.EngineExecutionOrchestrator.execute(EngineExecutionOrchestrator.java:52)
    at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:96)
    at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:75)
    at com.intellij.junit5.JUnit5IdeaTestRunner.startRunnerWithArgs(JUnit5IdeaTestRunner.java:74)
    at com.intellij.rt.execution.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:47)
    at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:242)
    at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:70)
Caused by: org.postgresql.util.PSQLException: 錯(cuò)誤: 關(guān)系 "test_user" 不存在
  位置：84
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2553)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2285)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:323)
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:473)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:393)
    at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:164)
    at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:153)
    at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:497)
    at org.apache.shardingsphere.shardingjdbc.executor.SQLExecuteCallbackFactory$2.executeSQL(SQLExecuteCallbackFactory.java:62)
    at org.apache.shardingsphere.shardingjdbc.executor.SQLExecuteCallbackFactory$2.executeSQL(SQLExecuteCallbackFactory.java:58)
    at org.apache.shardingsphere.sharding.execute.sql.execute.SQLExecuteCallback.execute0(SQLExecuteCallback.java:82)
    at org.apache.shardingsphere.sharding.execute.sql.execute.SQLExecuteCallback.execute(SQLExecuteCallback.java:58)
    at org.apache.shardingsphere.underlying.executor.engine.ExecutorEngine.syncExecute(ExecutorEngine.java:97)
    at org.apache.shardingsphere.underlying.executor.engine.ExecutorEngine.parallelExecute(ExecutorEngine.java:93)
    at org.apache.shardingsphere.underlying.executor.engine.ExecutorEngine.execute(ExecutorEngine.java:76)
    at org.apache.shardingsphere.sharding.execute.sql.execute.SQLExecuteTemplate.execute(SQLExecuteTemplate.java:68)
    at org.apache.shardingsphere.sharding.execute.sql.execute.SQLExecuteTemplate.execute(SQLExecuteTemplate.java:51)
    at org.apache.shardingsphere.shardingjdbc.executor.AbstractStatementExecutor.executeCallback(AbstractStatementExecutor.java:129)
    at org.apache.shardingsphere.shardingjdbc.executor.PreparedStatementExecutor.execute(PreparedStatementExecutor.java:148)
    at org.apache.shardingsphere.shardingjdbc.jdbc.core.statement.ShardingPreparedStatement.execute(ShardingPreparedStatement.java:145)
    at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:63)
    at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63)
    at com.sun.proxy.$Proxy100.query(Unknown Source)
    at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:63)
    at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:326)
    at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)
    at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109)
    at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:83)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:148)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:141)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:433)
    ... 83 more

最后編輯于：2021.01.14 14:09:59

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者

人面猴
序言：七十年代末馅巷，一起剝皮案震驚了整個(gè)濱河市膛虫，隨后出現(xiàn)的幾起案子，更是在濱河造成了極大的恐慌钓猬，老刑警劉巖稍刀，帶你破解...
沈念sama閱讀 210,914評論 6贊 490
死咒
序言：濱河連續(xù)發(fā)生了三起死亡事件，死亡現(xiàn)場離奇詭異敞曹，居然都是意外死亡账月，警方通過查閱死者的電腦和手機(jī)，發(fā)現(xiàn)死者居然都...
沈念sama閱讀 89,935評論 2贊 383
救了他兩次的神仙讓他今天三更去死
文/潘曉璐我一進(jìn)店門澳迫，熙熙樓的掌柜王于貴愁眉苦臉地迎上來局齿，“玉大人，你說我怎么就攤上這事橄登∽ゼ撸” “怎么了讥此？”我有些...
開封第一講書人閱讀 156,531評論 0贊 345
道士緝兇錄：失蹤的賣姜人
文/不壞的土叔我叫張陵，是天一觀的道長谣妻。經(jīng)常有香客問我萄喳，道長，這世上最難降的妖魔是什么蹋半？我笑而不...
開封第一講書人閱讀 56,309評論 1贊 282
?港島之戀（遺憾婚禮）
正文為了忘掉前任他巨，我火速辦了婚禮，結(jié)果婚禮上减江，老公的妹妹穿的比我還像新娘染突。我一直安慰自己，他們只是感情好辈灼，可當(dāng)我...
茶點(diǎn)故事閱讀 65,381評論 5贊 384
惡毒庶女頂嫁案：這布局不是一般人想出來的
文/花漫我一把揭開白布份企。她就那樣靜靜地躺著，像睡著了一般巡莹。火紅的嫁衣襯著肌膚如雪薪棒。梳的紋絲不亂的頭發(fā)上，一...
開封第一講書人閱讀 49,730評論 1贊 289
城市分裂傳說
那天榕莺，我揣著相機(jī)與錄音俐芯，去河邊找鬼。笑死钉鸯，一個(gè)胖子當(dāng)著我的面吹牛吧史，可吹牛的內(nèi)容都是我干的。我是一名探鬼主播唠雕，決...
沈念sama閱讀 38,882評論 3贊 404
雙鴛鴦連環(huán)套：你想象不到人心有多黑
文/蒼蘭香墨我猛地睜開眼贸营，長吁一口氣：“原來是場噩夢啊……” “哼！你這毒婦竟也來了岩睁？” 一聲冷哼從身側(cè)響起钞脂，我...
開封第一講書人閱讀 37,643評論 0贊 266
萬榮殺人案實(shí)錄
序言：老撾萬榮一對情侶失蹤，失蹤者是張志新（化名）和其女友劉穎捕儒，沒想到半個(gè)月后冰啃，有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體，經(jīng)...
沈念sama閱讀 44,095評論 1贊 303
?護(hù)林員之死
正文獨(dú)居荒郊野嶺守林人離奇死亡刘莹，尸身上長有42處帶血的膿包…… 初始之章·張勛以下內(nèi)容為張勛視角年9月15日...
茶點(diǎn)故事閱讀 36,448評論 2贊 325
?白月光啟示錄
正文我和宋清朗相戀三年阎毅，在試婚紗的時(shí)候發(fā)現(xiàn)自己被綠了。大學(xué)時(shí)的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片点弯。...
茶點(diǎn)故事閱讀 38,566評論 1贊 339
活死人
序言：一個(gè)原本活蹦亂跳的男人離奇死亡扇调，死狀恐怖，靈堂內(nèi)的尸體忽然破棺而出抢肛，到底是詐尸還是另有隱情狼钮，我是刑警寧澤碳柱，帶...
沈念sama閱讀 34,253評論 4贊 328
?日本核電站爆炸內(nèi)幕
正文年R本政府宣布，位于F島的核電站熬芜，受9級特大地震影響士聪，放射性物質(zhì)發(fā)生泄漏。R本人自食惡果不足惜猛蔽，卻給世界環(huán)境...
茶點(diǎn)故事閱讀 39,829評論 3贊 312
男人毒藥：我在死后第九天來索命
文/蒙蒙一、第九天我趴在偏房一處隱蔽的房頂上張望灵寺。院中可真熱鬧曼库，春花似錦、人聲如沸略板。這莊子的主人今日做“春日...
開封第一講書人閱讀 30,715評論 0贊 21
一樁弒父案，背后竟有這般陰謀
文/蒼蘭香墨我抬頭看了看天上的太陽叮称。三九已至种玛，卻和暖如春，著一層夾襖步出監(jiān)牢的瞬間瓤檐，已是汗流浹背赂韵。一陣腳步聲響...
開封第一講書人閱讀 31,945評論 1贊 264
情欲美人皮
我被黑心中介騙來泰國打工，沒想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留挠蛉，地道東北人祭示。一個(gè)月前我還...
沈念sama閱讀 46,248評論 2贊 360
代替公主和親
正文我出身青樓，卻偏偏與公主長得像谴古，于是被迫代替她去往敵國和親质涛。傳聞我的和親對象是個(gè)殘疾皇子，可洞房花燭夜當(dāng)晚...
茶點(diǎn)故事閱讀 43,440評論 2贊 348