#启用 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;