【中文排序】mysql order by 中文排序

时间:2023-02-01 10:44:17

1. 在MySQL中,我们经常会对一个字段进行排序查询,但进行中文排序和查找的时候,对汉字的排序和查找结果往往都是错误的。 这种情况在MySQL的很多版本中都存在。

如果这个问题不解决,那么MySQL将无法实际处理中文。 出现这个问题的原因是因为MySQL在查询字符串时是大小写不敏感的,在编绎MySQL时一般以ISO-8859字符集作为默认的字符集,因此在比较过程中中文编码字符大小写转换造成了这种现象。

2.解决方法:
对于包含中文的字段加上”binary”属性,使之作为二进制比较,例如将”name char(10)”改成”name char(10)binary”。
如果你使用源码编译MySQL,可以编译MySQL时使用 –with–charset=gbk 参数,这样MySQL就会直接支持中文查找和排序了(默认的是latin1)。也可以用 extra-charsets=gb2312,gbk 来加入多个字符集。

3. 如果不想对表结构进行修改或者重新编译MySQL,也可以在查询语句的 order by 部分使用 CONVERT 函数。

比如 select * from mytable order by CONVERT(chineseColumnName USING gbk);
UTF8 默认校对集是 utf8_general_ci , 它不是按照中文来的。你需要强制让MySQL按中文来排序。

例如:

SELECT * FROM employee_in ORDER BY CONVERT(NAME USING gbk)

