| View previous topic :: View next topic |
| Author |
Message |
Xildjian
Joined: 08 Jan 2005
Posts: 100
|
 Posted: Mon Oct 20, 2008 15:10 Post subject: Table setup question... |
 |
|
I couldn't find this in the helps docs so does anyone if this is possible with MySQL.
I have a table of character information. One column is a characterID. Nothing more than an integer field with auto-increment set.
The question is can this be also set to pick next available for the auto-increment. For example:
charA has id of 1
charB has id of 2
charC has id of 3
If a player decides to delete CharB and create CharD it would be nice if the auto-increment picked ID 2 (next available) instead of ID 4.
Thanks.
_________________
Member Shadow of Iniquity development team |
|
| Back to top |
|
 |
TroveLord
Joined: 22 Nov 2006
Posts: 136
Location: Italy
|
| Posted: Mon Oct 20, 2008 15:28 Post subject: |
|
|
| I don't think so, it's against the logic of auto-increment. |
|
| Back to top |
|
|
Great Archmage
Joined: 19 Oct 2008
Posts: 7
|
| Posted: Mon Oct 20, 2008 16:28 Post subject: |
|
|
TroveLord is right. Auto-Increment only assigns numbers in sequential order.
If your storing character information though it would be best to use a composite key based on Player Login, Character Name, and possibly a third field to store a string value incase you want to be able to select a portion of information about the character. |
|
| Back to top |
|
|
Gryphyn
Joined: 20 Jan 2005
Posts: 431
|
| Posted: Mon Oct 20, 2008 23:26 Post subject: |
|
|
| If you know of a gap in the sequence you can always assign the ID value, rather than defaulting to the autoincrement value. |
|
| Back to top |
|
|
Xildjian
Joined: 08 Jan 2005
Posts: 100
|
| Posted: Tue Oct 21, 2008 5:03 Post subject: |
|
|
Thanks for the replies!
Yea, I have other data in the table to key off of also. I just thought it would be nice to fill in the missing numbers when characters are deleted and their database entries removed.
_________________
Member Shadow of Iniquity development team |
|
| Back to top |
|
|
Xildjian
Joined: 08 Jan 2005
Posts: 100
|
| Posted: Sun Nov 02, 2008 17:53 Post subject: |
|
|
I realized what's going on in my mod.
The startup script checks the database tables.
I have a count table, the mod uses to place the number of row entries for each table in the mod +1 ie next ID to use for those tables which use an ID.
So my question is would it be possible for the database to return the next available ie the gaps in the sequence instead of having to do it in a script which could potential cause a to many execution error (loop greater than 5000)?
I've been looking for away this might be done, but not seeing anything obvious.
Thanks!
_________________
Member Shadow of Iniquity development team |
|
| Back to top |
|
|
Fireboar
Joined: 17 Feb 2008
Posts: 323
|
| Posted: Sun Nov 02, 2008 21:48 Post subject: |
|
|
Why not just...
SELECT COUNT(1) FROM table [JOIN table [ON condition]] [WHERE condition]
[Bracketed] parts optional. That little query simply returns the number of rows that would be returned if you had replaced COUNT(1) with the names or IDs of fields.
Example:
| Code: | int nCount = 0;
SQLExecDirect("SELECT * FROM table");
while (SQLFetch())
{
nCount++;
} |
Equivalent to the much faster and more efficient alternative:
| Code: | SQLExecDirect("SELECT COUNT(1) FROM table");
SQLFetch();
int nCount = SQLGetData(1); |
With this in mind, do you really need that count table? Why not just use auto increment and COUNT?
FINAL NOTE: It's also possible to use SELECT COUNT(field) or SELECT COUNT(*), but these are both less efficient because it does a presence check: any fields that are NULL aren't counted even if the row itself exists. COUNT(1) will simply count the number of rows. COUNT(*) is just dumb and overused far too much. |
|
| Back to top |
|
|
Gryphyn
Joined: 20 Jan 2005
Posts: 431
|
| Posted: Sun Nov 02, 2008 23:45 Post subject: |
|
|
You're better off using MAX(column) rather than COUNT(...)
If rows get deleted, the COUNT(...) option can repeat a 'hi-value', and cause unique key conflicts.
But you're looking for gaps...
| Code: | select t1.id - 1 as 'missing'
from <table> t1 left outer join <table> t2
on t1.id = t2.id - 1
where t1.id > 1 and t2.id is null
limit 1 |
nb: t1 & t2 are the same table
not very efficient - but it will find the holes |
|
| Back to top |
|
|
Xildjian
Joined: 08 Jan 2005
Posts: 100
|
| Posted: Mon Nov 03, 2008 0:32 Post subject: |
|
|
| Gryphyn wrote: | You're better off using MAX(column) rather than COUNT(...)
If rows get deleted, the COUNT(...) option can repeat a 'hi-value', and cause unique key conflicts.
But you're looking for gaps...
| Code: | select t1.id - 1 as 'missing'
from <table> t1 left outer join <table> t2
on t1.id = t2.id - 1
where t1.id > 1 and t2.id is null
limit 1 |
nb: t1 & t2 are the same table
not very efficient - but it will find the holes |
Cool thanks, I'll have to play with this, and be able to test if I have no gaps because then I really want MAX(column) + 1.
Thanks for the replies!
_________________
Member Shadow of Iniquity development team |
|
| Back to top |
|
|
Gryphyn
Joined: 20 Jan 2005
Posts: 431
|
| Posted: Mon Nov 03, 2008 1:30 Post subject: |
|
|
last_insert_id() + 1 is another alternative.
But the only one to avoid duplicate keys is max(...) + 1 |
|
| Back to top |
|
|
Xildjian
Joined: 08 Jan 2005
Posts: 100
|
| Posted: Mon Nov 03, 2008 1:49 Post subject: |
|
|
| Code: | select t1.CharacterID + 1 as 'missing'
from table_character t1 left outer join table_character t2
on t1.CharacterID = t2.CharacterID -1
where t1.CharacterID > 1 and t2.CharacterID is null |
This appears to be working for me. I took out the limit one to list out all the gaps. So far of the 10 or so values I have checked the list is correct. And it looks like it will always return a value, if there are no gaps the value would be the next value I should use. ie MAX(column) + 1 
Thanks a bunch, this helps!
_________________
Member Shadow of Iniquity development team |
|
| Back to top |
|
|
Gryphyn
Joined: 20 Jan 2005
Posts: 431
|
| Posted: Mon Nov 03, 2008 6:08 Post subject: |
|
|
| Xildjian wrote: | | Code: | select t1.CharacterID + 1 as 'missing'
from table_character t1 left outer join table_character t2
on t1.CharacterID = t2.CharacterID -1
where t1.CharacterID > 1 and t2.CharacterID is null |
This appears to be working for me. I took out the limit one to list out all the gaps. So far of the 10 or so values I have checked the list is correct. And it looks like it will always return a value, if there are no gaps the value would be the next value I should use. ie MAX(column) + 1
Thanks a bunch, this helps! |
Careful there, t1.CharacterID + 1 as 'missing' will give you the wrong result, its t1.CharacterID - 1 as 'missing' to select the 'hole' (the same as the on criteria.)
The sequence 1,3,4 would give you a dup key.
When t1.id = 3, the hole = 2 (3-1), and you return 4 (3+1) - which already exists.
it's the t2 value that is missing, that's the one you want to report.
You can use +1's in both to effectively get the 'hole' or 'next' value. The catch with this is that it won't find rows/ids before MIN(column) |
|
| Back to top |
|
|
Xildjian
Joined: 08 Jan 2005
Posts: 100
|
| Posted: Mon Nov 03, 2008 7:45 Post subject: |
|
|
Maybe it's a MySQL version thing but when I run your original query with sequence: 1,2,4,5,7,8,9,10...
the missing sequence returned is: 1,4,13...
When I change the missing to t1.CharacterID + 1 the sequence returned is:
3,6,15...
Which is what I would expect. Tho I don't completely understand how your query is working.
I'm running this against my database with MySQL Query Browser.
MySQL version 5.0.45 community edition
_________________
Member Shadow of Iniquity development team |
|
| Back to top |
|
|
Gryphyn
Joined: 20 Jan 2005
Posts: 431
|
| Posted: Mon Nov 03, 2008 8:28 Post subject: |
|
|
t1.id (where t1 is also t2)
1 -- excluded by where t1.id > 1
2, does (2-1) 1 exist in table t2? yes
4, does (4-1) 3 exist in table t2? no
5, does (5-1) 4 exist in table t2? yes
7, does (7-1) 6 exist in table t2? no
8, does (8-1) 7 exist in table t2? yes
9, does (9-1) 8 exist in table t2? yes
10, does (10-1) 9 exist in table t2? yes
14, does (14-1) 13 exist in table t2? no
so where the t2 answer is no, a null is provided (you want these rows)
that's (4-1) 3 , (7-1) 6 and (14-1) 13 which are the 'holes' in the sequence.
**you'll also note that 11 and 12 are not detected, these gaps will eventually be filled with further inserts and queries.
insert 13 & run query again
13, does (13-1) 12 exist in table t2? no
14, does (14-1) 13 exist in table t2? yes
This time the result would be (4-1) 3 , (7-1) 6 and (13-1) 12
The original query again...
| Code: | select t1.id - 1 as 'missing'
from <table> t1 left outer join <table> t2
on t1.id = t2.id - 1
where t1.id > 1 and t2.id is null
limit 1 |
|
|
| Back to top |
|
|
Fireboar
Joined: 17 Feb 2008
Posts: 323
|
| Posted: Mon Nov 03, 2008 18:36 Post subject: |
|
|
| Gryphyn wrote: | You're better off using MAX(column) rather than COUNT(...)
If rows get deleted, the COUNT(...) option can repeat a 'hi-value', and cause unique key conflicts.
But you're looking for gaps...
| Code: | select t1.id - 1 as 'missing'
from <table> t1 left outer join <table> t2
on t1.id = t2.id - 1
where t1.id > 1 and t2.id is null
limit 1 |
nb: t1 & t2 are the same table
not very efficient - but it will find the holes |
Ah, careful. That all depends on what you want to do.
If you want to know the number of rows, then COUNT(...) is the one to use. If you want to know the next insert ID to use, then MAX(column) is indeed the one to use. From "I have a count table", I had assumed the former. |
|
| Back to top |
|
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
