19

My database structure is: 

`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
`address` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
`tel` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
`longitude` int(6) NOT NULL,
`latitude` int(6) NOT NULL,
`type` varchar(1) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`id`)

As I want to backup, but I don't want to include id when export to file (.sql)
How can I do?

Cœur
  • 37,241
  • 25
  • 195
  • 267
red23jordan
  • 2,841
  • 10
  • 41
  • 57
  • Why would you do that? Isn't the ID used in other tables, or URL's, or whatever? If you have, say, a thousand users and re-import the export without the ID's, user #1 will now have become user #1001. – CodeCaster Nov 15 '11 at 14:53
  • possible duplicate of [How do I export particular column in MySQL using phpmyadmin?](http://stackoverflow.com/questions/4486743/how-do-i-export-particular-column-in-mysql-using-phpmyadmin) – T.Todua May 23 '15 at 11:08

2 Answers2

58

Go to SQL tab and manually write your select query without id column (SELECT name, address, ... FROM your_table). Then once you get the table with the results, scroll down to the bottom of the page and you'll see export icon inside the gray "Query results operations" fieldset. This should work for you.

Screenshot per request (see very bottom): enter image description here

matino
  • 17,199
  • 8
  • 49
  • 58
  • I do not see thegray "Query operations" fieldset that you are referring to. Can you share a screenshot? – pal4life Aug 22 '12 at 17:57
  • I think the reason you ask is that you will re-import and have sequential ID's from auto-increment. But if you try to import it this way you will get an error saying your column numbers don't match. Try this: SELECT '', name, address FROM table ===> This will leave the id column empty so it will be auto incremented. – Tarik May 31 '17 at 21:45
  • I get message: This table does not contain a unique column. Grid edit, checkbox, Edit, Copy and Delete features are not available. also export button is not there if i remove id from my table! I do not know maybe it can be done from specific version of phpMyadmin – Ingus Apr 11 '18 at 05:35
  • 1
    and when have a half million rows ? – Bruno Ribeiro Nov 20 '18 at 14:21
5

You can copy the table, and remove the ID primary column.

You just need navigate to Operations tab and copying the table enter image description here

When it has copied, you go to Structure tab, and remove primary column

After remove the primary column, just go Export tab.

Bruno Ribeiro
  • 1,280
  • 16
  • 21