如何为每个客户 ID 提供单独的订单 ID？

Question

想象一个数据库设置，其中 x 个客户可以下 y 个订单。 通常所有客户都会共享一组自动递增的订单 ID。

• 客户 A 的第一个订单的订单 ID 为 #1
• 客户 B 的第一个订单的订单 ID 为 #2。

我希望为每个客户提供单独的订单 ID 集。重要的是，客户无法根据 ID 查看有多少其他客户下了订单。

• 客户 A 的第一个订单的订单 id #1
• 客户 A 的第二个订单的订单 id #2
• 客户 B 的第一个订单的订单 id #1

现在，我能看到的唯一方法是手动选择客户的最大值订单号，加+1，然后手动插入。

select max(customer_order_id) from orders where customer_id = X

我怎样才能在数据完整性和规范化的意义上创建这种方法？比如为客户的各个订单 ID 提供某种自动增量？

create table customers
(
  id int auto_increment primary key, 
  name varchar(255)
);

create table orders
(
  id int auto_increment primary key, 
  customer_id int
  customer_order_id int 
  foo varchar
  bar varchar
);

Answer 1

只需将订单号设为客户编号，后跟序列号即可。因此，如果我是客户 103985，我的第一个订单是 103985-0001，我的第二个订单是 103985-0002。标准化数据——在订单中，分别存储客户编号和序列号。将它们组合起来进行显示。

Answer 2

听起来您想要一个基本的关系结构或只是一个可供选择的 slug 字段。

因此，您的订单表可以有一个对客户透明的自动增量 ID。根据您计划如何提供数据，您可以轻松使用 slug 系统来组合用户数据来查找正确的记录。

例如，基于 url 的请求 - somesite.com/orders/{user_id}/1 将选择其中用户 id 为登录用户，订单 slug 为 1。 {user_id} 可以存在或省略，具体取决于您的方式选择授权查看该页面。因此，管理员可以通过获取变量进行查看，非管理员必须针对此变量进行验证...但这可能不是重点。

或者，您可以使用 varchar 来处理订单 id 并组成一个完全无法破译的字符串。根据您网站的规模，这可能不是问题。

您唯一关心的是用户 B 的订单 id 为 100，您不想让他们知道之前还有 99 个其他订单吗？如果是这样，这里的任何答案都适合您。

Answer 3

与 MyISAM 相比，使用 InnoDB 有几个优点，但如果您使用 MyISAM 引擎，则可以拥有自动生成的复合主键。

来自 dev.mysql.com：示例自动增量。 html：

对于 MyISAM 和 BDB 表，您可以在多个表中的辅助列上指定AUTO_INCRMENT -列索引。在本例中，AUTO_INCRMENT 列的生成值计算公式为 MAX(auto_increment_column) + 1 WHERE prefix=given-prefix。当您想要将数据放入有序组时，这非常有用。

在你的情况下，它会是这样的：

CREATE TABLE customers
(
  id int auto_increment PRIMARY KEY, 
  name varchar(255)
);

CREATE TABLE orders
(
  customer_id int,
  customer_order_id int auto_increment,  
  ...
  PRIMARY KEY (customer_id, customer_order_id)
);

如果你想要与 InnoDB 相同的功能，你将不得不使用触发器。