Mysql 生成 (获取) 字段拼音首字母函数（支持中英文及数字符号混杂），mysql 按拼音排序

1. 生成 (获取) 字段第一个字符的首字母

-- 字符串P_NAME第一个字的拼音首字母，如果字符串P_NAME第一个字符为ascii码，则返回该的原值,如果P_NON_CN为真则非中文（如日文）也返回原值
drop function if exists firstPinyin;
CREATE FUNCTION `firstPinyin`(P_NAME VARCHAR(255),P_NON_CN BOOLEAN) RETURNS varchar(255) CHARSET utf8
BEGIN
    DECLARE V_RETURN VARCHAR(4);
    DECLARE V_FIRST varchar(4);
    DECLARE V_CHAR varchar(32);
    -- 第一个字符
    set V_FIRST= left(P_NAME,1);
    -- 第一个字符编码
    set V_CHAR = CONV(HEX(CONVERT(P_NAME USING gbk)),16,10);
    -- 如果是数字和字母则返回原值，如果想ascii也返回空的话就改改下面的if
    if V_CHAR <=127 then set V_RETURN = V_FIRST;
    -- 否则对中文取拼音首字母，其他则为null
    else
        SET V_RETURN = ELT(INTERVAL(V_CHAR,
                                0xB0A1,0xB0C5,0xB2C1,0xB4EE,0xB6EA,0xB7A2,0xB8C1,0xB9FE,0xBBF7,
                                0xBFA6,0xC0AC,0xC2E8,0xC4C3,0xC5B6,0xC5BE,0xC6DA,0xC8BB,
                                0xC8F6,0xCBFA,0xCDDA,0xCEF4,0xD1B9,0xD4D1),
                       'a','b','c','d','e','f','g','h','j','k','l','m','n','o','p','q','r','s','t','w','x','y','z');
        if P_NON_CN and V_RETURN is null then set V_RETURN=V_FIRST; end if;
    end if;
    return V_RETURN;
END;

-- 示例：
select firstPinyin('@',false); -- @
select firstPinyin('5',false); -- 5
select firstPinyin('R',false); -- R
select firstPinyin('我',false); -- w
select firstPinyin('（',false); -- null值
select firstPinyin('（',true); -- （

2. 生成 (获取) 字段全部字符的首字母

-- 字符串P_NAME所有字的拼音首字母,注意长度限制为255，如果P_NON_CN为真则非中文（如日文）返回原值否则仅ascii返回原值
drop function if exists pinyin;
CREATE  FUNCTION `pinyin`(P_NAME VARCHAR(255),P_NON_CN BOOLEAN) RETURNS varchar(255)
BEGIN
    DECLARE V_COMPARE VARCHAR(255);
    DECLARE V_RETURN VARCHAR(255);
    DECLARE V_PINYIN1 varchar(4);
    DECLARE I INT;
    if P_NAME is null or P_NAME = '' then return ''; end if;
    SET I = 1;
    SET V_RETURN = '';
    -- 循环每一个字符获取拼音首字母
    while I <= char_length(P_NAME) do
            SET V_COMPARE = SUBSTR(P_NAME, I, 1);
            IF (V_COMPARE != '') THEN
                set V_PINYIN1 = firstPinyin(V_COMPARE,P_NON_CN);
                if V_PINYIN1 is not null and V_PINYIN1 != '' then
                SET V_RETURN = CONCAT(V_RETURN, V_PINYIN1);
                end if;
            END IF;
            SET I = I + 1;
        end while;
    IF (ISNULL(V_RETURN) or V_RETURN = '') THEN
        SET V_RETURN = P_NAME;
    END IF;
    RETURN V_RETURN;
END;

-- 示例：
select pinyin('ほ#1&*@2我啊(大）]',false); -- #1&*@2wa(d]
select pinyin('ほ#1&*@2我啊(大）]',true); -- ほ#1&*@2wa(d）]

3. 例子：查询按照拼音首字母排序

3.1. 直接利用 convert 函数按照拼音首字母排序

-- 由于会全表扫描，在数据量大时不能满足查询速度需要
explain
select * from cust_base order by convert(cust_name using gbk) limit 10;

3.2. 利用专门的字段按照拼音首字母字段排序，并使用触发器自动生成拼音字段值

-- 添加字段
ALTER TABLE cust_base add column cust_name_pinyin varchar(6) not null default '' comment '客户名称拼音';
-- 插入时触发器
drop trigger if exists trigger_in_cust_base;
create trigger trigger_in_cust_base BEFORE INSERT on cust_base for each row
    begin
        declare v_pinyin varchar(255);
        if new.cust_name_pinyin is null or new.cust_name_pinyin = '' then
            set v_pinyin =  pinyin(left(new.cust_name,6),false);
            if v_pinyin is not null then
                set new.cust_name_pinyin = v_pinyin;
            end if;
        end if;
    end;
-- 修改时触发器
drop trigger if exists trigger_up_cust_base;
create trigger trigger_up_cust_base BEFORE update on cust_base for each row
begin
    declare v_pinyin varchar(255);
    if new.cust_name_pinyin is null or new.cust_name_pinyin = '' then
        set v_pinyin =  pinyin(left(new.cust_name,6),false);
        if v_pinyin is not null then
            set new.cust_name_pinyin = v_pinyin;
        end if;
    end if;
end;
-- 手工触发触发器生成拼音，以应用老数据
update cust_base set cust_name_pinyin='';
-- 创建索引以方便按拼音排序
create index idx_cust_name_pinyin on cust_base (cust_name_pinyin);

-- 执行查询，不会全表扫描
explain
select * from cust_base order by cust_name_pinyin limit 10;