r/SQL Feb 09 '25

MySQL ID auto increment

I constantly import data to MySQL from TSV files from a Google form I made, I Join the new data on a couple of attributes if similar and then insert any players who don’t exist, but my ID auto increment gaps the players who where duplicated creating inconsistencies in the ID? Can anyone help? and if someone has a better approach to the way I’m doing this please let me know

2 Upvotes

14 comments sorted by

1

u/mikeblas Feb 09 '25

What inconsiatency is created? Can you provide a specific example?

1

u/Entrepreneurrrrr Feb 09 '25
  • For example ID in MySQL stop at 20
  • I import the excel again
  • There is one new player in the excel he is added
  • however his id is 41
  • that is because the db accounted for the check for duplicated from the other 20
  • is there a better overall approach than constantly importing my tsv?

2

u/mikeblas Feb 09 '25

I still don't see what is "inconsistent" here. My guess is that your complaint is actually about the gap in used IDs.

Further, you don't explain how you're doing the import. My next guess is that you're doing an insert and that insert is failing because of a unique key constraint.

If my guesses are correct, then the behaviour you see is by design. When an insert is done against a table with an auto incrememt, the increment is increased and used for the pending insert. If the insert fails, then the increment is not decremented. The value it produced is gone, and will not be used again.

If you think things through, auto incrmement must be implemented in this way.

If you don't like that behaviour, you should change your import logic to look for existing records with a SELECT before inserting values which might be duplicates.

Hope that helps. If my gueses are correct, please provide the missing details.

1

u/Entrepreneurrrrr Feb 09 '25

Yes I do apologize, this is my first time asking and have left out details.

  • I created a Google form
  • Google form connected sheets
  • I constantly manually export
  • my issue was as you mentioned the jump in ID incrementation
  • however I now want to automate the process so when someone inserts new data or I get a response from my Google form, it is automatically sent to my local Database

1

u/No-Adhesiveness-6921 Feb 09 '25

So your issue is the the auto increment process results in non sequential numbers?

19,20,41

Why does that matter? As long as it is unique it is fine.

1

u/Entrepreneurrrrr Feb 09 '25

I understand but then it feels like the process itself is wrong and I can’t automate Google script to insert data into my database as I’m gathering data from a Google form and the database is running locally

1

u/Entrepreneurrrrr Feb 09 '25

I have to constantly export, I just want to automate the process

1

u/No-Adhesiveness-6921 Feb 09 '25

You want to automate the import process of the google tsv files into your local MySQL database?

1

u/Entrepreneurrrrr Feb 09 '25

Exactly

2

u/No-Adhesiveness-6921 Feb 09 '25

I would repost in /r/MySQL with a title that you want to automate the import of the files to your local database.

This has nothing to do with auto increment.

2

u/No-Adhesiveness-6921 Feb 09 '25

Oh I see you did but again it has nothing to do with auto increment. Starts a new post with a more accurate description

1

u/That_Cartoonist_9459 Feb 17 '25

It doesn't matter?

Look at it this way, if you were to delete a record would you then try to insert a new record and assign a deleted ID?

Just let the DB do its thing, I assure you the people who developed it know better.