使用长 varchar 键作为查找键的方法
我正在将评论机制构建到应用程序中，该机制允许程序员/插件作者以简单的方式实现评论线程。
我想要做到这一点的方法是为注释线程使用唯一标识符，这通过使用线程的唯一键来减轻开发人员的辛苦工作，您可以在应用程序的任何位置放置这样的代码。
假设程序员想要向他称为“我的图像”的图像上传插件添加评论。然后，在代码中，他可以这样调用：
insertCommentThread('myimages:340');
另一个开发人员可能有更复杂的事情，他想向 wiki 条目添加注释：
insertCommentThread('wiki-entry-page-name-it-could-be-long');
因此开发人员可以将线程称为他们喜欢的任何名称。
我有点担心如果密钥的长度会变长，事情的速度会变长，所以我想以其他格式存储密钥。
所以我的问题是...
是否有一种方法可以以某种编码方式存储字符串键，以便它变得更小且查找速度更快？
（我可以对字符串进行哈希处理，但数据库的可读性迷路了...）
顺便说一句。我正在使用 MySQL
I'm building a commenting mechanism into an application that allows a programmer/plugin author to implement comment threads in a simple manner.
The way I wanted to do this is by using a unique identifier for the comment threads, which took the hard work away from the developer by using a unique key for the thread, where you can place code like this anywhere in the application.
let's say the programmer wanted to add comments to an image upload plugin he calls "my images". In the code he can then call something like:
insertCommentThread('myimages:340');
another developer might have a more complicated thing and he wants to add comments to a wiki entry:
insertCommentThread('wiki-entry-page-name-it-could-be-long');
So the developer can call the threads any name they like.
I'm a bit worried about the speed of things if the length of the keys will become long, so I'd like to store the keys in some other format.
So my question is...
Is there a way to store a string key in some encoded way so that it becomes smaller and faster to lookup?
(I could hash the strings, but then readability of the database gets lost...)
btw. I'm using MySQL
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
我建议使用 2 个参数构建函数。一种用于评论类型，一种用于唯一评论本身：
我将像这样设计数据库：
这样您可以将查询限制为当前加载的模块（评论类型），并且也可以在不同的模块中具有相同的评论组。
I would suggest to build the functions with 2 parameters. One for the the comment type and one for the unique comment itselfe:
The DB I would design then like this:
This way you can limit your query to the module (commenttype) currently loaded and it is also possible to have the same comment within different groups.
我认为您希望该列成为唯一的索引字段..而不是主键。
I think you would want this column to be a unique, indexed field.. not the primary key.
为什么需要将搜索字符串设为主键？
我将使用数字主键来提高速度，并为长字符串使用单独的唯一查找字段。
无论如何，您很可能必须在插入记录之前进行重复检查，并在检查失败时寻找替代记录。我不确定 mySQL 的
UNIQUE约束在这里对您有多大帮助。Why do you need to make your search string a primary key?
I would use a numeric primary key for speed, and a separate unique lookup field for the long string.
You will most likely have to do a duplicate check before you insert the record anyway, and find a substitute if the check fails. I'm not sure how much mySQL's
UNIQUEconstraint will help you here.您可以在列上创建散列索引，而不会导致该列不可读；散列的是索引，而不是数据。如果您不想在范围内搜索，这似乎是可行的方法。
You can create a hashed index on a column without leaving the column unreadable; it is the index that is hashed, not the data. That would seem to be the way to go if you don't want to search on ranges.