regexp_time_limit&&regexp_stack_limit 参数

报错：

Timeout exceeded in regular expression match.

 

为了针对表中的手机号码进行加密

SQL 语句：

SELECT
ID
,PAYMENT
,MODIFIED
,PAY_TIME
,POST_FEE
,DISCOUNT_FEE
,STATUS
,CREATED
,TID
,TYPE
,ACCOUNTINFO_ID
,RECEIVED_PAYMENT
,COUPON_FEE
,PAID_COUPON_FEE
,ISCAINIAO
,PRICE
,TOTAL_FEE
,ADJUST_FEE
,BUYER_NICK
,SELLER_NICK
,ORDER_TAX_FEE
,SELLER_MEMO
,CONSIGN_TIME
,END_TIME
,PLATFORM
,SHOPCODE
,BUYER_RATE
,BUYER_MEMO
,BUYER_MESSAGE
,BUYER_FLAG
,SELLER_FLAG
,PAY_NO
,PAY_WAY
,REFUND_STATUS
##,RECEIVER_ADDRESS
##,RECEIVER_MOBILE
,md5(RECEIVER_ADDRESS) as RECEIVER_ADDRESS ####加密地址
,case when regexp_replace(RECEIVER_MOBILE,'\s+','') regexp '^[0-9]+$' =1 ####明文
then md5(RECEIVER_MOBILE)
when regexp_replace(RECEIVER_MOBILE,'\s+','') regexp '^[0-9]+\+[0-9\]+|^\+[0-9]+[0-9\]+$' =1 ##-半脱敏
then md5(RECEIVER_MOBILE)
else RECEIVER_MOBILE end as user_id ####加密 空 全脱敏不做处理
,case when regexp_replace(RECEIVER_MOBILE,'\s+','') regexp '^[0-9]+$' =1 ####明文
then 0
when regexp_replace(RECEIVER_MOBILE,'\s+','') regexp '^[0-9]+\+[0-9\]+|^\+[0-9]+[0-9\]+$' =1 ##-半脱敏
then 1
when regexp_replace(RECEIVER_MOBILE,'\s+','') regexp '[a-zA-Z]' =1 ## 加密
then 2
when regexp_replace(RECEIVER_MOBILE,'\s+','') regexp '^\*+$' =1
or length( regexp_replace(RECEIVER_MOBILE,'\s+','') )=0 ##-全脱敏 与 空字符串
then 3
end as encrypt_type ####user_id加密类型 0 明文 1半脱敏 2加密 3全脱敏与空字符串
,RECEIVER_NAME
,RECEIVER_STATE
,RECEIVER_ZIP
,RECEIVER_DISTRICT
,RECEIVER_CITY
,RECEIVER_PHONE
,RECEIVER_TOWN
,RECEIVER_COUNTRY
,SOURCE
,SOURCEDATA
,BATCHNO
,tag
,up_time
,oaid
,encrypt_address
,encrypt_name
,encrypt_mobile
,is_decrypt
from order_info_01

 

 

 

参考官网解析：

把此两值调成最大，还是一样报错，这次报堆内存溢出

 

 Overflow in the regular expression backtrack stack.

结论：

SQL取字段有问题，取的手机段，因为我们的数据没有清洗，

 

 里面有‘，中文和括号等造成，

修改方式：

第一步清洗

或直接取RECEIVE_PHONE

SELECT
ID
,PAYMENT
,MODIFIED
,PAY_TIME
,POST_FEE
,DISCOUNT_FEE
,STATUS
,CREATED
,TID
,TYPE
,ACCOUNTINFO_ID
,RECEIVED_PAYMENT
,COUPON_FEE
,PAID_COUPON_FEE
,ISCAINIAO
,PRICE
,TOTAL_FEE
,ADJUST_FEE
,BUYER_NICK
,SELLER_NICK
,ORDER_TAX_FEE
,SELLER_MEMO
,CONSIGN_TIME
,END_TIME
,PLATFORM
,SHOPCODE
,BUYER_RATE
,BUYER_MEMO
,BUYER_MESSAGE
,BUYER_FLAG
,SELLER_FLAG
,PAY_NO
,PAY_WAY
,REFUND_STATUS
##,RECEIVER_ADDRESS
##,RECEIVER_MOBILE
,md5(RECEIVER_ADDRESS) as RECEIVER_ADDRESS ####加密地址
,RECEIVER_MOBILE
,RECEIVER_NAME
,RECEIVER_STATE
,RECEIVER_ZIP
,RECEIVER_DISTRICT
,RECEIVER_CITY
,case when regexp_replace(RECEIVER_PHONE,'\s+','') ####明文
then md5(RECEIVER_PHONE)
when regexp_replace(RECEIVER_PHONE,'\s+','') ##-半脱敏
then md5(RECEIVER_PHONE)
else RECEIVER_PHONE end as user_id ####加密 空 全脱敏不做处理
,case when regexp_replace(RECEIVER_PHONE,'\s+','') ####明文
then 0
when regexp_replace(RECEIVER_PHONE,'\s+','') ##-半脱敏
then 1
when regexp_replace(RECEIVER_PHONE,'\s+','') ## 加密
then 2
when regexp_replace(RECEIVER_PHONE,'\s+','')
or length( regexp_replace(RECEIVER_PHONE,'\s+','') ) ##-全脱敏 与 空字符串
then 3
end as encrypt_type ####user_id加密类型 0 明文 1半脱敏 2加密 3全脱敏与空字符串
,RECEIVER_TOWN
,RECEIVER_COUNTRY
,SOURCE
,SOURCEDATA
,BATCHNO
,tag
,up_time
,oaid
,encrypt_address
,encrypt_name
,encrypt_mobile
,is_decrypt
from order_info_01