是否支持mysql JSON_VALUE(json_doc, path)语法

Apache ShardingSphere 提问
#1

方便更快捷的说明问题，可以按需填写（可删除）

shardingsphere-jdbc 目前是否支持mysql JSON_VALUE(json_doc, path)语法

  • mysql json_value()语法：

https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html#function_json-value

使用环境：

数据库： 腾讯云mysql （8.0.22-txsql）

--水平分表

CREATE TABLE inventory_0(
    id int,
    items JSON,
    INDEX i1 ( (JSON_VALUE(items, '$.name' RETURNING CHAR(50))) )
);

CREATE TABLE inventory_1(
    id int,
    items JSON,
    INDEX i1 ( (JSON_VALUE(items, '$.price' RETURNING DECIMAL(5,2))) )
);

应用： springBoot + mybatis-plus (3.3.0) + shardingsphere-jdbc (5.0.0)

  • 配置
server:
  port: 8088
spring:
  shardingsphere:
    # 属性配置
    props:
      sql-show: true # 是否开启SQL显示
    # 数据源配置
    datasource:
      name: ds0
      ds0:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://******:3306/test?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai&useSSL=false
        username: ******
        password: ******
    rules:
      sharding:
        # 分片表配置
        tables:
          inventory: # 逻辑表名
            actual-data-nodes: ds0.inventory_$->{0..1} # 实际数据节点
            # 分表策略
            table-strategy:
              standard:
                sharding-column: id
                sharding-algorithm-name: table-inline
        # 分片算法配置
        sharding-algorithms:
          table-inline:
            type: INLINE # 分片算法类型
            props:
              algorithm-expression: inventory_$->{id % 2}  # 分片算法的行表达式

场景、问题：

正常测试用例

//SELECT  * FROM inventory  WHERE id = 2
    @Test
    public void noJsonSelect2() {
        System.out.println(("----- noJsonSelect method test ------"));
        QueryWrapper queryWrapper = new QueryWrapper();
        queryWrapper.eq("id", 2);
        List<inventory> inventoryList = InventoryMapper.selectList(queryWrapper);
        inventoryList.forEach(System.out::println);
    }
  • 日志
2022-02-08 19:11:37.434  INFO 80159 --- [           main] ShardingSphere-SQL                       : Logic SQL: SELECT  id,items  FROM inventory 
 
 WHERE (id = ?)
2022-02-08 19:11:37.436  INFO 80159 --- [           main] ShardingSphere-SQL                       : SQLStatement: MySQLSelectStatement(limit=Optional.empty, lock=Optional.empty, window=Optional.empty)
2022-02-08 19:11:37.436  INFO 80159 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds0 ::: SELECT  id,items  FROM inventory_0 
 
 WHERE (id = ?) ::: [2]

JSON_VALUE语法测试用例

//SELECT  * FROM inventory  WHERE JSON_VALUE ( items, '$.name' RETURNING CHAR ( 50 ) ) = "hat"
    @Test
    public void jsonSelect() {
        System.out.println(("----- jsonSelect method test ------"));
        QueryWrapper queryWrapper = new QueryWrapper();
        queryWrapper.eq("JSON_VALUE ( items, '$.name' RETURNING CHAR ( 50 ) )", "hat");
        List<inventory> inventoryList = InventoryMapper.selectList(queryWrapper);
        inventoryList.forEach(System.out::println);
    }
  • 报错日志
----- jsonSelect method test ------

org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.executor.ExecutorException: Error preparing statement.  Cause: com.google.common.util.concurrent.UncheckedExecutionException: org.apache.shardingsphere.sql.parser.exception.SQLParsingException: You have an error in your SQL syntax

	at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:92)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:440)
	at com.sun.proxy.$Proxy212.selectList(Unknown Source)
	at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:223)
	at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.executeForMany(MybatisMapperMethod.java:177)
	at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:78)
	at com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:96)
	at com.sun.proxy.$Proxy217.selectList(Unknown Source)
	at com.example.jsonnotes.ShardingJdbcTest.jsonSelect(ShardingJdbcTest.java:50)
	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.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:59)
	at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
	at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:56)
	at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
	at org.springframework.test.context.junit4.statements.RunBeforeTestExecutionCallbacks.evaluate(RunBeforeTestExecutionCallbacks.java:74)
	at org.springframework.test.context.junit4.statements.RunAfterTestExecutionCallbacks.evaluate(RunAfterTestExecutionCallbacks.java:84)
	at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:75)
	at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:86)
	at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:84)
	at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:366)
	at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:251)
	at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:97)
	at org.junit.runners.ParentRunner$4.run(ParentRunner.java:331)
	at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:79)
	at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:329)
	at org.junit.runners.ParentRunner.access$100(ParentRunner.java:66)
	at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:293)
	at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
	at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:70)
	at org.junit.runners.ParentRunner$3.evaluate(ParentRunner.java:306)
	at org.junit.runners.ParentRunner.run(ParentRunner.java:413)
	at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:190)
	at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
	at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:69)
	at com.intellij.rt.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:33)
	at com.intellij.rt.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:235)
	at com.intellij.rt.junit.JUnitStarter.main(JUnitStarter.java:54)
