How to implement regex_replace regular replacement function in mysql5.7
PS: I recently received a small request: for desensitization of user sensitive information, the data source is mysql. The initial idea was to use the regexp_replace function in mysql to clean sensitive fields directly. Later, it was found that regexp_replace() was only supported by mysql8.0. There are only regular matching functions in mysql5.7. Later, I want to extract the data to hdfs and use hive to process it. When I thought that I had to build a table, I finally decided to use mysql's custom function to solve it, saving time and effort.
1. Enable mysql custom function support
a. Check whether mysql currently supports writing custom:
show variables like '%fun%';
b.log_bin_trust_function_creators=OFF means that the custom function is not enabled. Enter the start command.
set global log_bin_trust_function_creators=1;
Note: It is not turned on here, and the custom function will be written later. call doesn't work!
2. Write regexp_replace() custom function
a. Write the code as follows:
#Delete the created custom function before creating
drop function if exists regexp_replace;
#Create regexp_replace function
DELIMITER $$
CREATE FUNCTION `regexp_replace`(string_a VARCHAR(1000), pattern VARCHAR(1000), string_b VARCHAR(1000))
RETURNS VARCHAR(1000)
DETERMINISTIC
BEGIN
DECLARE string_c VARCHAR(1000);
DECLARE nub VARCHAR(1);
DECLARE i INT;
SET i =1;
SET string_c = '';
IF string_a REGEXP pattern THEN
loop_label: LOOP
IF i>CHAR_LENGTH(string_a) THEN
LEAVE loop_label;
END IF;
SET nub = SUBSTRING(string_a,i,1);
IF NOT nub REGEXP pattern THEN
SET string_c = CONCAT(string_c,nub);
ELSE
SET string_c = CONCAT(string_c,string_b);
END IF;
SET i=i+1;
END LOOP;
ELSE
SET string_c = string_a;
END IF;
RETURN string_c;
END$$
DELIMITER;
b. After the above code is executed, check whether the creation is successful. The result has been created successfully.
c. Test the function and the result is successful!
0 Comments