【数据库-MySql】 一键建表中的A字段转换成拼音码到B字段

#启用 log_bin_trust_function_creators
set global log_bin_trust_function_creators=TRUE;

#创建 t_base_pinyin 表
CREATE TABLE IF NOT EXISTS `t_base_pinyin` (
  `pin_yin_` varchar(255) CHARACTER SET gbk NOT NULL,
  `code_` int(11) NOT NULL,
  PRIMARY KEY (`code_`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

#清空 t_base_pinyin 表
delete from t_base_pinyin;

INSERT INTO t_base_pinyin (pin_yin_,code_) VALUES 
("A", 20319),("B", 20283),("C", 19775),("D", 19218),("E", 18710),("F", 18526),
("G", 18239),("H", 17922),("J", 17417),("K", 16474),("L", 16212),("M", 15640),
("N", 15165),("O", 14922),("P", 14914),("Q", 14630),("R", 14149),("S", 14090),
("T", 13318),("W", 12838),("X", 12556),("Y", 11847),("Z", 11055);

#创建 to_pinyin 函数
DROP FUNCTION IF EXISTS to_pinyin;
CREATE FUNCTION to_pinyin(NAME VARCHAR(255) CHARSET gbk)
RETURNS VARCHAR(255) CHARSET gbk
BEGIN
    DECLARE mycode INT;
    DECLARE tmp_lcode VARCHAR(2) CHARSET gbk;
    DECLARE lcode INT;

    DECLARE tmp_rcode VARCHAR(2) CHARSET gbk;
    DECLARE rcode INT;

    DECLARE mypy VARCHAR(255) CHARSET gbk DEFAULT '';
    DECLARE lp INT;

    SET mycode = 0;
    SET lp = 1;

    SET NAME = HEX(NAME);

    WHILE lp < LENGTH(NAME) DO

        SET tmp_lcode = SUBSTRING(NAME, lp, 2);
        SET lcode = CAST(ASCII(UNHEX(tmp_lcode)) AS UNSIGNED); 
        SET tmp_rcode = SUBSTRING(NAME, lp + 2, 2);
        SET rcode = CAST(ASCII(UNHEX(tmp_rcode)) AS UNSIGNED); 
        IF lcode > 128 THEN
            SET mycode =65536 - lcode * 256 - rcode ;
            SELECT CONCAT(mypy,pin_yin_) INTO mypy FROM t_base_pinyin WHERE CODE_ >= ABS(mycode) ORDER BY CODE_ ASC LIMIT 1;
            SET lp = lp + 4;
        ELSE
            SET mypy = CONCAT(mypy,CHAR(CAST(ASCII(UNHEX(SUBSTRING(NAME, lp, 2))) AS UNSIGNED)));
            SET lp = lp + 2;
        END IF;
    END WHILE;
#返回值转换成小写 RETURN LOWER(mypy);
#返回值转换成大写 RETURN UPPER(mypy);
    RETURN UPPER(mypy);
END;
#将 med_name_ch 字段转换成py码到 med_py_code字段
UPDATE medicine set med_py_code = to_pinyin(med_name_ch) where med_py_code is null or med_py_code ='';

#清空还原
DROP FUNCTION IF EXISTS to_pinyin;
DROP TABLE IF EXISTS t_base_pinyin;
set global log_bin_trust_function_creators=false;