I have a table devicedetails with ID d_deviceId as auto increment. If I delete the first ID(i.e., 0) , I need to insert the next entry to the ID value 0.
我有一个表设备详细信息ID ID d_deviceId作为自动增量。如果我删除第一个ID（即0），我需要将下一个条目插入ID值0。
How can I implement this in MySQL.
我怎样才能在MySQL中实现它。
If I delete any other ID (say 5), I use the following query:
如果我删除任何其他ID（比如5），我使用以下查询：
SELECT d_deviceId + 1 FROM devicedetails d1 WHERE NOT EXISTS
(SELECT 1 FROM devicedetails d2 WHERE d2.d_deviceId = d1.d_deviceId + 1) limit 0,1
From this I get the next Reusable ID except the initial ID.
从这里我得到了除初始ID之外的下一个可重用ID。
Any one suggest a way?
有谁建议的方式？
1 个解决方案
#1
3
Stop using that query; it will give you incorrect results under high concurrency. Use the AUTO_INCREMENT property on your d_deviceId column instead -- it will automatically assign the next unused value to the column every time a row is inserted.
停止使用该查询;它会在高并发性下给你不正确的结果。使用d_deviceId列上的AUTO_INCREMENT属性 - 每次插入行时，它都会自动将下一个未使用的值分配给列。
This will require that d_deviceId be configured as the primary key for the table. But that should probably be the case already.
这将要求将d_deviceId配置为表的主键。但那可能就是这种情况。
http://dev.mysql.com/doc/refman/5.5/en/example-auto-increment.html
http://dev.mysql.com/doc/refman/5.5/en/example-auto-increment.html
#1
3
Stop using that query; it will give you incorrect results under high concurrency. Use the AUTO_INCREMENT property on your d_deviceId column instead -- it will automatically assign the next unused value to the column every time a row is inserted.
停止使用该查询;它会在高并发性下给你不正确的结果。使用d_deviceId列上的AUTO_INCREMENT属性 - 每次插入行时，它都会自动将下一个未使用的值分配给列。
This will require that d_deviceId be configured as the primary key for the table. But that should probably be the case already.
这将要求将d_deviceId配置为表的主键。但那可能就是这种情况。
http://dev.mysql.com/doc/refman/5.5/en/example-auto-increment.html
http://dev.mysql.com/doc/refman/5.5/en/example-auto-increment.html