• mysql插入不重复的uuid 代码：

select md5(uuid())

• mysql查询昨天的数据 xml加特殊处理

AND <![CDATA[DATE(a.pay_time) < CURDATE()]]>
        AND <![CDATA[DATE(a.pay_time) >= ADDDATE(CURDATE(),interval -1 day)]]>

• mysql数据库AES加密与解密
  AES加密：

HEX(AES_ENCRYPT(’加密内容‘, '秘钥'))

• AES解密

AES_DECRYPT(UNHEX('加密内容'), '秘钥')

• mysql json数据查询，参考：https://blog.csdn.net/ghostyusheng/article/details/84260831
  https://blog.csdn.net/qq_21187515/article/details/90760337

select JSON_EXTRACT(is_claim, '$[*].policyNo') as aa,is_claim from train_trip where is_claim is not null AND is_claim != '';

select REPLACE(JSON_EXTRACT(is_claim, '$[2].policyNo'),'"','')  as aa from train_trip 
 where is_claim is not null AND is_claim != '' and is_claim->'$[2].isClaim' = '1'

• AES解密且16进制转字符串

select a.create_date,b.policy_no,b.product_code,b.product_name,b.start_date,b.end_date,
       CONVERT(AES_DECRYPT(UNHEX(b.phone), 'xxx') USING utf8),
       CONVERT(AES_DECRYPT(UNHEX(b.insured_name), 'xxx') USING utf8),
       CONVERT(AES_DECRYPT(UNHEX(b.card_no), 'xxx') USING utf8),
       -- CONVERT(AES_DECRYPT(UNHEX(a.name), 'xxx') USING utf8),
       -- CONVERT(AES_DECRYPT(UNHEX(a.card_no), 'xxx') USING utf8),
       a.ticket_no,a.check_code,a.check_result
from caic_check a
left join caic_policy b
on a.policy_id = CONVERT(b.id USING utf8) COLLATE utf8_unicode_ci
where -- a.ticket_no='3892141708636' --
a.create_date>'2020-10-31'
order by a.create_date desc;

• mysql的in和not in的用法（特别注意not in结果集中不能有null）
• 修改排序规则

ALTER TABLE `jz_com_plat`.`hjb_insured`MODIFY`id`varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; 
ALTER TABLE`jz_com_plat`.`hjb_order`MODIFY`id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;