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?


Try this:

SELECT column1, column2, column10
INTO OUTFILE "c:/user.csv"

For more option read the documentation page.


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.


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.


Many DB admin tools have the ability to do a custom import. In particular, I accomplished this using Toad for MySQL to transfer data between tables using autoincrement PKs.

I saved a query result to Excel using the Export Wizard.

Then you can exclude columns using the Import Wizard by setting the Source Field to: Skip This Column

import wizard

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy