By Interval Sharding Algorithm, the data is not appropriately fragmented.

[tianxin8206]

ShardingSphere : 5.5 Spring Boot : 2.7.18

My configurations: sharding.yml

databaseName: person

mode:
  type: Standalone
  repository:
    type: JDBC

dataSources:
  person:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.cj.jdbc.Driver
    jdbcUrl: jdbc:mysql://xxx/person?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&useSSL=false&serverTimezone=Asia/Shanghai
    username: xxx
    password: xxx

rules:
  - !SHARDING
    shardingAlgorithms:
      ps_t_history_result_interval:
        type: INTERVAL
        props:
          datetime-pattern: yyyy-MM-dd HH:mm:ss
          datetime-lower: '2024-09-01 00:00:00'
          sharding-suffix-pattern: yyyyMM
          datetime-interval-unit: MONTHS
    tables:
      ps_t_history_result:
        actualDataNodes: person.ps_t_history_result${202409..205012}
        tableStrategy:
          standard:
            shardingColumn: ps_mine_entertime
            shardingAlgorithmName: ps_t_history_result_interval
    bindingTables:
      - ps_t_history_result
  - !SINGLE
    tables:
      - "person.*"

application.yml

spring:
  datasource:
    dynamic:
      primary: jy_eadp_mkwszc_cumt
      datasource:
        jy_eadp_mkwszc_cumt:
          driver-class-name: com.mysql.cj.jdbc.Driver
        person:
          driver-class-name: org.apache.shardingsphere.driver.ShardingSphereDriver
          url: jdbc:shardingsphere:classpath:sharding.yml

stack

nested exception is org.apache.ibatis.executor.ExecutorException: Error preparing statement.  Cause: org.apache.shardingsphere.infra.exception.kernel.metadata.TableNotFoundException: Table or view 'ps_t_history_result_0' does not exist.

The SQL statements generated by the Mybatis Plus:

SELECT
  cs_mine_code AS orgCode,
  cs_mine_shortname AS orgName,
  ps_person_card AS card,
  ps_person_name AS NAME,
  ps_person_post AS post,
  ps_person_duty AS duty,
  ps_person_dept AS dept,
  ps_class_code AS classCode,
  ps_mine_entertime AS mineEnterTime,
  ps_mine_outtime AS mineOutTime,
  ps_enter_duration AS enterDuration,
  ps_is_lose AS isLose,
  ps_station_name AS stationName,
  ps_station_code AS stationCode,
  ps_station_location AS stationLocation,
  ps_station_entertime AS stationEnterTime,
  ps_area_code AS areaCode,
  ps_area_name AS areaName,
  ps_area_entertime AS areaEnterTime,
  ps_position_x AS positionX,
  ps_position_y AS positionY,
  ps_position_z AS positionZ,
  ps_station_distance AS stationDistance,
  client_time,
  land_time 
FROM
  ps_t_history_result_0 
WHERE
  (
    cs_mine_code = '006070021'
    AND ps_station_code = '000311'
    AND ps_area_code = '0019'
    AND ( ps_mine_entertime BETWEEN '2024-10-16 08:03:16' AND '2024-10-16 23:56:29' ) 
  ) 
ORDER BY
  ps_mine_entertime DESC

Why are the sharding rules not functioning properly?

[terrymanu]

Is 'ps_t_history_result_0' existing in database?

[tianxin8206]

@terrymanu 'ps_t_history_result_0' does not exist. My tables looks like this:

[terrymanu]

Your sharding rule is configured for ps_t_history_result, but your SQL query is targeting ps_t_history_result_0.

[tianxin8206]

@terrymanu Yes. Based on my configuration, the right execution should be "ps_t_history_result202410", but the SQL is "ps_t_history_result_0". I have no idea what caused this SQL error.