在 SQL 中存储表示而不是整个字符串？
我想用四个静态字符串之一来标记我的用户：坚不可摧、势不可挡、超级高效和超级神奇。
存储字符串的一个字符表示而不是整个字符串会更快、更有效吗？有点像键<->值映射。当我进行搜索时，数据库只需要匹配一个字母而不是整个字符串。
在数据库中，这些单词只能表示为 i,u,s 和 m。在用户个人资料上，如果我得到一个i，我会打印indesctuctible，如果我得到一个u，我
会 打印unstoppable等等。我实际上通过这样做加快了任何事情的速度，还是这只是不必要的工作？
如果我还有 10 个这样的表示怎么办？
I want to mark my users with one out of four static strings: indestructible, unstoppable, superefficient and megafantastic.
Would it be faster and more efficient to store a one char representation of the string rather than the whole string? Sort of like a key<->value mapping. The DB would only need to match one letter instead of a whole string when I do a search.
In the DB these words would only be represented as i,u,s and m. On the users profile I print indesctuctible if I get an i, unstoppable if I get a u etc.
Do I actually speed up anything by doing like this or is it just unnecessary work?
What if I have 10 more of these representations?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
如果您正在谈论数据库设计，则应该有一个规范化设计。
创建一个查找表 id/name，将 id 值存储在用户表中（使用类似tinyint的东西）。
这样就可以直接在数据库中查询字符串。
如果您缓存此表，您将不会访问数据库来获取这些值，并且您只会为每个用户存储少量数据（更不用说通过线路传输更少的数据） - 这些都将转化为更好的性能数据库。
If you are talking about database design, you should have a normalized design.
Create a lookup table id/name, store the id value in your user table (use something like a tinyint).
This way you can query the database directly for the string.
If you cache this table, you will not be hitting the database for these values, and you will only be storing a small amount of data per user (not to mention transfer less data over the wire) - these all translate to better performance on your DB.
当然，您可能会看到一些性能提升。也就是说，如果您损害数据库设计，这可能不值得。这是一个规范化问题。如果不知道您计划如何使用数据，就很难确切地说出您应该如何构建数据。
Sure, you might see a little performance boost. That said, it may not be worth it if you compromise your database design. This is a normalization question. Without knowing how you're planning on using the data it is difficult to say exactly how you should structure your data.
我认为这是浪费时间。当心微观优化。对于这样的减少，您将失去数据库中该列的所有含义，失去以相同字母开头的值的灵活性，必须修改 DAL 以在给定整个单词时仅传递第一个字母。
我通常会索引该列并将其保留为完整字符串。如果这是您系统中的瓶颈，那么您将编写世界上最快、最高效的程序，并且无论如何您都不需要我的谦虚建议;-)。
I think it is a waste of time. Beware of micro-optimizations. For such a reduction you would loose all meaning in the database for that column, loose the flexibility to have values that start with same letter, have to modify your DAL to pass in just the first letter when given a whole word.
I would normally index the column and leave it as full strings. If that was the bottleneck in your system then your writing the fastest, most efficient programs in the world and you don't need my humble advice anyway ;-).
是的，这应该会让你的代码更快，使用代码 1,2,3,4 甚至会更快，原因有 2 个：
1) 比较 int（可以在 1 个 CPU 周期内比较）比比较两个字符串花费的时间更少。即使 1 个字符也可能需要更多的周期，因为如果“A”等于“a”等，它需要工作。2
）它在表中占用的空间更少，在数据库中，行越小，数据库速度就越快，因为 SQL 会能够在页面上容纳更多行。
您可能需要有大量用户才能注意到差异，但存储代码而不是值无疑是一个好习惯。
问候 GJ
Yes this should make your code faster, using a code 1,2,3,4 would be even fastest for 2 reasons
1) It takes less time to compare an int(that can be compared in 1 CPU cycle) than comparing two strings. Even 1 char may take more cycles because it needs to work if 'A' equals 'a' etc.
2) it takes up less space in the table, in a DB the smaller your rows are the faster your DB will be becaus SQL will be able to fit more rows onto a page.
You may need to have a serious number of users to notice the difference, but it would defenitley be good practice to store codes instead of values.
Regards GJ
主表中添加一个 fk
您可以创建一个查找表，并在配置文件表所在的
这将允许您在字符表中的 ProfileID 上添加更小、更有效的索引
You could create a lookup table and which has a fk into the main table
where profile table is
This would allow you to add a smaller more efficient index onto the ProfileID in the Character table
如果您希望最大限度地提高性能并且不关心标准化这些数据（这几乎总是最好的选择），您可以将这 4 个属性存储为位：
这是不好的做法，因为如果您决定，您将成为 SOL添加“isMagnificent”或将“isIndestructable”更改为“isResilient”，但如果您确实只有 4 个正在跟踪的属性，则 4 位很难被击败。
If you're looking to maximize your performance and don't care about normalizing this data (which is almost always the best option), you could store those 4 attributes as bits:
It's bad practice in that you'll be SOL if you decide to add "isMagnificent" or change "isIndestructable" to "isResilient", but if you've really just got 4 attributes you're tracking, 4 bits are hard to beat.