如何检查(甚至设置)MDB(MS Access)文件中的排序规则? [英] How to check (or even set) collation in mdb (ms access) file?

问题描述

我成功地将Access DB迁移到MySQL DB.一切似乎都很好，但是我需要更具体的MySQL DB行为.
选择选项将A到Z排序" 后，我需要与Access DB完全相同的查询结果排序.
我在Java应用程序中使用此结果.

I succesfully migrated Access DB to MySQL DB. All seems to be fine but i need more specific behaviour of MySQL DB.
I need exactly the same sorting of query result as in Access DB after choose option "sort A to Z".
I use this result in Java application.

MySQL表为UTF-8，并且排序规则设置为utf8_general_ci.
查询示例为:

MySQL table is UTF-8 and collation is set utf8_general_ci.
Example query is:

"SELECT encode, language FROM suffixes ORDER BY encode COLLATE utf8_unicode_ci"

它返回相似的结果，但是排序上存在无法接受的差异.

It returns similar result but there are unacceptable differences in sorting.

访问排序结果如下:

001_01._02.1_02.2.1_02.4_05.e.3.1_07.2.2_15.5.d_20.3.2.1_31.2.2_33.3.4_001
001_01._02.1_02.4.1_06.4.1_06.4.2_07.2.1.1_07.2.2_10.1_11.1.3_20.3.2.1_20.3.7_20.6.8_001

Java对结果进行排序，如下所示:

Java sorts result like this:

001_01._02.1_02.2.1_02.4_05.e.3.1_07.2.2_15.5.d_20.3.2.1_31.2.2_33.3.4_001                                                              
001_01._02.1_02.4_06.1_06.2.4.1.2_06.2.4.1.3_06.3.1_07.2.1.1_07.2.2.1_11.2.2_15.2.1.a.1_15.5.a_20.3.2.2.1.a_20.7.1.5_20.8_33.4.5.3_001 

此外，我不知道如何在Access中检查字符集和排序规则.

Additionaly I don't know how to check charset and collation in Access.

有人可以给我一个提示，我应该怎么做才能获得适当的结果?

Can anybody give me a hint what i should do to obtain proper results?

推荐答案

我知道了.我使用了Collat​​or类进行正确的排序.

I figured this out. I used Collator class for proper sorting.

仅供参考，如下所示(这是对ArrayList的排序):

FYI it looks like this (this is sorting of ArrayList):

public class AccessSorter
{ 
final static String letters = "< a, A < b, B < c, C < d, D < e, E < f, F < g, G < h, H < i, I < j, J < k, K < l, L < m, M < n, N < o, O < p, P < q, Q < r, R < s, S < t, T < u, U < v, V < w, W < x, X < y, Y < z, Z";
final static String digits = "< 0 < 1 < 2 < 3 < 4 < 5 < 6 < 7 < 8 < 9";
final static String special = "< '.' < '_'";
private RuleBasedCollator accessCollator;


AccessSorter()
{
    try
    {
        accessCollator = new RuleBasedCollator(special + digits + letters);
    }
    catch (ParseException e)
    {
        e.printStackTrace();
    }
}


SomeComparator getSomeComparator()
{
    return new SomeComparator();
}

class SomeComparator implements Comparator<String[]>
{
    @Override
    public int compare(String[] s1, String[] s2)
    {
       return accessCollator.compare(s1[1], s2[1]);
    }
}

}

用法示例:

Collections.sort(someCollectionOfStringArray, new AccessSorter().getSomeComparator()

现在您无需担心访问或mySQL的排序规则.您自己设定规则.当然，对这种数组进行排序需要花费一些时间.
请记住，如果需要对大型数组进行多次排序，请考虑使用getCollat​​orKey()来提高效率.
还请记住，如果您需要添加到整理程序分号，句号，引号将它们放入''.
我希望它会以某种方式对您有所帮助.问候.

And now you don't need to worry about collations of access or mySQL. You set rules by yourselve. Of course it takes some time to sort such array.
Remember, if you need to sort big arrays more than once, consider using of getCollatorKey() to increase efficiency.
Also remember if you need to add to collator semicolons, periods, quotation marks put them into ' '.
I hope it will help you somehow. Regards.

这篇关于如何检查(甚至设置)MDB(MS Access)文件中的排序规则?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！