Friday, April 11, 2008

Truncate Table Example for MySQL

I have been upgrading my phpbb forum from 2.X to 3.X. It has been a slog so far and mostly due to spammers. Before the 3.X converter would allow me to complete the conversion process, I had to delete most of the spammer usernames because they were essentially dups (e.g. User, USer, user, etc) but with different UID's . There were about 7,000 rows in the phpbb_user table - now there is a fraction of that left. Good.

Anyways, after exporting approximately 7000 rows to a file, I truncated the table. Here is an example.

TRUNCATE TABLE `phpbb_users`;

Here is the cleaned up (unique usernames) version of the file, which was re-ingested into the phpbb_users 2.X table.

INSERT INTO `phpbb_users` VALUES(2, 1, 'adam123', '49ad5...,NULL);
INSERT INTO `phpbb_users` VALUES(3, 1, 'laurahing', 'a3dcb4d2..., NULL);
INSERT INTO `phpbb_users` VALUES(4, 1, 'joshman', '49ad5b0771f..., NULL);
INSERT INTO `phpbb_users` VALUES(5, 0, 'ipsofacto', 'b63fc..., NULL);
INSERT INTO `phpbb_users` VALUES(6, 1, 'FitForFree', '04305e8e..., NULL);
INSERT INTO `phpbb_users` VALUES(7, 1, 'banchee', '8a645340...,NULL);
...

#####
I saw another meta data query asking how to limit the deletes by time for mysql. Here is a simple example.

DELETE FROM classified_ads WHERE user = 'esoft123'
ORDER BY timestamp_col LIMIT 10;

No comments: