MySQL - 为每条记录提供唯一键，而不是主键
好吧，这是一个很难解释的问题。
我正在创建一个包含 PAGES 的应用程序，目前我使用 PageID 作为 SEL 记录的键。
我现在遇到的问题是，我希望用户能够编辑页面，但不会丢失上一页（出于历史记录、记录保留原因，例如更改日志或 wiki 页面历史记录）。
这让我觉得我需要在 PAGE 表中添加一个新字段来充当 pageID，但不是每次添加行时都会自动递增的主键。
Google Docs 有一个 DOCID：/Doc?docid=0Af_mFtumB56WZGM4d3Y3d2JfMTNjcDlkemRjeg
这样我就可以拥有具有相同 Doc ID 的多个记录，并根据 dataAdded 字段显示历史更改日志。当用户想要查看该 DOCID 时，我只需提取最新的 DOCID 即可。
想法？我很欣赏你的聪明才智，为我指明了正确的方向！
Ok this is a tricky one to explain.
I am creating an app that will have PAGES, currently I'm using PageID as the key to SEL the record.
The issue I'm having now is that I want users to be able to EDIT pages, but not lose the previous page (for history, recording keeping reasons, like a changelog or wiki page history).
This is making me think I need a new field in the PAGE table that acts as the pageID, but isn't the Primary Key that is auto-incremented every time a row is added.
Google Docs has a DOCID: /Doc?docid=0Af_mFtumB56WZGM4d3Y3d2JfMTNjcDlkemRjeg
That way I can have multiple records with the same Doc ID, and show a history change log based on the dataAdded field. And when a user wants to view that DOCID, I simply pull the most recent one.
Thoughts? I appreciate your smart thinking to point me in the right direction!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
你走在正确的轨道上。您需要的是历史记录或修订 ID 以及文档 ID。历史记录 id 将是主键，但出于查询目的，您还可以在文档 id 上有一个键。
通过历史记录跟踪，您可以为应用程序增加一点复杂性。您必须小心，文档的主视图显示当前历史修订（即给定文档 ID 的最大历史记录 ID）。
同样，如果您要存储大型文档，则每次编辑本质上都会将文档的另一个副本添加到数据库中，并且表将很快变得非常大。您可能需要考虑实现某种“差异”存储，仅存储对文档的更改而不是完整的内容，或者将历史编辑保存在单独的表中以仅用于历史搜索。
You're on the right track. What you need is a history or revision id, and a document id. The history id would be the primary key, but you would also have a key on the document id for query purposes.
With history tracking, you add a bit more complexity to your application. You have to be careful that the main view of the document is showing the current history revision (ie. largest history id for a given document id).
As well, if you are storing large documents, every edit is essentially going to add another copy of the document to your database, and the table will quickly grow very large. You might want to consider implementing some kind of "diff" storage, where you store only the changes to the document and not the full thing, or keeping history edits in a separate table for history-searching only.
UUID() 创建一个随机生成的 128 位数字，例如
'6ccd780c-巴巴-1026-9564-0040f4311e29'
这个数字在几百万年内都不会重复。
//注意大多数数字基于时间戳和机器信息，因此许多数字在重复调用时会相似，但它始终是唯一的。
UUID() creates a randomly generated 128bit number, like
'6ccd780c-baba-1026-9564-0040f4311e29'
This number will not be repeated in a few millions years.
//note most digits are based upon timestamp and machine information, so many of the digits will be similar upon repeated calls, but it will always be unique.
保留一份包含更改历史记录的审核表。例如，如果您需要回滚更改或查看更改历史记录，这将允许您返回。
Keep an audit table with the history of the changes. This will allow you to go back if you need to roll back the changes or view change history for example.
您可以这样建模：
You might model it like this:
每个文档实际上都是一个修订版：
doc - (doc_id)
revision - (rev_id, doc_id, version_num, name, description, content,author_id, active tinyint default 1)
然后您可以仅使用 rev_id 打开任何内容： /view?id= 21981
从修订版 r、文档 d 中选择 *，其中 r.rev_id =？并且 r.doc_id = d.doc_id
each document is really a revision:
doc - (doc_id)
revision - (rev_id, doc_id, version_num, name, description, content, author_id, active tinyint default 1)
then you can open any content with just the rev_id: /view?id=21981
select * from revision r, doc d where r.rev_id = ? and r.doc_id = d.doc_id
对我来说，这听起来是两张桌子的好工作。您可能有一张
page_header表和一张page_content表。标题表将保存静态信息，如标题、分类（无论什么），内容表将保存实际的可编辑内容。每次用户更新页面时，都会插入一条新的page_content记录，而不是更新现有记录。当您显示页面时，请确保获取最新的page_content记录。这是保留历史记录并在需要时回滚的简单方法。祝你好运！
This sounds like a good job for two tables to me. You might have one
page_headertable and onepage_contenttable. The header table would hold static info like title, categorization (whatever) and the content table would hold the actual editable content. Each time the user updates the page, insert a newpage_contentrecord versus updating an existing one. When you display the page just make sure you grab the latestpage_contentrecord. This is a simple way to keep a history and roll back if needed.Good luck!