Caused by: org.apache.ibatis.executor.ExecutorException: Error preparing statement.  Cause: com.google.common.util.concurrent.UncheckedExecutionException: org.apache.shardingsphere.sql.parser.exception.SQLParsingException: You have an error in your SQL syntax
	at org.apache.ibatis.executor.statement.BaseStatementHandler.prepare(BaseStatementHandler.java:97)
	at org.apache.ibatis.executor.statement.RoutingStatementHandler.prepare(RoutingStatementHandler.java:59)
	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.Invocation.proceed(Invocation.java:49)
	at com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor.intercept(PaginationInterceptor.java:186)
	at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61)
	at com.sun.proxy.$Proxy228.prepare(Unknown Source)
	at com.baomidou.mybatisplus.core.executor.MybatisSimpleExecutor.prepareStatement(MybatisSimpleExecutor.java:92)
	at com.baomidou.mybatisplus.core.executor.MybatisSimpleExecutor.doQuery(MybatisSimpleExecutor.java:66)
	at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:324)
	at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)
	at com.baomidou.mybatisplus.core.executor.MybatisCachingExecutor.query(MybatisCachingExecutor.java:155)
	at com.baomidou.mybatisplus.core.executor.MybatisCachingExecutor.query(MybatisCachingExecutor.java:90)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:147)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140)
	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:426)
	... 38 more
Caused by: com.google.common.util.concurrent.UncheckedExecutionException: org.apache.shardingsphere.sql.parser.exception.SQLParsingException: You have an error in your SQL syntax
	at com.google.common.cache.LocalCache$Segment.get(LocalCache.java:2051)
	at com.google.common.cache.LocalCache.get(LocalCache.java:3951)
	at com.google.common.cache.LocalCache.getOrLoad(LocalCache.java:3974)
	at com.google.common.cache.LocalCache$LocalLoadingCache.get(LocalCache.java:4958)
	at com.google.common.cache.LocalCache$LocalLoadingCache.getUnchecked(LocalCache.java:4964)
	at org.apache.shardingsphere.infra.parser.sql.SQLStatementParserEngine.parse(SQLStatementParserEngine.java:48)
	at org.apache.shardingsphere.infra.parser.ShardingSphereSQLParserEngine.parse0(ShardingSphereSQLParserEngine.java:71)
	at org.apache.shardingsphere.infra.parser.ShardingSphereSQLParserEngine.parse(ShardingSphereSQLParserEngine.java:60)
	at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.<init>(ShardingSpherePreparedStatement.java:157)
	at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.<init>(ShardingSpherePreparedStatement.java:128)
	at org.apache.shardingsphere.driver.jdbc.core.connection.ShardingSphereConnection.prepareStatement(ShardingSphereConnection.java:78)
	at org.apache.ibatis.executor.statement.PreparedStatementHandler.instantiateStatement(PreparedStatementHandler.java:86)
	at org.apache.ibatis.executor.statement.BaseStatementHandler.prepare(BaseStatementHandler.java:88)
	... 60 more
Caused by: org.apache.shardingsphere.sql.parser.exception.SQLParsingException: You have an error in your SQL syntax
	at org.apache.shardingsphere.sql.parser.core.database.parser.SQLParserExecutor.twoPhaseParse(SQLParserExecutor.java:68)
	at org.apache.shardingsphere.sql.parser.core.database.parser.SQLParserExecutor.parse(SQLParserExecutor.java:49)
	at org.apache.shardingsphere.sql.parser.api.SQLParserEngine.parse(SQLParserEngine.java:51)
	at org.apache.shardingsphere.infra.parser.sql.SQLStatementParserExecutor.parse(SQLStatementParserExecutor.java:47)
	at org.apache.shardingsphere.infra.parser.cache.SQLStatementCacheLoader.load(SQLStatementCacheLoader.java:40)
	at org.apache.shardingsphere.infra.parser.cache.SQLStatementCacheLoader.load(SQLStatementCacheLoader.java:29)
	at com.google.common.cache.LocalCache$LoadingValueReference.loadFuture(LocalCache.java:3529)
	at com.google.common.cache.LocalCache$Segment.loadSync(LocalCache.java:2278)
	at com.google.common.cache.LocalCache$Segment.lockedGetOrLoad(LocalCache.java:2155)
	at com.google.common.cache.LocalCache$Segment.get(LocalCache.java:2045)
	... 72 more

2022-02-08 19:15:24.642  INFO 80540 --- [extShutdownHook] com.alibaba.druid.pool.DruidDataSource   : {dataSource-1} closing ...
2022-02-08 19:15:24.654  INFO 80540 --- [extShutdownHook] com.alibaba.druid.pool.DruidDataSource   : {dataSource-1} closed
2022-02-08 19:15:24.655  INFO 80540 --- [extShutdownHook] o.s.s.concurrent.ThreadPoolTaskExecutor  : Shutting down ExecutorService 'applicationTaskExecutor'

已进行操作：

无

现状：

#2

@cosmoxu 感谢反馈，我们优化下

#3

我在本地修复了这个问题，是 MySQL JSON 语法解析导致的报错，修复完路由结果如下：

[INFO ] 2022-02-09 14:08:03.073 [Connection-1-ThreadExecutor] ShardingSphere-SQL - Logic SQL: SELECT  * FROM inventory  WHERE JSON_VALUE ( items, '$.name' RETURNING CHAR ( 50 ) ) = "hat"
[INFO ] 2022-02-09 14:08:03.073 [Connection-1-ThreadExecutor] ShardingSphere-SQL - SQLStatement: MySQLSelectStatement(table=Optional.empty, limit=Optional.empty, lock=Optional.empty, window=Optional.empty)
[INFO ] 2022-02-09 14:08:03.073 [Connection-1-ThreadExecutor] ShardingSphere-SQL - Actual SQL: ds_0 ::: SELECT  * FROM inventory_0  WHERE JSON_VALUE ( items, '$.name' RETURNING CHAR ( 50 ) ) = "hat" UNION ALL SELECT  * FROM inventory_1  WHERE JSON_VALUE ( items, '$.name' RETURNING CHAR ( 50 ) ) = "hat"
1 个赞
#4

关联 issue——https://github.com/apache/shardingsphere/issues/15311.

京ICP备2021015875号