MySQL基于组的自增 [英] MySQL auto-increment based on group

问题描述

问题与 mysql 的自动增量有关.我想要实现的是根据客户编号增加 ID 值.所以基本上我将没有任何顺序的数据集插入到表中.每次插入新客户时，我希望 id 列增加，但当然要保留与客户相关的每一行，见下表.有什么方法可以通过sql实现吗?我尝试了多个主键的运气，也研究了分区，但自己无法弄清楚.

The problem is related to autoincrement with mysql. What I'm trying to achieve is to increment an ID value based on the customer number. So basically i insert data sets without any order into a table. Each time a new customer is inserted, i would like the id column to be incremented, but of course kept for every row related to the customer, see the table below. Is there any way to achieve that via sql? I tried my luck with multiple primary keys and also looked into partioning, but was not able to figure it out by myself.

推荐答案

你可以使用这样的查询:

you can use a query like this:

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>

这篇关于MySQL基于组的自增的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！