If I have a Parent and Child table in MySQL related by a foreign key, is it possible using a SQL statemen开发者_运维技巧t to move certain rows from Parent and the related rows from Child into archive tables (e.g. Parent_Archive and Child_Archive) in an atomic manner?
Use transactions - their whole purpose is to make the series of SQL statements atomic.
For example (NOT very optimized - can be improved with temp table):
START TRANSACTION;
INSERT Child_Archive
SELECT DISTINCT
Child.* FROM Child, Parent
WHERE Child.FK = Parent.PK
AND Parent.something=11;
DELETE Child WHERE FK IN (
SELECT DISTINCT PK FROM Parent WHERE Parent.something=11);
INSERT Parent_Archive
SELECT DISTINCT * FROM Parent WHERE Parent.something=11;
DELETE Parent WHERE Parent.something=11;
COMMIT;
精彩评论