ShardingSphere-JDBC 5.2.0 在linux与window下查询结果不一致

wangjie · 2023 年3 月 3 日 08:56

使用环境：

通过Spring 命名空间方式集成了ShardingSphere-JDBC 5.2.0

环境1：
windows7 MySQL5.7.30

环境2：

MySQL 5.7.30

场景、问题：

执行如下SQL，查询结果不一致，实际数据库查询结果为：10275

已进行操作：

环境1：

2023-03-03 16:48:24.429 [pool-10-thread-1] [INFO]-[ShardingSphere-SQL] Logic SQL: SELECT COUNT(m.id) AS COUNT_R FROM model_dict m INNER JOIN model_dict_tag t ON m.id = t.base_dictionary_id WHERE t.val in ('介入治疗','手术') AND m.is_deleted = 0
2023-03-03 16:48:24.430 [pool-10-thread-1] [INFO]-[ShardingSphere-SQL] SQLStatement: MySQLSelectStatement(super=SelectStatement(super=AbstractSQLStatement(parameterCount=0, parameterMarkerSegments=[], commentSegments=[]), projections=ProjectionsSegment(startIndex=7, stopIndex=28, projections=[AggregationProjectionSegment(startIndex=7, stopIndex=17, type=COUNT, innerExpression=(m.id), parameters=[ColumnSegment(startIndex=13, stopIndex=16, identifier=IdentifierValue(value=id, quoteCharacter=NONE), owner=Optional[OwnerSegment(startIndex=13, stopIndex=13, identifier=IdentifierValue(value=m, quoteCharacter=NONE), owner=Optional.empty)])], alias=Optional[COUNT_R])], distinctRow=false), from=JoinTableSegment(startIndex=35, stopIndex=105, alias=Optional.empty, left=SimpleTableSegment(tableName=TableNameSegment(startIndex=35, stopIndex=44, identifier=IdentifierValue(value=model_dict, quoteCharacter=NONE)), owner=Optional.empty, alias=Optional[m]), joinType=INNER, right=SimpleTableSegment(tableName=TableNameSegment(startIndex=59, stopIndex=72, identifier=IdentifierValue(value=model_dict_tag, quoteCharacter=NONE)), owner=Optional.empty, alias=Optional[t]), condition=BinaryOperationExpression(startIndex=79, stopIndex=105, left=ColumnSegment(startIndex=79, stopIndex=82, identifier=IdentifierValue(value=id, quoteCharacter=NONE), owner=Optional[OwnerSegment(startIndex=79, stopIndex=79, identifier=IdentifierValue(value=m, quoteCharacter=NONE), owner=Optional.empty)]), right=ColumnSegment(startIndex=86, stopIndex=105, identifier=IdentifierValue(value=base_dictionary_id, quoteCharacter=NONE), owner=Optional[OwnerSegment(startIndex=86, stopIndex=86, identifier=IdentifierValue(value=t, quoteCharacter=NONE), owner=Optional.empty)]), operator==, text=m.id = t.base_dictionary_id), using=[]), where=Optional[WhereSegment(startIndex=107, stopIndex=155, expr=BinaryOperationExpression(startIndex=113, stopIndex=155, left=InExpression(startIndex=113, stopIndex=134, left=ColumnSegment(startIndex=113, stopIndex=117, identifier=IdentifierValue(value=val, quoteCharacter=NONE), owner=Optional[OwnerSegment(startIndex=113, stopIndex=113, identifier=IdentifierValue(value=t, quoteCharacter=NONE), owner=Optional.empty)]), right=ListExpression(startIndex=122, stopIndex=134, items=[LiteralExpressionSegment(startIndex=123, stopIndex=128, literals=介入治疗), LiteralExpressionSegment(startIndex=130, stopIndex=133, literals=手术)]), not=false), right=BinaryOperationExpression(startIndex=140, stopIndex=155, left=ColumnSegment(startIndex=140, stopIndex=151, identifier=IdentifierValue(value=is_deleted, quoteCharacter=NONE), owner=Optional[OwnerSegment(startIndex=140, stopIndex=140, identifier=IdentifierValue(value=m, quoteCharacter=NONE), owner=Optional.empty)]), right=LiteralExpressionSegment(startIndex=155, stopIndex=155, literals=0), operator==, text=m.is_deleted = 0), operator=AND, text=t.val in ('介入治疗','手术') AND m.is_deleted = 0))], groupBy=Optional.empty, having=Optional.empty, orderBy=Optional.empty, combines=[]), table=Optional.empty, limit=Optional.empty, lock=Optional.empty, window=Optional.empty)
2023-03-03 16:48:24.430 [pool-10-thread-1] [INFO]-[ShardingSphere-SQL] Actual SQL: ds ::: SELECT COUNT(m.id) AS COUNT_R FROM model_dict m INNER JOIN model_dict_tag t ON m.id = t.base_dictionary_id WHERE t.val in ('介入治疗','手术') AND m.is_deleted = 0
2023-03-03 16:48:24.687 [pool-10-thread-1] [INFO]-[org.***.***.dao.******DaoImpl] ------------------------------:10275

