数据库设计以编程方式处理递增 ID 字段、可扩展性 - 困惑
如果我正在构建一个多商店电子商务解决方案，并希望订单表维护基于商店的顺序 ID，那么最好的方法是什么？
例如，想象一下这些订单 ID 按顺序排列： -
UK0001
UK0002
UK0003
DE0001
UK0004
DE0002
等等
通过分组 PK ID MySQL / MyISAM
- 如果国家/地区字段和 自动递增的 ID 字段是 用过的。但MyISAM有一些固有的 诸如表锁定等问题 这个功能看起来像是它的功能 只能在 MyISAM 中使用，所以 移动数据库引擎不会 可以使用此解决方案。
以编程方式。假设我们有 两个字段：order_id（全局自动 Inc PK 列由 DB 管理）， 订单号（特定于国家/地区 维护顺序 ID 字段 通过代码），表中还有 用于关联订单的 shop_id 列 到商店。
因此，在创建新订单记录并且数据库引擎为新记录分配一个 ID 后，新创建的订单 ID 已在代码中作为变量 $newID 检索
select order_number+1 as new_order_number from orders where order_id < $newID and shop_id = UK order by order_id desc limit 1
（这是伪代码/sql 顺便说一句）
问题：
- < p>这是一个可行的解决方案吗？或者是否有更好更有效的方法来做到这一点？
当表有100万+条记录时，每个订单提交的额外查询开销会不会出现问题？
当表有如果向同一国家/地区下了两个订单并且同时处理它们，则似乎可能会出现 order_number 冲突。如果有这种可能吗？如果是这样，有办法预防吗？ （也许是唯一索引和交易？）
期待您的帮助！
谢谢
If I am building a multi-shop e-commerce solution and want the orders table to maintain a shop based sequential ID, what is the best way of doing this?
For instance imagine these order IDs in sequence: -
UK0001
UK0002
UK0003
DE0001
UK0004
DE0002
etc.
through grouped PK ID MySQL / MyISAM
- MySQL will manage this automatically if a country field and
an auto incrementing ID field are
used. But MyISAM has some inherent
problems such as table locking and
this feature seems like it's feature
that is only available in MyISAM so
moving database engine would not be
possible with this solution.
- MySQL will manage this automatically if a country field and
Programmatically. Let's say we have
two fields: order_id (global auto
inc PK column managed by DB),
order_number (country specific
sequential ID field maintained
through code) and the table also has
a shop_id column to associate orders
to shops.
So - after the new order record has been created and the DB engine has assigned an ID to the new record, and the newly created order ID has been retrieved in code as variable $newID
select order_number+1 as new_order_number from orders where order_id < $newID and shop_id = UK order by order_id desc limit 1
(this is pseudo code / sql btw)
Questions:
is this a feasible solution? Or is there a better more efficient way to do this?
When the table has 1 million + records in it, will the additional query overhead per order submission cause problems, or not?
It seems there'd be a chance of order_number clashes if two orders are placed for the same country and they get processed simultaneously. If this a possibility? if so, is there a way of protecting against it? (perhaps a unique index and a transaction?)
Look forward to your help!
Thanks
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
是的，您绝对走在正确的道路上。将“订单 ID”字段设置为“唯一”并执行与您尝试执行的操作完全相同的操作。添加一个 catch 语句，如果由于 UNIQUE 错误而未添加，则尝试使用相同的语句再次插入它，以确保 ORDER ID 永远不会同时使用相同的 ID 插入。
Yes you are definitely on the right track. Set the ORDER ID field as UNIQUE and do the exact same thing you were trying to do. Add a catch statement where if it is not added because of the UNIQUE error, then try to insert it again with the same statement to ensure that the ORDER ID is never inserted with the same ID at the same time.