Right, as I've said in previous posts, I'm not that great with MySQL. I'd just like to find out if there is actually a way to 'reset' an auto increment column? - IE. When a row is removed, push back the inc by 1. If you know of a way so it updated all ID's bar the last one. Simply because there's data in other tables that are linked to that data, and I don't have access to that table, which is kinda funny, but to my knowledge, it acts like a foreign key, but it isn't actually a foreign key, someone decided it would be a good idea to do all that by hand!
It genuinely amazes me how some people decide to structure a database.... It genuinely f$!king amazes me, even though in this job, because my company buys out up and coming companies (who NEVER have anyone that's remotely good with tech employed).... Usually the existing applications/services/databases they have in place, they work, but f$!k me, they have no idea what they're doing, and when I say they work, I mean they do, but they shouldn't, it's actually disgusting some of the stuff I've encountered, you wouldn't believe me if I told you.....
Some of the source code I've seen.... I .........I. t ....... It ... It genuinely makes me want to be blind....
By my experience it's actually better to leave auto-increments alone and not touch them. Assuming you're running 64bit OS and database (which... why wouldn't you?) it's going to be some time until an auto-increment hits max_int.
That being said it is actually possible to set the auto-increment start point on your own (it doesn't have to start a 0), and you can use the same method to "reset" the auto-increment counter.
ALTER TABLE `table` AUTO_INCREMENT = x;
You can get the last value from within MySQL too of course (untested, because my server-board died):
SELECT `id` FROM `table` ORDER BY `id` DESC LIMIT 1;
*should* yield you the highest current index. Stick that value in a variable and apply it to the auto-increment (untested, again):
SET @increment = SELECT `id` FROM `table` ORDER BY `id` DESC LIMIT 1;
ALTER TABLE `table` AUTO_INCREMENT = @increment + 1;
---
If you're deleting rows in between I don't think there's an easy way of "compressing" the table and have no unused indexes. So if you're doing something like this:
You won't get rid of the gaps as easily. You "could" probably build a loop that loops through all rows and updates each row but... eh You'd need select the highest index (or make a SELECT on the AUTO_INCREMENT), then loop through each, check if it's there, remember that number, continue the loop until you hit a row that exists, update the row and continue... not really easy for no benefit really. And that doesn't even cover the problem with using those indexes in other tables.
PS: Forum bugged? I can't draw a horizontal line oO
No; and don't do this. Serial IDs are not "record numbers" or anything like. They only need to be unique, and everything else is an implementation detail.
If you have code which relies on Serial IDs being sequential, you should fix that code.
Is there a specific problem you need to solve?
"eh" is right. this would be error-prone at best. and in large DBs, it would waste a horrendous amount of time.
The horizontal lines work here, but look between the 2 last codeboxes, for some reason it won't convert that one line... who knows (and yes, there are empty lines in between :P)
If that's the case then screw it, I was only trying to do this because I made a bunch of test rows to test a backend I made for the web admin and sales team, it works 100% fine. Updates, adds and deletes as expected, it's fast and simple to use.... But ignore that, if it is more hassle and effort than what it's worth, then screw it.
@_adrian Considering yourself and @mihawk90 have said it's just more hassle than what it's worth, screw it! ... I'm glad you've informed me of this though, I am still new to using SQL and playing around with a lot of server end stuff, I mean in my defence, there's only so much you can really do without access to a server that has all the toys you need to make large websites. When I say large, I mean one website we've finished, literally what feels like 5 minutes ago, but is probably a week or two ago, not even an entire month yet, it's already generated £2.4m in profit, yes, profit.
I would, however, I'm still not 100% sure how the tables work, like I don't even know why some columns exist, I have asked senior developers, and I didn't really get an answer, and they're busy with their own work which is fair enough. I don't even have access to the SQL server personally, I mean I just write scripts and hope for the best, I just get told what tables are used, what columns are used and what needs to be done.
It's not even a problem, it works fine and runs just fine as it is, I just thought it would be nice to fix it a little bit. It's literally just my O.C.D.
If you don't have one yet (which apparently...), you can also add it later. There are a few ways, but the "correct" one seems to be via `MODIFY COLUMN`.
ALTER TABLE `table`
MODIFY COLUMN `column2`
your_previous_column_definition COMMENT "OH SHIT I FORGOT MY COMMENT";
If you don't know the previous column definition (because it needs to be 1 by 1 or else it may or may not screw up your tables) you can read it out using:
SHOW CREATE TABLE `table`;
Let them give you at least a partial copy and just tell them you don't wanna screw over the live system. You shouldn't be working directly on a live system in the first place, especially if it's a big site like you suggest it is. On a side-note: That code probably doesn't rely on sequential IDs in the first place (or else it would have broken already), so best just leave it alone
PS: apparently the forum doesn't like creating horizontal lines between 2 code blocks, or formatting anything really... if I just append a codeblock here all the line breaks and the quote goes away... @wendell ?
Thankfully, I'm not that much of a newbie with MySQL, but I mean there is not comments, the table already exists, and I think it was made through a GUI of some sort, I'm not even sure how the tables were initially made. They were made and have been in use long before I even started this job! ... That's why I try to ask the senior developers, there's no documentation, and some of the data is so vague, and the title of the column, again, so vague, it can be a little confusing....
I like that....
I totally agree with you there, but it would be nice to at least know how the structure of the database works and how everything links up together, instead, tis mostly educated guess work on my behalf, I have asked senior developers to check it through, they seem fine with what I've done, so I can't really say I've done a bad job.... I guess?
It does, kinda, but that is why I plan on leaving it alone, I don't wanna break it, I haven't actually written a script to try and change the increment part of the table(s).... If it ain't broken, don't fix it I guess? .... Like I said before, it's not even a problem, it's just my O.C.D. going nuts....
Could that just be down to your browser? - I've never had any issue(s) with formatting anything.... Strange, still good to point out, but very strange...
Well yeah, you do get that from a copy as well Is there no development environment around? Kinda asking for trouble there... Just do a random DROP TABLE somewhere in there and they'll do it as fast as possible :X
Well the formatting isn't done by the browser, but by the Forum Software, but for reference... Firefox ESR 52.2.1 Yesterday it was at home Vivaldi.... whatever the current snapshot is
I know it's done by the forum software, but I assumed that there could be an issue with transferring the data or maybe it's how your browser was rendering & storing the data, I don't know? .... I'm literally taking a shot in the dark here...
and your ocd will relax a bit once you process that "that's not what it's for"
↑ THIS
that's not really encouraging. if you're a new dev, especially a junior dev, you shouldn't be guessing at sure things. a senior dev should be assigned to you and be making sure you're clear on everything, from how things work to what you're doing to how pleased they are with your performance.
(and yes, there are empty lines in between :P) 
... I'm glad you've informed me of this though, I am still new to using SQL and playing around with a lot of server end stuff, I mean in my defence, there's only so much you can really do without access to a server that has all the toys you need to make large websites. When I say large, I mean one website we've finished, literally what feels like 5 minutes ago, but is probably a week or two ago, not even an entire month yet, it's already generated £2.4m in profit, yes, profit. 
... That's why I try to ask the senior developers, there's no documentation, and some of the data is so vague, and the title of the column, again, so vague, it can be a little confusing.... 
Is there no development environment around? Kinda asking for trouble there...