aaarticlea/png;base64,iVBORw0KGgoAAAANSUhEUgAAAFUAAAEeCAIAAAB0f84WAAALf0lEQVR4nO2dO27tOBKGuR7DzgTuwZm9ACbEADc0vAZGDAx4AxfojMAFmpEDr8DgSbULZR0o9wQUH8WHjo5nBqOS6ofRPj6WL/yxipSa9bvI/nVusb/OLeY//XM+lfyXr8/zfBA/8RM/8UP+v1/uGLt7uGeMMcbuXv8sV//9cuffYve/Ll+fly/1zIKe1Ot9ujhdmf34Dj+68X9/YuxJXb4+L2+Py4uvz8ubeg8D8fDy+/L1efnz64E9vn99vt6zh5ffl7fHh5fflz+/HpYB+gSv9/exwh/jpp4DQBZVlvif1OXr8/X+8f3r0/Pnl+08BW7hD6G+BM4ef/ru7j9u5A8TYcn2Hv+Xeo4jte+PlfWPMfb4Hle4J3Xxi4JP/vs7xtjz2/Ldh5ffr/eM3f+6vD0yxp7fPv2LoP2OBd3/iJ/4/6L//134Tyv28fHxfUp58IV/Pp+In/iJn/g9v5WMMcakzV7d8CZClfEfNWdcz/NsZULa/iY6NfJ/1LwO6PY3canBbyXjnBdU29/EpZLfyiWe8cVNb6IT4F/yOVvVuB63v/n/ZvmJ6P5H/MRP/MR/ZtH+z8fH9/f3dD61+Z0amDDhKyPYoNz6v+OUMutX7FNNfiMAr1PiGv40TUZkQ4ZGNb9Tw8Lh1MBKAUQjqu93r92pCn6nhkE5TxZ//Xb84Rwpv4dlNnTz3zSC7xXmRpEewsB0wBD9Tv43f/lr89sIHMRQBb8RbFDGrwC92Q3WxjRRMn7nri+XOxHk9794WgFzquKm4JVdmg3XMAxXb5g7UWv+5/zpixa/U4MQy/uHyP9pmgr+qJo/XBcWwqPyh8wu3yzHo1wwMMwBev4nfuIn/nOL9n9o/3OFf9T8anlryzU7VIu/KuiPWl5FK64ZRxxj0ebParzSbuXn+bMf5xxFTfgaP9cjYOvm+JYx2qEgPwwi11oC7AajlawpFMGf+/G3kmurOVviL/v8dUZgSoWN+U/8fipUsJ38x5L+vftfnP9h/YtAVudDYK2ds5tdeDFqjWQAIL8nT8FrZHf+cBASJT77pIcgLKbAa89/dX6HEQG2rzhlYnrkObNj0fM/8RM/8Z9btP9D+5/Ev/Ab4UtX0d8wKJe8DnkRbPuVu1Uz/t7wsthejBAmvK6cMNuv3KlW8j9GUhgQ4fof2X7l7gT5wy8/KDdNzrnaAVaaf65fuW8157//1YMXDAhYW7ZfuVv1+H15vxHV3Nqy/crdaiX+U+FoGIQofCHbr9ytOvymYW6sbTHbr9ytVu7/hRr+p+1X7lbXn//ASr+q7VfuR/T8S/zET/znFu3/0P4n5Ic13G41a9RaWgRlnq768R917OwAa5650sAU/heGpAZc8/ccHV6wHtop8rVMATtVI/7AupW8AFk+zCncUhbtH0arJZbi97xe/68jX7jawiKRBgmX+WPu9P/h5VRmWaxrVCu5lM0f2f8S0Ir/OOrFB2GlD7yVjDFZrPQZfhZx5PGf55TOy+ewyFnJIChfxggsdkfmb6zqtrRFnYk/3v2z+yBq/vohpvcIYOFSOM/ptrH/RS8TPf8TP/ET/7lF+z+0/wn5oX+j2/vCKSUMklJfU/34Z/1djOpVtNLA1AVwFCXQmn+9qQ+s7XUKvYgKoI34FxaPAAj7/cSeD0LAcXFGCSzNf6Z2/rczYFCu9LSERSJv/YHF+LWo5HdqYMPQbn3kY12jGjEI0fyR/S8Brfg7p4T/7Y3wgTeCMSaKlT7DzyKOPP7TlNJ5+RwWuaLjiV8BhPlRs7jd6Cb+pteluCuciT/e/bP7IGr+Vse/9iOAgUvhNHXaBO1d9PxP/MRP/OcW7f/Q/mefH8vfsv9ELX5Y6B015xKTpeMm1fxlIyP/ZW7xOJIq/uD18u0NsppvUf4/iCB/5vzxrg9geJIa/3lPpVrz31fzrZQ2K4hzPVrUTse2av6+eW+09uD5DwwQwfeU60ipP8/ztfs/yAUkHV1uU8FvrU4ZsPjfjhz+Kv5Fe6ezxf90In7iJ37a/6H9n++T73/2+XGebLVNLX5Y6HVqGAQmS8dNqvmLAmbe2+eAqviD18sfbAQ7fKBxNW0X5M/PMFIOzHunBqF8efhImdCa/76av3SzGrLxQO10bKvm75v3nDEHz39ggAi+p1xHSv1pmq7d/0EuIOrqs10FvzEqZUB5ouEBw1/FH1qdTxf/04n4iZ/4af+H9n++T77/2eTf/tfsSO0RV+MPq179Ejiinje5av7yIC9QBedSdoqAh+HfZnXa3ilh3/opfyWkJsH/Fv+ouZTdNmH7VXP+387vfwjPsU9RNf9PFrL4M+hWwYr/B/1rSpcIpiEo+W+c/KPmtSto1BzF2j/Ptf9zK/1y/1s5DhPHGNDzP/ETP/GfW7T/Q/ufgL95os0R1eZ3aqiLf8wfdtKqAoKDQJwa8DhFmvwdC0CntYExyrnkj8FVJmzwd+PX4wejhaj30TQ1+J0SQoTGLrDyXfF7Q0xOvAS/3zBrbyr4jRiUq9oaLYQlv1MK8qfUR7N+tub/Nv7YDyvwQ5sokhFY58+9QFVPvJDjS84kXEw+qTX+Ut31j7GiMxIet+x/xN9vl4Mh9adp2spfn+zl1ICIsi96/id+4if+c4v2f2j/8zvvf4H4RJfb1OGHZbD4xvGGAvKHk39qH0s458WCEv/KWWEIan/z3Oj/YMv4L9XtZuh75X48NoB+/ncdThnaYflzzBVn08H4U8hj6xMZ+6FIG8+8i6NxMP7lvXz+J0dEi+no/Ol0rzZRf/0/Bn/4Mpz9Vx8AdrT4WwnnOisOd+MoXZ590fM/8RM/8Z9btP9D+5+R3yhh3DQ5o+oyZuFuMEo0LsKkgr/h+XFGpAOBhAFHIBUVcWdCQXDAMi6t+IdjT0TohNOt85WnxGXNcpDUBpv8Kfb+6xZLkSnCHIU/K2oLZYyr5kR92E/sEdW+as8q+UG7q/y/U/sAoAHw449/YWm4yp8vFYfgd8ucj1amtMZVUOGqw+R/w9AiRByKkj8OUjv+BoMNqvP8l+V88rR1D7prG4ZQmCDX+H0LvPzNxF8+CLYPDd2/EZie/4mf+In/3KL9H9r/rPmLvwZvFX+q97BViEr+vKIpLTjxp+UAaViFcDmEOvHPQj9qzroOmFFzaa1kXC59keJFSPpBrfFHH5zvawL4c3eMtPNoZeGWkXYeR2z80PPD9Thaq8McWMn/1PjlEPlvJWOcZyaIXv6H8eI6+Oa4tvE9DOPQ4LeSay2lTcYnvwyOWvJ4/tvikQLtwOLZQYhOCir5l1/dr3/Xj/8qusOFsxJxxH6e6/4vsY9RHcAt979mP5w96/r9L+oqvz8yMt4vUajLXyMU/MvkSEsfPCwSyRJIz//ET/zEf27R/g/tf35nf/89sKz8OYS2LnVJ04hhSH/rj6rxzaI2f13ObRe1isIfwkOyuvyhm0foadKscYY3q6r54iHbvyB/FnJh4GEwRfCNEZ308MIyDxrxz/p5VH6ArBhu6o5AK06RvarmN2IYhDJqYMIUvayq+V11xEHP75RQJlKtxH/5vmgckoVpFpTzf1Cu0edpSBaYev03wg+MU4MwmHrfTNPa/Q90/wqLnTDLYr98dxCizHk8uT9NG/rfxOeetVNQYVqA56edq/P8J+KdD/a2K94J60O7IyKC6U/+B+InfuL/X2+w7Fy0/0P7n1f5reYrxT1glcGjgn+02hs5OF+K3UlSAz9H0R8F5ek/NX+y7/BUyo5F8Vb4YbcMMCQIMmKVv9EMIVFVDVIY1zaVSJGYAK7Ev3jZEEiKET9/1vai2QsLVrVHzXkyxRyBv4q/lUuKcz1auMaNmkubFgdpj8c/aqm1Xl5yLnU6/zE2BuMHzf8x8IyaSwl5EnV3/cfJnzwuycEalvrm/Qzyo4DO1H3+G7W2BXiIbqc5IkrR8z/xf3//Gw5Amy+VxZtvAAAAAElFTkSuQmCC" alt="" />