保留自动增量ID

Question

我正在开发一个电子商务网络应用程序，下面是我的想法。

为了简化这个问题，user表包含两列：id (int、PK、NN、AI)、name(varchar(45)、NN)。我希望员工的I更短，所以我希望保留短I，并在以后分配它们。id分为三类。

1. 为员工保留1-1000份。
2. 1001-5000保存给合伙人。
3. 面向用户的5001+。

那么有什么方法可以操作自动增量id吗？我目前的演练是: fires创建垃圾记录以填充ID，然后将其分配给雇主和合作伙伴。

Answer 1

要更改AUTO_INCREMENT值，您必须执行下一个查询：

mysql> ALTER TABLE user AUTO_INCREMENT=next_id;

当您在下一个insert语句中省略id时，您必须用将要设置的值替换next_id。

然而，属性AUTO_INCREMENT值的工作方式并不是您想要的那样，至少不是单独的，您必须每次搜索--这是您要查找的下一个值--在3种情况下执行一个sql查询。

例如，对于要查找员工下一个id的情况，您必须执行下一个查询：

mysql> SELECT MAX(id) AS max_given_id FROM user WHERE id >= 1 AND id < 1000;

然后对该值执行一个加号1，以获得使用AUTO_INCREMENT属性在insert中设置的id，或者使用第一个给定的语句来更改表AUTO_INCREMENT值，然后在插入中省略id。

这样做的一种方法是执行我刚才用存储过程解释的过程：

mysql> DELIMITER //
mysql>
mysql> DROP PROCEDURE IF EXISTS InsertUserByType;
    -> /*first we try to erase the procedure if it is set, so we dont redeclare*/
    ->
    -> CREATE PROCEDURE InsertUserByType(IN pname VARCHAR(45), IN type VARCHAR(45))
    -> BEGIN
    ->
    ->     /*this is the declaration of the variable where we are storing the next value to set in the insert*/
    ->     DECLARE next_id INT DEFAULT 0;
    ->
    ->     /*we do the first case where we are inserting an employee*/
    ->     IF (type = 'employe') THEN
    ->         /*we look for the max value in the range for the given case*/
    ->         SELECT MAX(id) INTO next_id FROM user WHERE id >= 1 AND id < 1000;
    ->         IF (next_id IS NULL) THEN
    ->             /*if the max value doesnt exists we set it to the first value for this case*/
    ->             SET next_id = 1;
    ->         ELSE
    ->             SET next_id = next_id + 1;
    ->         END IF;
    ->     ELSE
    ->         /*we do the second case where we are inserting a partner*/
    ->         IF (type = 'partner') THEN
    ->             /*we look for the max value in the range for the given case*/
    ->             SELECT MAX(id) INTO next_id FROM user WHERE id >= 1001 AND id < 5000;
    ->             IF (next_id IS NULL) THEN
    ->                 /*if the max value doesnt exists we set it to the first value for this case*/
    ->                 SET next_id = 1001;
    ->             ELSE
    ->                 SET next_id = next_id + 1;
    ->             END IF;
    ->         ELSE
    ->             /*we do the third case where we are inserting an user*/
    ->             IF (type = 'user') THEN
    ->                 /*we look for the max value in the range for the given case*/
    ->                 SELECT MAX(id) INTO next_id FROM user WHERE id >= 5001;
    ->                 IF (next_id IS NULL) THEN
    ->                     /*if the max value doesnt exists we set it to the first value for this case*/
    ->                     SET next_id = 5001;
    ->                 ELSE
    ->                     SET next_id = next_id + 1;
    ->                 END IF;
    ->             END IF;
    ->         END IF;
    ->     END IF;
    ->
    ->     /*we make our insert with the recolected value and the name of the user given as a param*/
    ->     INSERT INTO user (id, name) VALUES (next_id, pname);
    ->
    -> END//
Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER ;
mysql> 

每次您想要插入一个用户时，然后编写下一个语句：

mysql> CALL InsertUserByType("User Name", "partner");
Query OK, 1 row affected (0.01 sec)

Answer 2

您可以为表提前如下所示的“最高”值：

ALTER TABLE user AUTO_INCREMENT=5000;

这样做之后，为下一次插入生成的id值将为5001或更高。自动增量永远不会生成低于表当前最大值的值。

您可以重写自动增量并插入低于最大值的任何值.只需在INSERT语句中指定值：

INSERT INTO user (id, name, ...) VALUES (478, 'Sam', ...);

当您这样做时，您有责任确保该值目前不被使用，并且您有责任确保两个并发会话不会意外地尝试使用相同的id值。

自动增量只跟踪表中的单个最高值，并且总是生成更多的值。它保证是唯一的，如果您有并发会话执行插入，那么它就能很好地工作。但是它不能保证给出连续的数字(也就是说，它可能生成一个等于最大值+2或更多的值)。而且，它不会像您所描述的那样在多个范围内生成值。

Answer 3

如果您想要唯一的数字，而不关心实际的数值，那么使用一个自动递增的字段。

如果您确实关心数值本身，那么使用一个常规字段并自己做繁重的工作。

话虽如此，我认为你是在把自己逼到一个角落里。

对于每个人来说，一个自动递增的用户Id字段加上一个用户类型字段有什么问题？

select * 
from users 
order by type, id; 

+------+------+--------------------+
| id   | type | name               | 
+------+------+--------------------+
|  123 | E    | Fred               |
| 2345 | E    | Barney             | 
| 7890 | E    | "Mudsy" Muddlemore | 
| 3456 | P    | Snagletooth        | 
| 4567 | U    | Brenda             | 
| 5678 | U    | Dee Dee            | 
| 6789 | U    | Taffy              | 
+------+------+--------------------+

请记住，您不太可能从来没有删除用户记录。

随着时间的推移，他们会积累起来，所以，在上面的例子中，"Barney“有id > 1000，但它仍然是一名员工。