环境2：

2023-03-03 16:37:48.403 [pool-10-thread-2] [INFO]-[ShardingSphere-SQL] Logic SQL: SELECT COUNT(m.id) AS COUNT_R FROM model_dict m INNER JOIN model_dict_tag t ON m.id = t.base_dictionary_id WHERE t.val in ('介入治疗','手术') AND m.is_deleted = 0
2023-03-03 16:37:48.404 [pool-10-thread-2] [INFO]-[ShardingSphere-SQL] SQLStatement: MySQLSelectStatement(super=SelectStatement(super=AbstractSQLStatement(parameterCount=0, parameterMarkerSegments=[], commentSegments=[]), projections=ProjectionsSegment(startIndex=7, stopIndex=28, projections=[AggregationProjectionSegment(startIndex=7, stopIndex=17, type=COUNT, innerExpression=(m.id), parameters=[ColumnSegment(startIndex=13, stopIndex=16, identifier=IdentifierValue(value=id, quoteCharacter=NONE), owner=Optional[OwnerSegment(startIndex=13, stopIndex=13, identifier=IdentifierValue(value=m, quoteCharacter=NONE), owner=Optional.empty)])], alias=Optional[COUNT_R])], distinctRow=false), from=JoinTableSegment(startIndex=35, stopIndex=105, alias=Optional.empty, left=SimpleTableSegment(tableName=TableNameSegment(startIndex=35, stopIndex=44, identifier=IdentifierValue(value=model_dict, quoteCharacter=NONE)), owner=Optional.empty, alias=Optional[m]), joinType=INNER, right=SimpleTableSegment(tableName=TableNameSegment(startIndex=59, stopIndex=72, identifier=IdentifierValue(value=model_dict_tag, quoteCharacter=NONE)), owner=Optional.empty, alias=Optional[t]), condition=BinaryOperationExpression(startIndex=79, stopIndex=105, left=ColumnSegment(startIndex=79, stopIndex=82, identifier=IdentifierValue(value=id, quoteCharacter=NONE), owner=Optional[OwnerSegment(startIndex=79, stopIndex=79, identifier=IdentifierValue(value=m, quoteCharacter=NONE), owner=Optional.empty)]), right=ColumnSegment(startIndex=86, stopIndex=105, identifier=IdentifierValue(value=base_dictionary_id, quoteCharacter=NONE), owner=Optional[OwnerSegment(startIndex=86, stopIndex=86, identifier=IdentifierValue(value=t, quoteCharacter=NONE), owner=Optional.empty)]), operator==, text=m.id = t.base_dictionary_id), using=[]), where=Optional[WhereSegment(startIndex=107, stopIndex=155, expr=BinaryOperationExpression(startIndex=113, stopIndex=155, left=InExpression(startIndex=113, stopIndex=134, left=ColumnSegment(startIndex=113, stopIndex=117, identifier=IdentifierValue(value=val, quoteCharacter=NONE), owner=Optional[OwnerSegment(startIndex=113, stopIndex=113, identifier=IdentifierValue(value=t, quoteCharacter=NONE), owner=Optional.empty)]), right=ListExpression(startIndex=122, stopIndex=134, items=[LiteralExpressionSegment(startIndex=123, stopIndex=128, literals=介入治疗), LiteralExpressionSegment(startIndex=130, stopIndex=133, literals=手术)]), not=false), right=BinaryOperationExpression(startIndex=140, stopIndex=155, left=ColumnSegment(startIndex=140, stopIndex=151, identifier=IdentifierValue(value=is_deleted, quoteCharacter=NONE), owner=Optional[OwnerSegment(startIndex=140, stopIndex=140, identifier=IdentifierValue(value=m, quoteCharacter=NONE), owner=Optional.empty)]), right=LiteralExpressionSegment(startIndex=155, stopIndex=155, literals=0), operator==, text=m.is_deleted = 0), operator=AND, text=t.val in ('介入治疗','手术') AND m.is_deleted = 0))], groupBy=Optional.empty, having=Optional.empty, orderBy=Optional.empty, combines=[]), table=Optional.empty, limit=Optional.empty, lock=Optional.empty, window=Optional.empty)
2023-03-03 16:37:48.404 [pool-10-thread-2] [INFO]-[ShardingSphere-SQL] Actual SQL: ds ::: SELECT COUNT(m.id) AS COUNT_R FROM model_dict m INNER JOIN model_dict_tag t ON m.id = t.base_dictionary_id WHERE t.val in ('介入治疗','手术') AND m.is_deleted = 0
2023-03-03 16:37:48.514 [pool-10-thread-2] [INFO]-[org.***.***.dao.******DaoImpl] ------------------------------:0

