前几天同事问到如何能使oracle中的查询大小写不敏感,为此我特意查阅了一下oracle的官方文档.总结如下:
首先,oracle中默认排序和对比列值时是区分大小写的，这点与mysql可不一样!!!
大小写不敏感的查询
- 在数据库中可以通过语句:ALTER SESSION SET NLS_COMP = LINGUISTIC; 来使查询时,列的对比基于语义对比,会忽略大小写.但是,从执行计划中可以看到这样的信息"filter(NLSSORT(要查询的列,'nls_sort=''BINARY_CI''')=HEXTORAW(某数值)".这说明,更改了对比方式后，oracle会对这个列进行函数操作.意味着这个列上的索引会失效.当然解决方法也很直观,就对这个列建立函数索引即可:CREATE INDEX 索引名 ON T(NLSSORT(要索引的列名, 'NLS_SORT=BINARY_CI')); 索引建立后，再看执行计划，上述信息就变成了"access(NLSSORT(要查询的列,'nls_sort=''BINARY_CI''')=HEXTORAW(某数值)".这点要注意哦，不然可是全表扫描，很可能会死人的....
- 我们在项目中当然不会向上面那样操作咯.我们是使用JDBC.这时可以这样操作: connection.createStatement().execute("alter session set nls_comp=LINGUISTIC"); 这时你需要能修改session的权限.在ibatis里,可以直接调用execute执行alter语句.使用spring的JdbcTemplate时,除了直接执行这个语句外，还可以实现StatementCallback . 但是要注意，如果使用的是连接池,请注意使用完后修改回来，否则,这个session会被重复使用，后续的使用者将受此影响.
- 而在hibernate中，一般可以通过将查询条件全部转换为大写或者小写来变相的实现这一目的.
- 如果是HQL,使用oracle的LOWER()或UPPER()函数.例如: select ...... where LOWER(name) = key.toLowerCase();
- 如果是使用Criteria,则可以直接使用ignoreCase()或ilike()方法(注意，这个方法在like前面多了个i).例如:
- Restrictions.eq('colname','value′).ignoreCase()
- Restrictions.ilike('colname','value′)
- Restrictions.like('colname','value′).ignoreCase().
- 这里要注意索引的问题了，有必要的话，需要建立函数索引.
大小写不敏感的排序
- 在数据库中可以通过语句:ALTER SESSION SET NLS_SORT = BINARY_CI;索引方面同上.
- 在项目中使用同上.
以上alter session的方式都可以改为alter system的方式对全部session生效.但是务必要注意性能问题.尤其是不敏感大小写后，oracle实际增加了函数操作,导致索引无效的问题
