Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Is there a way to mysqldump a table without some fields?

Let me explain:
I have a MySQL database called tests. In tests I have 3 tables: USER, TOTO and TATA. I just want to mysqldump some fields of table USER, so excluding some fields like mail, ip_login, etc.

How can I do this?

share|improve this question

migrated from serverfault.com Feb 17 '13 at 13:24

This question came from our site for professional system and network administrators.

3 Answers 3

Try this:

SELECT column1, column2, column10 FROM USER INTO OUTFILE "c:/user.csv" FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY "\n";

For more option read the documentation page.

share|improve this answer
    
Perfect way, thanks! –  user160293 Feb 16 '13 at 18:38

This can't be done with mysqldump directly. However, you can create a temporary table or a view that does not contain the data in question, and then dump the resulting data. In the case of a view, I think you'll have to use INTO OUTFILE rather than mysqldump to get the data you want, but using a view instead of a temporary table has the advantage of not taking up any extra space in the DBMS.

The other option is to create a parser for the file to strip the fields you don't want. To put it mildly, this can get complicated very quickly—that is, it will get complicated assuming you want to create a parser that's robust in the face of multibyte characters, escaped string separators, etc., etc. But for the trivial or one-time-use case, it may still be a valid option.

share|improve this answer

You can also use Phpmyadmin to do this. Create a query to select the fields you want, then choose the "export" function at the bottom of the page (this exports your query results, not the whole table), and choose the "Custom" option. You can then choose SQL as your format - if you want you can then create a series of UPDATE queries rather than INSERTS. Can often be a quick way to get something like this done.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.