mysql字段中提取汉字，去除数字以及字母

一时失言乱红尘 2021-10-18 16:58 871阅读 0赞

# 需求 #

最近接到的新需求用户表中有两个字段分别代表用户的系统姓名以及真实姓名,系统姓名在系统中是唯一的用于展示给业务操作人员,减少重名对操作人员的干扰，真实姓名用于用户端展示。但是由于之前需求缺失，并没有对用户真实姓名进行维护，所以这次的要求已系统用户表为准，去掉其中包含的数字以及字母只保留汉字更新到真实姓名中。

这个功能使用只使用一次,不建议使用Java程序处理，因为处理之后还需要重新冗余代码，所以尝试从sql角度，用sql将数据修复。

# 处理流程 #

## 1、提取字段中汉字 ##

## 2、已提取后为准更新字段 ##

mysql提取汉字主要是按字符循环处理，具体函数如下

DELIMITER $$
    DROP FUNCTION IF EXISTS `Num_char`$$
    CREATE FUNCTION `Num_char`(Varstring VARCHAR(100)CHARSET utf8) RETURNS VARCHAR(50) CHARSET utf8
    BEGIN
    	DECLARE len INT DEFAULT 0;
    	DECLARE Tmp VARCHAR(100) DEFAULT '';
    	SET len=CHAR_LENGTH(Varstring);
    		WHILE len > 0 DO
    		IF NOT (MID(Varstring,len,1)REGEXP '^[u0391-uFFE5]')
    		THEN
    		SET Tmp=CONCAT(Tmp,MID(Varstring,len,1));
    		END IF;
    		SET len = len - 1;
    		END WHILE;
    	  RETURN REVERSE(Tmp);
        END$$
    DELIMITER ;

之后利用Num\_char函数直接更新表中字段

**注意**：mysql中不允许一条sql中表中既有查询又更新，所以把查询做成子表，然后在去更新

UPDATE tb_student student,
    ( SELECT Num_char ( NAME) studentName, biz_id FROM tb_student WHERE deleted = 0 ) a 
    SET student.real_name = a.studentName 
    WHERE
    	student.biz_id = a.biz_id 
    	AND student.deleted =0;

由于用户表中数据比较大，造成此更新语句特别慢，如果线上执行可能会影响正常业务，所以接下主要是为了提高处理速度。这个更新语句过慢的原因在于Num\_char函数，是每条记录中的每个字符都要比较一下，所以造成非常耗时。但是使用sql提取汉字并没有其他的好的方案，只能尝试着从数据上分析，结合业务使用反馈以及数据库数据查看，发现系统名字中函数数字和字母比例不是很高，所以尝试从拆分数据，分开更新，如果只有小部分数据使用Num\_char函数，那么速度上也会有很大提高。

### **更新拆分方案** ###

1、查询系统名字中含有数字和字母的数据，并进行更新,此次利用正则表达式进行配匹

UPDATE tb_student student,
    ( SELECT Num_char ( `name`) studentName, biz_id FROM tb_student  where name  REGEXP '[0-9|A-Za-z]+') a 
    SET student.real_name = a.studentName 
    WHERE
    	student.biz_id = a.biz_id;

2、将剩余数据的系统名称直接更新到真实姓名上

UPDATE tb_student student,
    ( SELECT name studentName, biz_id FROM tb_student  where name not REGEXP '[0-9|A-Za-z]+') a 
    SET student.real_name = a.studentName 
    WHERE
    	student.biz_id = a.biz_id;

经过拆分后，时间从1h+已经降到10min以内，处于可以接受范围中了。

### 题外话 ###

再提供另外一种思路

以时间为分割线利用临时表将需要处理的字段提前在临时表中处理好，待需求上线后，将临时表和用户表做联合更新，以后利用Num\_char函数处理临时表中之外的数据，同样能达到相同的效果。（ps:结合目前系统的表数据量考虑第一种方案处理起来会更简单，大家结合自己的数据合理选择）

# 总结 #

遇到问题解决问题，生活和工作不是考试题目，不是每一个题目都只有一个答案，只要我们愿意去想去做，一定会有更好的方案以及新的惊喜等待我们。各位加油干吧！

ps：mysql字段中根据需要筛选汉字，数字，字符函数如下

DELIMITER $$
    DROP FUNCTION IF EXISTS `Num_char_extract`$$
    CREATE FUNCTION `Num_char_extract`(Varstring VARCHAR(100)CHARSET utf8, flag INT) RETURNS VARCHAR(50) CHARSET utf8
    BEGIN
    	DECLARE len INT DEFAULT 0;
    	DECLARE Tmp VARCHAR(100) DEFAULT '';
    	SET len=CHAR_LENGTH(Varstring);
    	IF flag = 0 
    	THEN
    		WHILE len > 0 DO
    		IF MID(Varstring,len,1)REGEXP'[0-9]' THEN
    		SET Tmp=CONCAT(Tmp,MID(Varstring,len,1));
    		END IF;
    		SET len = len - 1;
    		END WHILE;
    	ELSEIF flag=1
    	THEN
    		WHILE len > 0 DO
    		IF (MID(Varstring,len,1)REGEXP '[a-zA-Z]') 
    		THEN
    		SET Tmp=CONCAT(Tmp,MID(Varstring,len,1));
    		END IF;
    		SET len = len - 1;
    		END WHILE;
    	ELSEIF flag=2
    	THEN
    		WHILE len > 0 DO
    		IF ( (MID(Varstring,len,1)REGEXP'[0-9]')
    		OR (MID(Varstring,len,1)REGEXP '[a-zA-Z]') ) 
    		THEN
    		SET Tmp=CONCAT(Tmp,MID(Varstring,len,1));
    		END IF;
    		SET len = len - 1;
    		END WHILE;
    	ELSEIF flag=3
    	THEN
    		WHILE len > 0 DO
    		IF NOT (MID(Varstring,len,1)REGEXP '^[u0391-uFFE5]')
    		THEN
    		SET Tmp=CONCAT(Tmp,MID(Varstring,len,1));
    		END IF;
    		SET len = len - 1;
    		END WHILE;
    	ELSE 
    		SET Tmp = 'Error: The second paramter should be in (0,1,2,3)';
    		RETURN Tmp;
    	END IF;
    	RETURN REVERSE(Tmp);
        END$$
    DELIMITER ;

使用方式 标识 0 提取数字 1 提取字母 2提取数字+字母 3 提取汉字

select Num_char_extract('字段',标识)