mysql - MySQL基于组的自动增量

问题与mysql自动增量有关。我想要实现的是根据客户编号增加ID值。所以基本上我将没有任何顺序的数据集插入表中。每次插入新客户时，我都希望id列增加，但是对于与客户相关的每一行，当然要保留，请参见下表。有什么办法可以通过sql来实现吗？我用多个主键尝试了运气，还研究了分区，但是我自己却无法解决。

最佳答案

您可以使用如下查询：

INSERT INTO autoinc (cid,info,customer)
SELECT
    COALESCE(max(cid),0) +1
    , 'A Customer 1'
    , 12345
FROM autoinc
WHERE customer = 12345;

样品

mysql> SELECT * from autoinc;
Empty set (0,00 sec)

mysql> INSERT INTO autoinc (cid,info,customer)
    -> SELECT
    ->     COALESCE(max(cid),0) +1
    ->     , 'A Customer 1'
    ->     , 12345
    -> FROM autoinc
    -> WHERE customer = 12345;
Query OK, 1 row affected (0,00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT * from autoinc;
+----+------+--------------+----------+
| id | cid  | info         | customer |
+----+------+--------------+----------+
|  1 |    1 | A Customer 1 |    12345 |
+----+------+--------------+----------+
1 row in set (0,00 sec)

mysql> INSERT INTO autoinc (cid,info,customer)
    -> SELECT
    ->     COALESCE(max(cid),0) +1
    ->     , 'A Customer 1'
    ->     , 12345
    -> FROM autoinc
    -> WHERE customer = 12345;
Query OK, 1 row affected (0,00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT * from autoinc;
+----+------+--------------+----------+
| id | cid  | info         | customer |
+----+------+--------------+----------+
|  1 |    1 | A Customer 1 |    12345 |
|  2 |    2 | A Customer 1 |    12345 |
+----+------+--------------+----------+
2 rows in set (0,00 sec)

mysql> INSERT INTO autoinc (cid,info,customer)
    -> SELECT
    ->     COALESCE(max(cid),0) +1
    ->     , 'B Customer 2'
    ->     , 9876
    -> FROM autoinc
    -> WHERE customer = 9876;
Query OK, 1 row affected (0,00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT * from autoinc;
+----+------+--------------+----------+
| id | cid  | info         | customer |
+----+------+--------------+----------+
|  1 |    1 | A Customer 1 |    12345 |
|  2 |    2 | A Customer 1 |    12345 |
|  3 |    1 | B Customer 2 |     9876 |
+----+------+--------------+----------+
3 rows in set (0,00 sec)

mysql> INSERT INTO autoinc (cid,info,customer)
    -> SELECT
    ->     COALESCE(max(cid),0) +1
    ->     , 'A Customer 1'
    ->     , 12345
    -> FROM autoinc
    -> WHERE customer = 12345;
Query OK, 1 row affected (0,00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT * from autoinc;
+----+------+--------------+----------+
| id | cid  | info         | customer |
+----+------+--------------+----------+
|  1 |    1 | A Customer 1 |    12345 |
|  2 |    2 | A Customer 1 |    12345 |
|  3 |    1 | B Customer 2 |     9876 |
|  4 |    3 | A Customer 1 |    12345 |
+----+------+--------------+----------+
4 rows in set (0,00 sec)

mysql>