CODEWITHSUNDEEP
mysqlphpmyadmin
Mark Locklear
Mark Locklear

Reputation: 5325

Auto increment column in mysql when importing with phpmyadmin

I have a table in mysql with the following headings:

staff_id,dept_id,role_id,username,firstname,lastname,passwd,backend,email,phone,phone_ext,mobile,signature,lang,timezone,locale,notes,isactive,isadmin,isvisible,onvacation,assigned_only,show_assigned_tickets,change_passwd,max_page_size,auto_refresh_rate,default_signature_type,default_paper_size,extra,permissions,created,lastlogin,passwdreset,updated

staff_id is a primary key value and is set to AUTO_INCREMENT.

I found the solution by Queue in this post really helpful, although when the data is being imported the staff_id column is not being auto-incremented. I am inserting the column names in the Format-Specific Options in phpmysql. I can only get it to populate if the staff_id value exists in the csv file I am trying to import.

So if the data looks like this:

2,1,1,agent,Mista,Busta,NULL,NULL,[email protected],,NULL,,,NULL,NULL,NULL,<p>this is an agent; mista busta; [email protected]</p>,1,0,1,0,0,0,0,0,0,none,Letter,"{""def_assn_role"":true}","{""user.create"":1,""user.delete"":1,""user.edit"":1,""user.manage"":1,""user.dir"":1,""org.create"":1,""org.delete"":1,""org.edit"":1,""faq.manage"":1}",2020-02-04 10:18:42,NULL,NULL,2020-02-04 10:18:42

...note the first '2' is the staff_id. What I would like do is have this in the csv:

,1,1,agent,Mista,Busta,NULL,NULL,[email protected],,NULL,,,NULL,NULL,NULL,<p>this is an agent; mista busta; [email protected]</p>,1,0,1,0,0,0,0,0,0,none,Letter,"{""def_assn_role"":true}","{""user.create"":1,""user.delete"":1,""user.edit"":1,""user.manage"":1,""user.dir"":1,""org.create"":1,""org.delete"":1,""org.edit"":1,""faq.manage"":1}",2020-02-04 10:18:42,NULL,NULL,2020-02-04 10:18:42

...leaving the staff_id column blank and allowing mysql to auto-populate (auto_increment) as it sees fit.

Upvotes: 0

Views: 3505

Answers (1)

Zak
Zak

Reputation: 7515

AUTO INCREMENT is set at the server level within MySQL, and can be overwritten if assigned manually within a CSV or other import. If you simply exclude the entire column from your import, you'll allow MySQL to do what is set as default for that column, and automatically assign ID's, since it doesn't think you want to assign them yourself.

Also as a side note, if you import more than once without using TRUNCATE TABLE -- MySQL will pick up on the last inserted ID and move on from there, even if the table is empty. So if you ever want to start over from 1 you'll have to trucate the table.

Upvotes: 1

Related Questions