Regex Replace in MySQL Nov 25th 2021 Words: 110

Background

One of the MySQL table contains base64 JSON fields impact the server performance thus needs removed.

Schema of the device_event:

Column Type
event JSON

Solution

Preview JSON contains base64 data:

1
SELECT SUBSTRING(event, 1, 400) FROM device_event WHERE event LIKE '%data:image/jpeg;base64%' LIMIT 10;

Preview regex replace result:

1
SELECT REGEXP_REPLACE(event, '"data:image/jpeg;base64[^"]+"', 'null') FROM device_event WHERE event LIKE '%data:image/jpeg;base64%' LIMIT 10;

Execute the update query:

1
UPDATE device_event SET event = REGEXP_REPLACE(event, '"data:image/jpeg;base64[^"]+"', 'null') WHERE event LIKE '%data:image/jpeg;base64%';