Oracle订单由不同 [英] Oracle order by different

问题描述

我在Oracle数据库中有一个这样的表

I have a table in an Oracle Database like this

ID   | LABEL
------------
 1   |  label alpha 1
 2   |  label alpha 2
 3   |  label alpha a

当我在像Squirrel这样的应用程序中进行选择时:

when I do a select in an application like Squirrel like this :

select * FROM MA_TABLE order by LABEL asc

我得到了:

ID   | LABEL
------------
 1   |  label alpha 1
 2   |  label alpha 2
 3   |  label alpha a

很好！

但是当我使用MyBatis执行相同的请求时:

but When I execute the same request using MyBatis :

<select id="selectMaTable" resultMap="resultMap" >
  Select * FROM MA_TABLE order by LABEL asc
</select>

我得到了:

ID   | LABEL
------------
 3   |  label alpha a
 1   |  label alpha 1
 2   |  label alpha 2

字母字符位于数字字符之前...为什么??

The alphabetic characters come before the numeric characters... why ??

预先感谢

Antoine

Ps:我正在使用org.mybatis:mybatis:jar:3.0.5和com.oracle:ojdbc6:jar:11.2.0.2.0进行数据库访问

Ps : I am using org.mybatis:mybatis:jar:3.0.5 and com.oracle:ojdbc6:jar:11.2.0.2.0 for database access

此链接也对我有帮助

多亏了Soulcheck的一句话，我发现如果我使用ORDER BY NLSSORT(ATL_SIT.ATL_SIT_LIB，'NLS_SORT = BINARY')更改order by子句，那么它可以工作...

Thanks to Soulcheck's remark, I found that if I change the order by clause with ORDER BY NLSSORT(ATL_SIT.ATL_SIT_LIB, 'NLS_SORT=BINARY') it works...

有人知道如何用myBatis强制NLS_SORT = BINARY吗? (它已经在我的Oracle数据库中的NLS_DATABASE_PARAMETERS中设置了)

Does anyone knows how to force NLS_SORT=BINARY with myBatis ? (It is already set on my Oracle database in NLS_DATABASE_PARAMETERS)

推荐答案

似乎是语言环境问题.与松鼠连接并检查设置的NLS_LANG，然后检查使用Java应用程序的语言环境.另一个可能影响排序的参数是NLS_SORT.

It looks like it might be a locale issue. Connect with squirrel and check what NLS_LANG it sets, then check what locale uses your java app. Another parameter that can influence sort is NLS_SORT.

您可以通过发出以下命令来检查两者的值:

You can check the value of both by issuing:

select parameter,value from NLS_DATABASE_PARAMETERS where parameter in('NLS_LANGUAGE','NLS_SORT');

然后您可以使用以下命令在Java中对其进行测试:

Then you can test it in java by using:

Locale.getDefault()

并通过以下方式进行设置:

and set it by using:

Locale.setDefault(Locale)

或通过添加jvm参数:

or by adding jvm parameters:

-Duser.country=en -Duser.language=en

修改

春季论坛建议创建一个登录触发器，该触发器在用户登录时设置NLS_SORT环境变量.不是MyBatis，而是jdbc，因此应该适合您的情况.

spring forums recommend creating a logon trigger which sets NLS_SORT environmental variable on user logon. It's not MyBatis, but jdbc anyway so should work in your case.

这篇关于Oracle订单由不同的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！