Background
One of the MySQL table contains base64 JSON fields impact the server performance thus needs removed.
Schema of the device_event:
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%';
|