{"id":1228,"date":"2022-11-23T09:20:59","date_gmt":"2022-11-23T09:20:59","guid":{"rendered":"https:\/\/editjournal.redakt.eu\/faxmodem\/?p=1228"},"modified":"2022-11-23T11:16:11","modified_gmt":"2022-11-23T11:16:11","slug":"non-destructive-duplicate-removal-from-a-mysql-table","status":"publish","type":"post","link":"https:\/\/editjournal.redakt.eu\/faxmodem\/blog\/development\/non-destructive-duplicate-removal-from-a-mysql-table\/","title":{"rendered":"Non-destructive duplicate removal from a MySQL table"},"content":{"rendered":"
    \n
  1. \n

    Create a new table<\/p>\n

    CREATE TABLE records_new LIKE records;<\/code><\/pre>\n<\/li>\n
  2. \n
    Add composite UNIQUE<\/code> constraint preventing duplicates in the future<\/p>\n
    ALTER TABLE records_new ADD UNIQUE idx_composite_UNIQUE (site_id ASC, session_id ASC, query ASC, remote_addr ASC);<\/code><\/pre>\n<\/li>\n
  3. \n
    Copy records over.<\/p>\n
    INSERT INTO records_new SELECT * FROM records ORDER BY id ON DUPLICATE KEY UPDATE records_new.created=NOW();<\/code><\/pre>\n
    The above updates created<\/code> to NOW()<\/code> on every duplicate hit, but if you don't want that, change it to ON DUPLICATE KEY UPDATE records_new.id=records_new.id<\/code> (where id<\/code> is the table's PRIMARY KEY<\/code>), which does nothing<\/a><\/p>\n<\/li>\n
  4. \n
    Swap the table names<\/p>\n
    RENAME TABLE records TO records_old, records_new TO records;<\/code><\/pre>\n<\/li>\n<\/ol>\n
    This has been to a large extent based on C\u00e9sar Revert-Gomar's answer<\/a>, so credits to the author.<\/p>\n","protected":false},"excerpt":{"rendered":"
    Create a new table CREATE TABLE records_new LIKE records; Add composite UNIQUE constraint preventing duplicates in the future ALTER TABLE records_new ADD UNIQUE idx_composite_UNIQUE (site_id ASC, session_id ASC, query ASC, remote_addr ASC); Copy records over. INSERT INTO records_new SELECT * FROM records ORDER BY id ON DUPLICATE KEY UPDATE records_new.created=NOW(); The above updates created to… Continue reading Non-destructive duplicate removal from a MySQL table<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[706],"tags":[720],"class_list":["post-1228","post","type-post","status-publish","format-standard","hentry","category-development","tag-mysql","entry"],"_links":{"self":[{"href":"https:\/\/editjournal.redakt.eu\/faxmodem\/wp-json\/wp\/v2\/posts\/1228","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/editjournal.redakt.eu\/faxmodem\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/editjournal.redakt.eu\/faxmodem\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/editjournal.redakt.eu\/faxmodem\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/editjournal.redakt.eu\/faxmodem\/wp-json\/wp\/v2\/comments?post=1228"}],"version-history":[{"count":4,"href":"https:\/\/editjournal.redakt.eu\/faxmodem\/wp-json\/wp\/v2\/posts\/1228\/revisions"}],"predecessor-version":[{"id":1233,"href":"https:\/\/editjournal.redakt.eu\/faxmodem\/wp-json\/wp\/v2\/posts\/1228\/revisions\/1233"}],"wp:attachment":[{"href":"https:\/\/editjournal.redakt.eu\/faxmodem\/wp-json\/wp\/v2\/media?parent=1228"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/editjournal.redakt.eu\/faxmodem\/wp-json\/wp\/v2\/categories?post=1228"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/editjournal.redakt.eu\/faxmodem\/wp-json\/wp\/v2\/tags?post=1228"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}