phpMyAdmin: trouble importing .sql files II

Now that he could successfully load large sql files into MySQL database, Maheshwor wrote back to me saying he stumbled upon another  bit of problem. The sql files contained duplicate data which he didnt want. For example,

INSERT INTO table1 VALUES 
("a", "apple", "fruit"), 
("a", aeroplane", "object"),
("b", "ball", "object");
... some more values;

INSERT INTO table1 VALUES
("a", "apple", "fruit"), 
("b", "ball", "object"), 
("c", "cat", "animal");

... and so on....

So an easy solution to this problem would be,

1. Open some Word Processor (or some text-editor which has  ’Find and Replace‘ facility). I shall guide you with Microsoft Word 2003/2007.
2. Now open the sql file from the text editor. For this, press Crtl+O. When the dialog box opens up, Change the Files of type to All files and select the sql file.  If the file is large, it may take a while to open the doc. Dont worry about formatting. Dont worry if it looks scrambled and irritating.
3. Now press Ctrl+F, a dialog box should open up with the Find tab selected. Get to Replace tab next to it.
4. In the Find what: box, type in INSERT and in Replace with: box, type INSERT IGNORE.
5. Save the document (in the sql format).

If you’ve already run the sql script and dont have much patience to do so again, try this:

1. The process is pretty simple, select all the unique data
2. Create a temporary table using that data
3. Delete the previous table
4. Rename the current table to the previous one.

Example:

mysql> CREATE TABLE tmp SELECT last_name, first_name, sex
    ->                  FROM person_tbl;
    ->                  GROUP BY (last_name, first_name);
mysql> DROP TABLE person_tbl;
mysql> ALTER TABLE tmp RENAME TO person_tbl;

If you found that boring (” yestooooo jhyau lagdo.. ” in Nepali), declare a primary key. Like this:

mysql> ALTER IGNORE TABLE person_tbl
    -> ADD PRIMARY KEY (last_name, first_name);

Declaring  a field as  a primary key doesnt let duplicate entries. Plus, you can add all the fields to the primary key. Example,

-> ADD PRIMARY KEY (last_name, first_name, sex, <blah>, <blah>, <so on>);

Okay Mahesh, hope this helps…

Later,