MySQL 中的唯一 ID？
因此，我正在为我工作的公司编写一个相当小的脚本，以帮助我们更好地管理服务器。不过，我不经常使用 MySQL，所以我对什么是最好的路径有点困惑。
我正在做这样的事情......
$sql = "CREATE TABLE IF NOT EXISTS Servers
(
MachineID int NOT NULL AUTO_INCREMENT,
PRIMARY KEY(MachineID),
FirstName varchar(32),
LastName varchar(32),
Datacenter TINYINT(1),
OperatingSystem TINYINT(1),
HostType TINYINT(1)
)";
$result = mysql_query($sql,$conn);
check ($result);
$sql = "CREATE TABLE IF NOT EXISTS Datacenter
(
DatacenterID int NOT NULL AUTO_INCREMENT,
PRIMARY KEY(DatacenterID),
Name varchar(32),
Location varchar(32)
)";
$result = mysql_query($sql,$conn);
check ($result);
基本上在服务器表中，我将在另一个表中存储条目的索引。我担心的是，如果我们删除这些条目之一，它会搞乱自动递增索引，并可能导致大量不正确的数据。
为了更好地解释，假设我添加的第一台服务器的 Datacenter 值为 3（即 DatacenterID），我们稍后删除 id 1 (DatacenterID) Datacenter。
有没有好的方法可以做到这一点？
So I am working on a fairly small script for the company I work at the help us manage our servers better. I don't use MySQL too often though so I am a bit confused on what would be the best path to take.
I am doing something like this...
$sql = "CREATE TABLE IF NOT EXISTS Servers
(
MachineID int NOT NULL AUTO_INCREMENT,
PRIMARY KEY(MachineID),
FirstName varchar(32),
LastName varchar(32),
Datacenter TINYINT(1),
OperatingSystem TINYINT(1),
HostType TINYINT(1)
)";
$result = mysql_query($sql,$conn);
check ($result);
$sql = "CREATE TABLE IF NOT EXISTS Datacenter
(
DatacenterID int NOT NULL AUTO_INCREMENT,
PRIMARY KEY(DatacenterID),
Name varchar(32),
Location varchar(32)
)";
$result = mysql_query($sql,$conn);
check ($result);
Basically inside the Servers table, I will be storing the index of an entry in another table. My concern is that if we remove one of those entries, it will screw up the auto incremented indexes and potentially cause a lot of incorrect data.
To explain better, lets say the first server I add, the Datacenter value is 3 (which is the DatacenterID), and we remove the id 1 (DatacenterID) Datacenter at a later time.
Is there a good way to do this?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
自动增量仅对向表中插入新行有影响。因此，您向数据库中插入了 3 条记录，它们的 id 分别为 1、2 和 3。稍后您删除了 id 1，但 id 2 和 3 处的记录未更改。这并没有说明任何可能尝试引用数据库 id 1 记录的服务器记录，尽管该记录现在已丢失。
Auto increment only has an effect on inserting new rows into a table. So you insert three records into database and they get assigned ids of 1, 2, and 3. Later you delete id 1, but the records at id 2 and 3 are unchanged. This says nothing of any of the server records that might be trying to reference the database id 1 record though that is now missing.
正如保罗所说，删除旧行并稍后添加另一个新行是安全的。自动增量索引不会受到删除的影响。
但我建议不要删除它们，只需添加一列“状态”并设置 0，这意味着它们不再使用，以在数据库中保留任何可能的记录。
as said by paul, it is safe to remove old row and add another new one later. The auto incremental index won't be affected by deletion.
But I would suggest instead of removing them, simply add a column 'status' and set 0, implying they are no longer in use, to keep any possible record in db.
Servers.Datacenter 也应该是 INT，因为您将在此字段中存储 DataCenterID。然后，当您从第二个表中删除一些数据中心时，不会出现任何混乱。
Servers.Datacenter should be an INT, too, as you would store the DataCenterID in this field. Then, nothing will be mixed up when you remove some Datacenter from the second table.
如果您按照您建议的方式删除，则不会发生任何事情。每当添加新记录时，自动增量只会按顺序添加下一个最大数字，因此不会影响任何以前的记录。
希望有帮助。
If you remove in the way you suggest nothing will happen. auto-increment will just add the next highest number in sequence whenever a new record is added and so will not affect any previous records.
Hope that helps.