mysql实现用拼音搜索中文的数据库实现

最新推荐文章于 2021-03-17 12:34:55 发布

转载最新推荐文章于 2021-03-17 12:34:55 发布 · 3.8k 阅读

文章标签：

#mysql

本文介绍了一个基于MySQL的拼音转换解决方案，包括创建拼音对照表、插入常用拼音数据、定义拼音转换函数及建立拼音视图等内容。

1、创建表：

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;

2、插入数据：

INSERT INTO t_base_pinyin (pin_yin_, code_)
VALUES
    ("a", 20319),
    ("ai", 20317),
    ("an", 20304),
    ("ang", 20295),
    ("ao", 20292),
    ("ba", 20283),
    ("bai", 20265),
    ("ban", 20257),
    ("bang", 20242),
    ("bao", 20230),
    ("bei", 20051),
    ("ben", 20036),
    ("beng", 20032),
    ("bi", 20026),
    ("bian", 20002),
    ("biao", 19990),
    ("bie", 19986),
    ("bin", 19982),
    ("bing", 19976),
    ("bo", 19805),
    ("bu", 19784),
    ("ca", 19775),
    ("cai", 19774),
    ("can", 19763),
    ("cang", 19756),
    ("cao", 19751),
    ("ce", 19746),
    ("ceng", 19741),
    ("cha", 19739),
    ("chai", 19728),
    ("chan", 19725),
    ("chang", 19715),
    ("chao", 19540),
    ("che", 19531),
    ("chen", 19525),
    ("cheng", 19515),
    ("chi", 19500),
    ("chong", 19484),
    ("chou", 19479),
    ("chu", 19467),
    ("chuai", 19289),
    ("chuan", 19288),
    ("chuang", 19281),
    ("chui", 19275),
    ("chun", 19270),
    ("chuo", 19263),
    ("ci", 19261),
    ("cong", 19249),
    ("cou", 19243),
    ("cu", 19242),
    ("cuan", 19238),
    ("cui", 19235),
    ("cun", 19227),
    ("cuo", 19224),
    ("da", 19218),
    ("dai", 19212),
    ("dan", 19038),
    ("dang", 19023),
    ("dao", 19018),
    ("de", 19006),
    ("deng", 19003),
    ("di", 18996),
    ("dian", 18977),
    ("diao", 18961),
    ("die", 18952),
    ("ding", 18783),
    ("diu", 18774),
    ("dong", 18773),
    ("dou", 18763),
    ("du", 18756),
    ("duan", 18741),
    ("dui", 18735),
    ("dun", 18731),
    ("duo", 18722),
    ("e", 18710),
    ("en", 18697),
    ("er", 18696),
    ("fa", 18526),
    ("fan", 18518),
    ("fang", 18501),
    ("fei", 18490),
    ("fen", 18478),
    ("feng", 18463),
    ("fo", 18448),
    ("fou", 18447),
    ("fu", 18446),
    ("ga", 18239),
    ("gai", 18237),
    ("gan", 18231),
    ("gang", 18220),
    ("gao", 18211),
    ("ge", 18201),
    ("gei", 18184),
    ("gen", 18183),
    ("geng", 18181),
    ("gong", 18012),
    ("gou", 17997),
    ("gu", 17988),
    ("gua", 17970),
    ("guai", 17964),
    ("guan", 17961),
    ("guang", 17950),
    ("gui", 17947),
    ("gun", 17931),
    ("guo", 17928),
    ("ha", 17922),
    ("hai", 17759),
    ("han", 17752),
    ("hang", 17733),
    ("hao", 17730),
    ("he", 17721),
    ("hei", 17703),
    ("hen", 17701),
    ("heng", 17697),
    ("hong", 17692),
    ("hou", 17683),
    ("hu", 17676),
    ("hua", 17496),
    ("huai", 17487),
    ("huan", 17482),
    ("huang", 17468),
    ("hui", 17454),
    ("hun", 17433),
    ("huo", 17427),
    ("ji", 17417),
    ("jia", 17202),
    ("jian", 17185),
    ("jiang", 16983),
    ("jiao", 16970),
    ("jie", 16942),
    ("jin", 16915),
    ("jing", 16733),
    ("jiong", 16708),
    ("jiu", 16706),
    ("ju", 16689),
    ("juan", 16664),
    ("jue", 16657),
    ("jun", 16647),
    ("ka", 16474),
    ("kai", 16470),
    ("kan", 16465),
    ("kang", 16459),
    ("kao", 16452),
    ("ke", 16448),
    ("ken", 16433),
    ("keng", 16429),
    ("kong", 16427),
    ("kou", 16423),
    ("ku", 16419),
    ("kua", 16412),
    ("kuai", 16407),
    ("kuan", 16403),
    ("kuang", 16401),
    ("kui", 16393),
    ("kun", 16220),
    ("kuo", 16216),
    ("la", 16212),
    ("lai", 16205),
    ("lan", 16202),
    ("lang", 16187),
    ("lao", 16180),
    ("le", 16171),
    ("lei", 16169),
    ("leng", 16158),
    ("li", 16155),
    ("lia", 15959),
    ("lian", 15958),
    ("liang", 15944),
    ("liao", 15933),
    ("lie", 15920),
    ("lin", 15915),
    ("ling", 15903),
    ("liu", 15889),
    ("long", 15878),
    ("lou", 15707),
    ("lu", 15701),
    ("lv", 15681),
    ("luan", 15667),
    ("lue", 15661),
    ("lun", 15659),
    ("luo", 15652),
    ("ma", 15640),
    ("mai", 15631),
    ("man", 15625),
    ("mang", 15454),
    ("mao", 15448),
    ("me", 15436),
    ("mei", 15435),
    ("men", 15419),
    ("meng", 15416),
    ("mi", 15408),
    ("mian", 15394),
    ("miao", 15385),
    ("mie", 15377),
    ("min", 15375),
    ("ming", 15369),
    ("miu", 15363),
    ("mo", 15362),
    ("mou", 15183),
    ("mu", 15180),
    ("na", 15165),
    ("nai", 15158),
    ("nan", 15153),
    ("nang", 15150),
    ("nao", 15149),
    ("ne", 15144),
    ("nei", 15143),
    ("nen", 15141),
    ("neng", 15140),
    ("ni", 15139),
    ("nian", 15128),
    ("niang", 15121),
    ("niao", 15119),
    ("nie", 15117),
    ("nin", 15110),
    ("ning", 15109),
    ("niu", 14941),
    ("nong", 14937),
    ("nu", 14933),
    ("nv", 14930),
    ("nuan", 14929),
    ("nue", 14928),
    ("nuo", 14926),
    ("o", 14922),
    ("ou", 14921),
    ("pa", 14914),
    ("pai", 14908),
    ("pan", 14902),
    ("pang", 14894),
    ("pao", 14889),
    ("pei", 14882),
    ("pen", 14873),
    ("peng", 14871),
    ("pi", 14857),
    ("pian", 14678),
    ("piao", 14674),
    ("pie", 14670),
    ("pin", 14668),
    ("ping", 14663),
    ("po", 14654),
    ("pu", 14645),
    ("qi", 14630),
    ("qia", 14594),
    ("qian", 14429),
    ("qiang", 14407),
    ("qiao", 14399),
    ("qie", 14384),
    ("qin", 14379),
    ("qing", 14368),
    ("qiong", 14355),
    ("qiu", 14353),
    ("qu", 14345),
    ("quan", 14170),
    ("que", 14159),
    ("qun", 14151),
    ("ran", 14149),
    ("rang", 14145),
    ("rao", 14140),
    ("re", 14137),
    ("ren", 14135),
    ("reng", 14125),
    ("ri", 14123),
    ("rong", 14122),
    ("rou", 14112),
    ("ru", 14109),
    ("ruan", 14099),
    ("rui", 14097),
    ("run", 14094),
    ("ruo", 14092),
    ("sa", 14090),
    ("sai", 14087),
    ("san", 14083),
    ("sang", 13917),
    ("sao", 13914),
    ("se", 13910),
    ("sen", 13907),
    ("seng", 13906),
    ("sha", 13905),
    ("shai", 13896),
    ("shan", 13894),
    ("shang", 13878),
    ("shao", 13870),
    ("she", 13859),
    ("shen", 13847),
    ("sheng", 13831),
    ("shi", 13658),
    ("shou", 13611),
    ("shu", 13601),
    ("shua", 13406),
    ("shuai", 13404),
    ("shuan", 13400),
    ("shuang", 13398),
    ("shui", 13395),
    ("shun", 13391),
    ("shuo", 13387),
    ("si", 13383),
    ("song", 13367),
    ("sou", 13359),
    ("su", 13356),
    ("suan", 13343),
    ("sui", 13340),
    ("sun", 13329),
    ("suo", 13326),
    ("ta", 13318),
    ("tai", 13147),
    ("tan", 13138),
    ("tang", 13120),
    ("tao", 13107),
    ("te", 13096),
    ("teng", 13095),
    ("ti", 13091),
    ("tian", 13076),
    ("tiao", 13068),
    ("tie", 13063),
    ("ting", 13060),
    ("tong", 12888),
    ("tou", 12875),
    ("tu", 12871),
    ("tuan", 12860),
    ("tui", 12858),
    ("tun", 12852),
    ("tuo", 12849),
    ("wa", 12838),
    ("wai", 12831),
    ("wan", 12829),
    ("wang", 12812),
    ("wei", 12802),
    ("wen", 12607),
    ("weng", 12597),
    ("wo", 12594),
    ("wu", 12585),
    ("xi", 12556),
    ("xia", 12359),
    ("xian", 12346),
    ("xiang", 12320),
    ("xiao", 12300),
    ("xie", 12120),
    ("xin", 12099),
    ("xing", 12089),
    ("xiong", 12074),
    ("xiu", 12067),
    ("xu", 12058),
    ("xuan", 12039),
    ("xue", 11867),
    ("xun", 11861),
    ("ya", 11847),
    ("yan", 11831),
    ("yang", 11798),
    ("yao", 11781),
    ("ye", 11604),
    ("yi", 11589),
    ("yin", 11536),
    ("ying", 11358),
    ("yo", 11340),
    ("yong", 11339),
    ("you", 11324),
    ("yu", 11303),
    ("yuan", 11097),
    ("yue", 11077),
    ("yun", 11067),
    ("za", 11055),
    ("zai", 11052),
    ("zan", 11045),
    ("zang", 11041),
    ("zao", 11038),
    ("ze", 11024),
    ("zei", 11020),
    ("zen", 11019),
    ("zeng", 11018),
    ("zha", 11014),
    ("zhai", 10838),
    ("zhan", 10832),
    ("zhang", 10815),
    ("zhao", 10800),
    ("zhe", 10790),
    ("zhen", 10780),
    ("zheng", 10764),
    ("zhi", 10587),
    ("zhong", 10544),
    ("zhou", 10533),
    ("zhu", 10519),
    ("zhua", 10331),
    ("zhuai", 10329),
    ("zhuan", 10328),
    ("zhuang", 10322),
    ("zhui", 10315),
    ("zhun", 10309),
    ("zhuo", 10307),
    ("zi", 10296),
    ("zong", 10281),
    ("zou", 10274),
    ("zu", 10270),
    ("zuan", 10262),
    ("zui", 10260),
    ("zun", 10256),
    ("zuo", 10254);

3、创建函数：

DROP FUNCTION IF EXISTS to_pinyin;
DELIMITER $
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);
END;
$
DELIMITER ;

4、创建视图：

CREATE VIEW v_pinyin AS SELECT
    u.id,
    to_pinyin (u.displayname) AS pinyin,
    u.displayName
FROM
    wsm_userinfo u