MySQL Custom function to achieve Chinese first letter pinyin query

Example 1

MySQL functional function to achieve Pinyin query:
Function: Enter the first letter of each word in Chinese string to retrieve the corresponding data.
How to: Use Py directly (field name) = ' keywords '.

The code is as follows

Copy Code

DELIMITER $$ CREATE FUNCTION ' Pyfirst ' (p_name VARCHAR (255)) RETURNS VARCHAR (255) CHARSET UTF8 BEGIN DECLARE V_return VARCHAR (255); SET V_return = ELT (INTERVAL CONV (HEX (Left (CONVERT (p_name USING GBK), 1), 16,10), 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 '); return v_return; end$$ DELIMITER; DELIMITER $$ CREATE FUNCTION ' PY ' (p_name VARCHAR (255)) RETURNS VARCHAR (255) CHARSET UTF8 BEGIN DECLARE v_compare VARCHAR (255); DECLARE V_return VARCHAR (255); DECLARE I INT; SET I = 1; SET V_return = '; While I < LENGTH (p_name) do SET V_compare = SUBSTR (P_name, I, 1); IF (V_compare!= ') THEN #SET V_return = CONCAT (V_return, ', ', v_compare); SET V_return = CONCAT (V_return, Pyfirst (V_compare)); #SET V_return = Pyfirst (V_compare); 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$$ DELIMITER;

Example 2

Now for the implementation in MySQL, the test environment is MYSQL-5.0.27-WIN32
1, the establishment of pinyin first letter data sheet

The code is as follows

Copy Code

DROP TABLE IF EXISTS ' Pyk '; CREATE TABLE ' Pyk ' ( ' PY ' varchar (1), ' HZ1 ' int, ' HZ2 ' int ) ; INSERT into ' pyk ' (' PY ', ' HZ1 ', ' HZ2 ') VALUES (' A ', -20319,-20284), (' B ', -20283,-19776), (' C ', -19775,-19219), (' D ', -19218,-18711), (' E ', -18710,-18527), (' F ', -18526,-18240), (' G ', -18239,-17923), (' H ', -17922,-17418), (' J ', -17417,-16475), (' K ', -16474,-16213), (' L ', -16212,-15641), (' M ', -15640,-15166), (' N ', -15165,-14923), (' O ', -14922,-14915), (' P ', -14914,-14631), (' Q ', -14630,-14150), (' R ', -14149,-14091), (' S ', -14090,-13319), (' T ', -13318,-12839), (' W ', -12838,-12557), (' X ', -12556,-11848), (' Y ', -11847,-11056), (' Z ', -11055,-10247); 2. Set up MySQL function DROP FUNCTION IF EXISTS hzcode; Delimiter// CREATE FUNCTION Hzcode (S CHAR (255)) RETURNS Char BEGIN DECLARE Hz_code int; DECLARE Hz_py Char; SET Hz_code = ord (substring (s,1,1)) *256+ord (substring (s,2,1))-65536; Select py into hz_py from Pyk where HZ_CODE&GT;=PYK.HZ1 and Hz_code<=pyk . hz2; return hz_py; End // delimiter; Database type if it is GBK, this function seems to have a little problem, slightly changed, seemingly can be used in the GBK Delimiter $$ DROP FUNCTION IF EXISTS ' Hzcode ' $$ CREATE FUNCTION ' Hzcode ' (s CHAR (255)) RETURNS Char BEGIN DECLARE Hz_code int; DECLARE Hz_py Char; DECLARE str varchar (400); SET Hz_code = ord (substring (s,1,1))-65536; Select py into hz_py from Pyk where HZ_CODE&GT;=PYK.HZ1 and hz_code<=pyk.hz2; return hz_py; end$$ Delimiter $$ 3, First Test Mysql> Select Hzcode (' Nanhai Dragon King '); +--------------------+ | Hzcode (' Nanhai Dragon King ') | +--------------------+ | N | +--------------------+ 1 row in Set (0.00 sec) 4, set up a test table DROP TABLE IF EXISTS ' F1 '; CREATE TABLE F1 ( Name varchar (30), Pykey varchar (1) ); Insert into F1 (name) values (' John '), (' Dick '), (' Harry '), (' Zhao Liu '), (' Money seven '); 5, testing Mysql> SELECT * from F1; +------+-------+ | name | Pykey | +------+-------+ | John | NULL | | Dick | NULL | | Harry | NULL | | Zhao Liu | NULL | | Money Seven | NULL | +------+-------+ 5 rows in Set (0.00 sec) Mysql> Update f1 Set Pykey = Hzcode (name); Query OK, 5 rows affected (0.05 sec) Rows Matched:5 Changed:5 warnings:0 Mysql> SELECT * from F1; +------+-------+ | name | Pykey | +------+-------+ | John | Z | | Dick | L | | Harry | W | | Zhao Liu | Z | | Money Seven | Q | +------+-------+ 5 rows in Set (0.00 sec)

This makes it easy to query the initials of the Chinese characters in MySQL. Similarly, you can get Chinese pinyin directly in MySQL. But the phonetic table is needed, and the function is not the same.