wangjie · 2023 年3 月 6 日 03:51

我发现在Linux MySQL下的ShardingSphere-JDBC如果查询条件带中文，则都无法查出来，这是什么原因导致的呢？
表实际数据如下：
A表：

B表：

英文条件时输出日志如下：

2023-03-06 11:53:23.142 [http-nio-8088-exec-43] [DEBUG]-[org.hyena.ims.dao.FtnDataCenterDaoImpl] Executing SQL query [SELECT COUNT(*) AS COUNT_R FROM A
WHERE A.username = 'jack']
2023-03-06 11:53:23.145 [http-nio-8088-exec-43] [INFO]-[ShardingSphere-SQL] Logic SQL: SELECT COUNT(*) AS COUNT_R FROM A
WHERE A.username = 'jack'
2023-03-06 11:53:23.147 [http-nio-8088-exec-43] [INFO]-[ShardingSphere-SQL] SQLStatement: MySQLSelectStatement(super=SelectStatement(super=AbstractSQLStatement(parameterCount=0, parameterMarkerSegments=[], commentSegments=[]), projections=ProjectionsSegment(startIndex=7, stopIndex=25, projections=[AggregationProjectionSegment(startIndex=7, stopIndex=14, type=COUNT, innerExpression=(*), parameters=[], alias=Optional[COUNT_R])], distinctRow=false), from=SimpleTableSegment(tableName=TableNameSegment(startIndex=32, stopIndex=32, identifier=IdentifierValue(value=A, quoteCharacter=NONE)), owner=Optional.empty, alias=Optional.empty), where=Optional[WhereSegment(startIndex=35, stopIndex=59, expr=BinaryOperationExpression(startIndex=41, stopIndex=59, left=ColumnSegment(startIndex=41, stopIndex=50, identifier=IdentifierValue(value=username, quoteCharacter=NONE), owner=Optional[OwnerSegment(startIndex=41, stopIndex=41, identifier=IdentifierValue(value=A, quoteCharacter=NONE), owner=Optional.empty)]), right=LiteralExpressionSegment(startIndex=54, stopIndex=59, literals=jack), operator==, text=A.username = 'jack'))], groupBy=Optional.empty, having=Optional.empty, orderBy=Optional.empty, combines=[]), table=Optional.empty, limit=Optional.empty, lock=Optional.empty, window=Optional.empty)
2023-03-06 11:53:23.147 [http-nio-8088-exec-43] [INFO]-[ShardingSphere-SQL] Actual SQL: ds ::: SELECT COUNT(*) AS COUNT_R FROM A
WHERE A.username = 'jack'
[{"COUNT_R":1}]

