0

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,agent@company.org,,NULL,,,NULL,NULL,NULL,<p>this is an agent; mista busta; agent@company.org</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,agent@company.org,,NULL,,,NULL,NULL,NULL,<p>this is an agent; mista busta; agent@company.org</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.

Mark Locklear
  • 5,044
  • 1
  • 51
  • 81
  • 1
    Do not leave staff_id blank, it's a required field -- Instead remove it completely (including the comma) -- Also make sure you remove the reference to staff_id in the actual insert statement if applicable. (IE simply removing the entire column, including header .. ) -- MySQL will see no reference to it during import, and obey the "auto increment", assigning new IDs – Zak Feb 04 '20 at 19:10
  • Create `staff_id` as simple non-indexed field,import the data as is, then drop `staff_id` and create as needed - auto_increment, primary key and so on. – Akina Feb 04 '20 at 19:31
  • @Zak right on bro! That worked. Put it in an answer and I'll accept it. – Mark Locklear Feb 04 '20 at 19:50
  • NULL also will be replaced to auto increment value – Slava Rozhnev Feb 04 '20 at 19:52

1 Answers1

1

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.

Zak
  • 6,976
  • 2
  • 26
  • 48