Remove Duplicates from MySQL table
Ever had to remove duplicate entries in a table because some GENUIS thought that it would be good design to not put a unique index on a field or better yet was too lazy to put in the proper checking to make sure that you are not inserting duplicates ? Well here is something to correct that sloppy laziness…..There are four steps to get this resolved.
Step 1. Make a backup of the table you are about to change in the event everything breaks.
mysqldump --add-drop-table -h db01.example.net -u dbocodex -p dbwp | bzip2 -c > blog.bak.sql.bz2
Step 2. Move the non duplicates (unique tables) into a temporary table
CREATE TABLE new_table AS SELECT * FROM old_table WHERE 1 GROUP BY [COLUMN TO remove duplicates BY];
Step 3. Delete the old table
DROP TABLE old_table;
Step 4. Rename the new_table to the name of the old_table
RENAME TABLE new_table TO old_table;
CocoaMySQL – MAC database management
The Mac database management app, CocoaMySQL, is the brainchild of Lorenz Textor. Lorenz was the creator and the main developer of CocoaMySQL from its early beginnings in 2003. Without Lorenz’s contributions to CocoaMySQL, the Mac database landscape would be very different today. Sequel Pro is the successor of CocoaMySQL. Download Sequel Pro from the dowload page. A copy of CocoaMySQL 0.7 beta 6 is available from the release archives for users running Mac OS X Tiger and Mac OS X Panther.CocoaMySQL is an application used to manage MySQL databases (locally or over the internet). It lets you add and remove databases and tables, change fields and indexes, view and filter the content of tables, add, edit and remove rows, perform custom queries and dump tables or entire databases.CocoaMySQL is written in Cocoa and Objective-C and uses the SMySQL framework by Serge Cohen to connect to the MySQL-Server. You can manage servers that are local and remote.
For more information: http://www.sequelpro.com/cocoamysql.html
Google Code Home: http://code.google.com/p/sequel-pro/
phpminiadmin – lightweight phpMyAdmin for MySQL
I have on some projects for clients come across server environments where the access that I would be allowed for the duration of the project would not allow me to make database changes, and in some rare cases not access at all to anything that would allow me to see, view or manipulate the database. Now of course I this can be done by code, but why reinvent the wheel or should I say I have been spoiled ( or relied upon ) phpMyAdmin for this ability. I would like to share a lightweight simple tool that I have come across in situations like this that more than fits the bill, since in these cases you will more than likely have the dbname, dbuser and dbpass and the ability to upload scripts, this tool fits the job nicely. phpminiadmin is an extremely lightweight alternative to heavy phpMyAdmin for quick and easy access MySQL databases. Instead of installing huge phpMyAdmin (~11Mb) and trying to figure out how to use all it’s features, just upload one ~10Kb file and it’s ready to use!
- then access it via the browser (ex. http://yoursite.com/phpminiadmin.php)
- script will ask you for DB login/pwd
- after successfull db login you will see area where you able to enter any SQL commands (select, update, insert, etc.)
- even if you don’t know SQL it’s still easy to manage tables in DB, export and import data using ‘quick links’ on the top bar
For more information: http://phpminiadmin.sourceforge.net/