MySQL汉字转对应的拼音字母(游标)

MySQL汉字转对应的拼音字母(游标)

-- MySQL创建函数设置
show variables like '%func%'; 
set global log_bin_trust_function_creators=1; 
grant all privileges on *.* to root@"%" identified by "."; 

-- 业务分析
-- 求汉字的首字符
SELECT LEFT('张三丰',1);

-- 获取字符串的16进制
SELECT HEX(LEFT('张三丰',1));

-- 为了更好支持中文编码为GBK
SELECT HEX(CONVERT(LEFT('张三丰',1) USING GBK));

-- 获取字符串的首字母的10进制的表示
SELECT CONV(HEX(CONVERT(LEFT('张三丰',1) USING GBK)),16,10);

-- 获取首字母的编码规范，根据GBK的编码顺序，A-Z的位置
SELECT INTERVAL(CONV(HEX(CONVERT(LEFT('张三丰',1) USING GBK)),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);

-- 获取首字母
SELECT ELT(INTERVAL(CONV(HEX(CONVERT(LEFT('张三丰',1) USING GBK)),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');

=================下面是针对 eperson 表姓名提取首字母的存储过程===============

-- 机构知识库对eperson表姓名提取首字母
DELIMITER ;;
drop procedure if exists getFirstHanZiCode;
CREATE DEFINER = CURRENT_USER PROCEDURE `getFirstHanZiCode`()
BEGIN
  #1.定义 变量 主要用来接收 查询出来的值（根据需求定义几个）
  DECLARE userid INT;   
  DECLARE myname VARCHAR(100);   

  # 内层while循环需要的变量
  DECLARE len INT DEFAULT 0;
  DECLARE i int DEFAULT 1;
	
  #首字母的结果
  DECLARE firstChar VARCHAR(50);
  #姓名中所有汉字的首字母
  DECLARE allChar VARCHAR(50);

  #2.这个用于处理游标到达最后一行的情况
  DECLARE s int default 0;  
             
  #3.声明游标cursor_name（cursor_name是个多行结果集）  
  DECLARE cursor_name CURSOR FOR 

  #4.查询结果集，将来要放入游标被遍历的
  SELECT eperson_id,scholar_name FROM  eperson WHERE initials IS NULL;
             
  #5.设置一个终止标记，终止之后把s置为1   
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET s=1; 
  
  #6.打开游标 
  OPEN cursor_name; 

  #7.获取游标当前指针的记录，读取一行数据并传给变量  
	fetch  cursor_name into userid,myname;  

	
	#8.开始循环，判断是否游标已经到达了最后作为循环条件   
	while s <> 1 do  
		# 8.1 TODO
		# insert into to_data(id,name) values(a,b);
	
		#初始化为空串
		SET firstChar = '';
		SET allChar = '';

		# 计算 姓名 的长度，一个字一个字的转
		SET len = CHAR_LENGTH(myname);
		IF len IS NOT NULL  THEN
			
			SET i = 1;
			WHILE i <= len DO
				# SELECT i;
				# 获取汉字首字母
				SET firstChar = ELT(INTERVAL(CONV(HEX(CONVERT(SUBSTR(myname,i,1) USING GBK)),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');
				SET allChar = CONCAT(allChar,firstChar) ;
				SET i = i+1;
			END WHILE;
		END IF;

		
		UPDATE `eperson` SET `initials` = allChar WHERE eperson_id =userid;
		#8.2 读取下一行的数据 
		FETCH  cursor_name INTO userid,myname; 
                     
	END WHILE; 
 
	#10.关闭游标  
	CLOSE cursor_name ;  

	#11.完成之后输出
	SELECT 'done';  
               
	#语句执行结束  
END;