中文条件时输出日志如下：

2023-03-06 11:46:29.056 [http-nio-8088-exec-22] [DEBUG]-[org.hyena.ims.dao.FtnDataCenterDaoImpl] Executing SQL query [SELECT COUNT(*) AS COUNT_R FROM A
WHERE A.username = '张三']
2023-03-06 11:46:29.058 [http-nio-8088-exec-22] [INFO]-[ShardingSphere-SQL] Logic SQL: SELECT COUNT(*) AS COUNT_R FROM A
WHERE A.username = '张三'
2023-03-06 11:46:29.058 [http-nio-8088-exec-22] [INFO]-[ShardingSphere-SQL] SQLStatement: MySQLSelectStatement(super=SelectStatement(super=AbstractSQLStatement(parameterCount=0, parameterMarkerSegments=[], commentSegments=[]), projections=ProjectionsSegment(startIndex=7, stopIndex=25, projections=[AggregationProjectionSegment(startIndex=7, stopIndex=14, type=COUNT, innerExpression=(*), parameters=[], alias=Optional[COUNT_R])], distinctRow=false), from=SimpleTableSegment(tableName=TableNameSegment(startIndex=32, stopIndex=32, identifier=IdentifierValue(value=A, quoteCharacter=NONE)), owner=Optional.empty, alias=Optional.empty), where=Optional[WhereSegment(startIndex=35, stopIndex=57, expr=BinaryOperationExpression(startIndex=41, stopIndex=57, left=ColumnSegment(startIndex=41, stopIndex=50, identifier=IdentifierValue(value=username, quoteCharacter=NONE), owner=Optional[OwnerSegment(startIndex=41, stopIndex=41, identifier=IdentifierValue(value=A, quoteCharacter=NONE), owner=Optional.empty)]), right=LiteralExpressionSegment(startIndex=54, stopIndex=57, literals=张三), operator==, text=A.username = '张三'))], groupBy=Optional.empty, having=Optional.empty, orderBy=Optional.empty, combines=[]), table=Optional.empty, limit=Optional.empty, lock=Optional.empty, window=Optional.empty)
2023-03-06 11:46:29.058 [http-nio-8088-exec-22] [INFO]-[ShardingSphere-SQL] Actual SQL: ds ::: SELECT COUNT(*) AS COUNT_R FROM A
WHERE A.username = '张三'
[{"COUNT_R":0}]

实际查询结果如下：
英文条件：

中文条件：

wangjie · 2023 年3 月 7 日 02:06

问题已经解决。
出现该问题的原因是linux服务器中mysql编码为latin1，而window下当时mysql编码为utf8
同时的数据表中设置的编码为utf8，导致查询失败。

但是我很好奇明明我在数据源配置的URL上设置了参数characterEncoding=UTF-8(如下所属)，但是却没有效果，导致这个问题查了很久，这是否是ShardingSphere-JDBC的一个缺陷？

ds0.url=jdbc:mysql://127.0.0.1:3306/mdc?useSSL=false&amp;useUnicode=true&amp;characterEncoding=UTF-8&amp;rewriteBatchedStatements=true