mysql脱敏测试环境实现物理脱敏

实现场景及需求：
因为业务需求：包含顾客隐私不被泄露，将在测试环境进行数据库脱敏操作

python 3.6

实现方法，截取订单后四位数字，截取顾客前7位电话号码，实现字符串拼接，将7位电话号码和后4位订单号进行拼接，然后直接修改数据库

import pymysql

conn = pymysql.connect(
    host='serverIP',
    port=3306,
    user='username',
    passwd='password',
    db='DBname',
    charset='utf8')

cursor = conn.cursor()
sql_select = 'select shop_id,phone_num from shopcar'
res = cursor.execute(sql_select)
result = cursor.fetchall()

for i in result :
        only_id = i[0]
        only_int = str(only_id)[-4:]
        phone_num = i[1]
        phone_int=str(phone_num)[:7]
        m_sql=phone_int+only_int
        min_sql=int(m_sql)
        sql='update shopcar set phone_num="%s" where shop_id="%s"' % (m_sql,only_id)
        cursor.execute(sql)

conn.commit()
cursor.close()
conn.close()

结果：

mysql> select * from shopcar;
+---------+-------------+--------------------+
| shop_id | phone_num   | auth_num           |
+---------+-------------+--------------------+
| 1245780 | 15816905780 | 429322199008084023 |
| 1245781 | 15816905781 | 429322199008084024 |
| 1245782 | 15816905782 | 429322199008084022 |
| 1245783 | 15816905783 | 429322199008084011 |
+---------+-------------+--------------------+
4 rows in set (0.00 sec)

mysql> 

转载于:https://blog.51cto.com/yibeishui/2117017