Gemäss der neuen DSGVO gilt ja das Prinzip der Datenminimierung und Daten dürfen nur so lange gespeichert werden wie es notwendig ist.
Aus diesem Grund wollen wir alle Kundenadressen älter als x Jahre löschen und alle Bestellungen älter als 2 Jahre anonymisieren. (Bitte keine Diskussion warum und weshalb)
Mein aktueller Ansatz dies regelmässig über sql Befehle durchzuführen. Meine ersten Test sind recht vielversprechend und ich konnte keine Fehler entdecken. Ich bin aber eher kein SQL Spezialist.
Vielleicht mag ja mal der eine oder andere drüberschauen oder es ausprobieren und Feedback geben.
1. Kunden Löschen Welche Kunden sind es?
SELECT DISTINCT customers_id,customers_lastname
FROM customers WHERE customers_id > 1 AND customers_id NOT IN (
SELECT DISTINCT customers_id FROM orders WHERE date_purchased > DATE_SUB(CURDATE(),INTERVAL 730 DAY)
)
Löschbefehle für die unterschiedlichen Datenbanken
DELETE FROM `address_book` WHERE customers_id > 1 AND customers_id NOT IN (SELECT DISTINCT customers_id FROM orders WHERE date_purchased > DATE_SUB(CURDATE(),INTERVAL 730 DAY));
DELETE FROM `customers` WHERE customers_id > 1 AND customers_id NOT IN (SELECT DISTINCT customers_id FROM orders WHERE date_purchased > DATE_SUB(CURDATE(),INTERVAL 730 DAY));
DELETE FROM `customers_basket` WHERE customers_id > 1 AND customers_id NOT IN (SELECT DISTINCT customers_id FROM orders WHERE date_purchased > DATE_SUB(CURDATE(),INTERVAL 730 DAY));
DELETE FROM `customers_basket_attributes` WHERE customers_id > 1 AND customers_id NOT IN (SELECT DISTINCT customers_id FROM orders WHERE date_purchased > DATE_SUB(CURDATE(),INTERVAL 730 DAY));
DELETE FROM `customers_info` WHERE customers_info_id > 1 AND customers_info_id NOT IN (SELECT DISTINCT customers_id FROM orders WHERE date_purchased > DATE_SUB(CURDATE(),INTERVAL 730 DAY));
DELETE FROM `customers_ip` WHERE customers_id > 1 AND customers_id NOT IN (SELECT DISTINCT customers_id FROM orders WHERE date_purchased > DATE_SUB(CURDATE(),INTERVAL 730 DAY));
DELETE FROM `customers_memo` WHERE customers_id > 1 AND customers_id NOT IN (SELECT DISTINCT customers_id FROM orders WHERE date_purchased > DATE_SUB(CURDATE(),INTERVAL 730 DAY));
DELETE FROM `customers_status_history` WHERE customers_id > 1 AND customers_id NOT IN (SELECT DISTINCT customers_id FROM orders WHERE date_purchased > DATE_SUB(CURDATE(),INTERVAL 730 DAY));
2. Bestellungen anonymisierenWelche Bestellungen sind es?
SELECT customers_id,customers_firstname,customers_lastname,billing_firstname, delivery_name FROM orders WHERE customers_id>1 AND date_purchased < DATE_SUB(CURDATE(),INTERVAL 1000 DAY);
und updaten
UPDATE orders SET
customers_name='ab',
delivery_name='ab',
billing_name='ab',
customers_firstname='a',
delivery_firstname='a',
billing_firstname='a',
billing_name='ab',
billing_firstname='a',
billing_lastname='b',
customers_company ='c',
delivery_company ='c',
billing_company ='c',
customers_street_address ='d 2',
delivery_street_address ='d 2',
billing_street_address ='d 2',
customers_telephone = '4 ',
customers_ip = '127.0.0.1'
customers_email_address = 'dd@ail.com '
WHERE customers_id>1 AND date_purchased < DATE_SUB(CURDATE(),INTERVAL 1000 DAY);
Welche Kommentare müssen gelöscht werden?
SELECT DISTINCT orders_id FROM orders_status_history WHERE orders_id IN (SELECT orders_id FROM orders WHERE date_purchased > DATE_SUB(CURDATE(),INTERVAL 730 DAY)))
und Löschen
UPDATE orders_status_history SET comments='v ' WHERE orders_id IN ( SELECT orders_id FROM orders WHERE date_purchased < DATE_SUB(CURDATE(),INTERVAL 730 DAY));
Linkback: https://www.modified-shop.org/forum/index.php?topic=38640.0