MYSQL help needed - Printable Version

MYSQL help needed - Printable Version

+- SA-MP Forums Archive (https://sampforum.blast.hk)
+-- Forum: SA-MP Scripting and Plugins (https://sampforum.blast.hk/forumdisplay.php?fid=8)
+--- Forum: Scripting Help (https://sampforum.blast.hk/forumdisplay.php?fid=12)
+--- Thread: MYSQL help needed (/showthread.php?tid=657644)

MYSQL help needed - m4karow - 12.08.2018

Hey

How is it possible to insert the lowest value with AUTO_INCREMENT?
I mean i have about 100 record but I deleted record with ID 2. Whenever I insert something it'll insert to ID 101 but i want to insert to ID 2. How i could solve this?

Re: MYSQL help needed - Akeem - 12.08.2018

i don't think you can.

Re: MYSQL help needed - GospodinX - 12.08.2018

Hi
You can try this:

Код:

mysql_tquery(g_SQL,"SELECT u.Id + 1 AS FirstAvailableId FROM `yourtable` u LEFT JOIN `yourtable` u1 ON ul.Id = u.Id + 1 WHERE u1.Id IS NULL ORDER BY u.Id LIMIT 0, 1" , "sql_FirstNextID", "i",playerid);

Код:

forward sql_FirstNextID(playerid);
public sql_FirstNextID(playerid)
{
new i,query[128];
cache_get_value_name_int(0, "FirstAvailableId",i);
mysql_format(g_SQL, query,sizeof(query),"INSERT INTO `yourtable` (`id`) VALUES ('%i')",i);
mysql_tquery(g_SQL, query);
...
return 1;
}

Re: MYSQL help needed - DAKYSKYE - 12.08.2018

Southclaws explained everything very well.

Re: MYSQL help needed - m4karow - 12.08.2018

Quote:

Originally Posted by [HLF]Southclaw

The real question is why? What would you gain from filling removed IDs? You'll probably cause more problems for yourself in future, just let AUTOINCREMENT do what it does and stay out. You have 4 billion possible integer values and 9,223,372,036,854,775,807 if your database supports 64 bit values (obviously SA:MP would not like that, but I'm making a point).

If you are relying on the IDs being in sequence - don't.

The inventory system listing the player item's with their DB ID-s and whenever an item dropped into the floor and picked up this ID is increasing. So the ID-s could be easily above a million that would disturb the inventory dialog and the problem is the inventory system is working always with theese ID-s (for example select the given ID when responding to the inventory listitem)

The inventory dialog will look like this:

ID / Item name / Amount
2342523 Fuel 0,3 l
2342524 Money $5000
2342525 Something 0,3 l
2342526 Something 0,3 l

Re: MYSQL help needed - m4karow - 18.08.2018

bump