preface

Hello, I'm Milo. Name desensitization was used in the project a few days ago. Keep the head and tail, and fill an indefinite number of '*' in the middle. Because I'm not familiar with related functions, I've spent a lot of trouble. Today, I'd like to summarize what I feel useful and collect it

What is data desensitization?

Mobile phone name Data Masking, as the name implies, is to shield sensitive data, and to transform some data to some sensitive information such as ID number, cell phone number, card number, customer name, customer address, e-mail address, salary, etc., through the desensitization rule, and realize the reliable protection of privacy data. Common desensitization rules in the industry include replacement, rearrangement, encryption, truncation and mask. Users can also customize desensitization rules according to the desired desensitization algorithm.

II. Name desensitization cases

2.1 fixed length desensitization

The so-called fixed length desensitization means that the length of the replaced '*' is fixed, and the effect is shown in the figure below

SELECT
	CONCAT( LEFT ( real_name, 1 ), '*', RIGHT ( real_name, CHAR_LENGTH( real_name )- 2 ) ) AS username 
FROM
	sys_user;

2.2 non constant length desensitization

The so-called non fixed length desensitization is the replacement of '*' with variable length, which should be changed in real time according to the name;

Mode 1

SELECT
 rpad(
  substring(u.real_name, 1, 1),
  char_length(u.real_name),
  '*'
 )
FROM
 sys_user u;

Mode II

SELECT
  CONCAT( LEFT ( real_name, 1 ),  repeat('*',CHAR_LENGTH( real_name )- 1)) AS username 
FROM
  sys_user;

Definition of three functions

In the above example, we used some functions. Next, we'll learn about WeChat. Search for the Bug's Milo official account and pay attention to me, and work hard to write Bug.

CONCAT()

The CONCAT() function requires one or more string parameters and concatenates them into a string.

CONCAT(string1,string2, ... );

The CONCAT() function converts all parameters to string type before connecting. If any parameter is NULL, the CONCAT() function returns a NULL value.

mysql> SELECT CONCAT('MySQL','CONCAT');
+--------------------------+
| CONCAT('MySQL','CONCAT') |
+--------------------------+
| MySQLCONCAT              |
+--------------------------+
1 row in set

If you add a NULL value, the CONCAT function returns a NULL value as follows:

mysql> SELECT CONCAT('MySQL',NULL,'CONCAT');
+-------------------------------+
| CONCAT('MySQL',NULL,'CONCAT') |
+-------------------------------+
| NULL                          |
+-------------------------------+
1 row in set

LEFT()

The LEFT() function is a string function that returns the left part of a string of a specified length.

LEFT(str,length);

The LEFT() function accepts two parameters:

• str is the string to extract the substring.
• length is a positive integer that specifies the number of characters to be returned from the left.

The LEFT() function returns the leftmost length character in the str string. Returns a NULL value if the str or length parameter is NULL.

RIGHT()

This method is just the opposite of left(), and the usage is the same, so it will not be repeated;

CHAR_LENGTH()

Returns the length of the string str measured in characters. A multi byte character is counted as a character. This means that for a string containing five two byte characters, length () returns 10 and CHAR_LENGTH() returns 5.

rpad()

The RPAD(str,len,padstr) function indicates that padstr is used to fill the right side of the string str until the length is len. For example:

SELECT RPAD(123, 6, '0') AS str1, RPAD(123, 2, '0') AS str1;
|str1  |str1|
----+----+
|123000|12  |

repeat()

Returns the string after str is repeated count times. If count is less than 1, an empty string is returned. Returns NULL if str or count is null.

mysql> SELECT REPEAT('MySQL', 3);+-----------------------------------------------------| MySQLMySQLMySQL                                     +-----------------------------------------------------

Thank you for reading. I'll see you later

The following is my private wechat. If you have questions or suggestions about the article, or exchange technology and watch the circle of friends, you can add my wechat! I hope we can learn and grow together~