StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I decided to go with the "start off with a high ID" method I proposed.</p> <p>My script goes something like this (SUPER simplified from the real code).</p> <pre><code><?php /* Temp ID to add to each legacy ID This MUST be MUCH GREATER than any record counts combined can possibly reach In my case, all rows in my existing 7 tables (per data type) totalled about ~350,000, so I rounded up generously to be safe */ define('TEMP_ID', 400000); // Whole process repeated for each store, and numerous types of data $query = $db->query('SELECT * FROM store1_tickets'); // Insert statement $insert_sql = 'INSERT INTO tickets SET id = %1$d, legacy_id = %2$d, store = 1, customer = %3$d;'; // Update for other tables linked to this ID $update_sql = 'UPDATE logs SET ticket = %1$d WHERE ticket = %2$d;'."\n"; $update_sql.= 'UPDATE appointments SET ticket = %1$d WHERE ticket = %2$d;'; // Counter (kept for going between sessions for large datasets) $ticket_id = 0; while($row = $db->fetch_row($query)){ /* Insert with temp IDs for references to legacy data Note how the legacy id itself is stored un-touched, we are just worried about links to other tables */ $sql .= sprintf($insert_sql, ++$ticket_id, $row['id'], $row['customer'] + TEMP_ID); /* Now I store an update for the tables linked to this ticket to update to the new ID from the old (with temp added) */ $patch .= sprintf($update_sql, $ticket_id, $row['id'] + TEMP_ID); } ?> </code></pre> <p>I then run everything from <code>$sql</code> to enter the raw data, then run the <code>$patch</code> statements to fix all the links.</p> <p>Now once I've run all of these queries (stored in a .sql file and dumped in using a batch script), I run a few overall "clean-up" queries to reduce the IDs back down where needed.</p> <pre><code>UPDATE logs SET ticket = ticket - 400000 WHERE ticket > 400000; UPDATE appointments SET ticket = ticket - 400000 WHERE ticket > 400000; UPDATE tickets SET customer = customer - 400000 WHERE customer > 400000; </code></pre> <p>This worked beautifully for me with over 100MB of MySQL data from a few hundred tables. Took quite a bit of time to run the actual queries (I recommend the awesome script <a href="http://www.ozerov.de/bigdump/" rel="nofollow" title="BigDump by Alexey Ozerov">BigDump</a>), but it seems to have everything looking good so far.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload