MySQL中文排序

最新推荐文章于 2022-06-27 17:03:34 发布

Wolfape

最新推荐文章于 2022-06-27 17:03:34 发布

阅读量242

点赞数

分类专栏： java 文章标签： java mysql

本文链接：https://blog.youkuaiyun.com/wangy_0228/article/details/114653171

版权

java 专栏收录该内容

5 篇文章

订阅专栏

在我们的项目开发中经常会遇到按照名称A-Z等SQL中文排序查找问题，在这里记录一下：

<if test="null != orderField and orderField != '' and  null != orderType and orderType != ''">
        ORDER BY 
        CONVERT( ${orderField} USING GBK)
        ${orderType}
</if>

有时候我们会做连表查询或表字段的聚合函数计算等就要具体的表字段排序，而不是${orderField}引用否则会报错：

SELECT
        w.f_id AS id, w.f_title AS title, u.f_name AS createdName, u.f_account AS account
        FROM  tablew w
        LEFT JOIN tuser u ON u.f_id = w.f_created_by
        <where>
            <if test="null != ...">
                ...
            </if>
        </where>
        <if test="null != orderField and orderField != '' and  null != orderType and orderType != ''">
            ORDER BY
            <choose>
                <when test="orderField == 'f_title' or orderField == 'createdName'">
                    CONVERT(${orderField} USING GBK)
                </when> 
                <otherwise>
                    w.${orderField}
                </otherwise>
            </choose>
            ${orderType}
        </if>

如上代码则会报错，CONVERT(${orderField} USING GBK) 需要排序明确表字段w.f_title

            <choose>
                <when test="orderField == 'f_title'">
                    CONVERT(w.f_title USING GBK)
                </when>
                <otherwise>
                    w.${orderField}
                </otherwise>
            </choose>
            ${orderType}