MySQL varchar(2000)vs文字? [英] MySQL varchar(2000) vs text?

问题描述

我平均需要存储一段文本，在数据库中大约需要800个字符.在极少数情况下，它最多可以包含2000-2500个字符.我已经阅读了手册，我知道已经有很多这样的问题，但是我已经阅读了10多个关于stackoverflow的问题，但是仍然很难弄清楚我应该只使用文本还是类似varchar( 2000).似乎有一半人说使用varchar，而另一半则说文本.有人说，如果您的字符数超过255个，则始终使用文本(是的，这是在5.0.3之后允许varchar最多为65k的字符).但是后来我想到，如果每次字符超过255个时我都要使用文本，那么如果那总是最好的选择，为什么mysql根本不增加大小呢?

I need to store on average a paragraph of text, which would be about ~800 characters in the database. In some rare cases it may go up to 2000-2500~ characters. I've read the manual and I know there are many of these questions already, but I've read over 10+ questions on stackoverflow and I still find it a bit hard to figure out whether I should simply use text or something like varchar(2000). Half seem to say use varchar, while the other half say text. Some people say always use text if you have more than 255 characters (yea, this was after 5.0.3 which allowed varchar up to 65k). But then I thought to myself if I were to use text everytime the characters were over 255, then why did mysql bother increasing the size at all if that was always the best option?

我所阅读的存储空间大小都是可变的，所以我的情况不会有差异吗?我个人倾向于varchar(2000)，然后我读到varchar内联存储数据，而文本则不行.这是否意味着如果我不断选择该列，将数据存储为varchar会更好，反之，如果我很少选择此列，那么使用文本会更好?如果是这样，我想我现在选择text列，因为我在表上运行查询的很多次都不会选择该列.如果重要的话，该表也经常被联接(但不会选择该列)，这还会进一步增加使用文本的好处吗?

They both have a variable size in storage I've read, so would there be no difference in my situation? I was personally leaning towards varchar(2000) then I read that varchar stores the data inline while text doesn't. Does this mean that if I constantly select this column, storing the data as varchar would be better, and conversely if I rarely select this column then using text would be better? If that is true, I think I would now choose the text column as I won't be selecting this column many of the times I run a query on the table. If it matters, this table is also frequently joined to as well (but won't be selecting the column), would that also further the benefit of using text?

我的假设是否正确，在这种情况下我应该使用文本?

Are my assumptions correct that I should go with text in this case?

推荐答案

当表具有TEXT或BLOB列时，该表无法存储在内存中.这意味着每个查询(不命中高速缓存)都必须访问文件系统-比内存慢几个数量级.

When a table has TEXT or BLOB columns, the table can't be stored in memory. This means every query (which doesn't hit cache) has to access the file system - which is orders of magnitude slower than the memory.

因此，您应该将此TEXT列存储在单独的表中，该表仅在实际需要时才可以访问.这样，原始表可以存储在内存中，并且速度更快.

Therefore you should store this TEXT column in a seperate table which is only accessed when you actually need it. This way the original table can be stored in memory and will be much faster.

将其视为将数据分为一个内存表"和一个文件表".这样做的原因是为了避免访问文件系统，除非必要时(即仅当您需要文本时).

Think of it as separating the data into one "memory table" and one "file table". The reason for doing this is to avoid accessing of the filesystem except when neccessary (i.e. only when you need the text).

通过将文本存储在多个表中不会获得任何收益.您仍然必须访问文件系统.

You don't earn anything by storing the text in multiple tables. You still have to access the file system.

抱歉，我的意思是例如论坛脚本，在posts表中，它们可能存储了20列的post数据，它们还将实际的post作为文本字段存储在> same表中.这样帖子栏应该分开吗?

Sorry what I meant was for example, a forum script, in the posts table they might be >storing 20 columns of post data, they also store the actual post as a text field in the >same table. So that post column should be separated out?

是的

似乎有一个叫做post的表很奇怪，但是实际的post没有存储在那里，也许>不确定另一个表"actual_post"中的信息.

It seems weird to have a table called post, but the actual post isn't stored there, maybe >in another table called "actual_post" not sure lol.

您可以尝试(posts，post_text)或(post_details，posts)或类似的东西.

You can try (posts, post_text) or (post_details, posts) or something like that.

我有一个标签表，其中只有三个字段，即tag_id，tag和description.这样> description列也应该分开吗?所以我需要一个标签表和一个> tags_description表来存储3列?

I have a tags table that has just three fields, tag_id, tag, and description. So that >description column should also be separated out? So I need a tags table and a >tags_description table just to store 3 columns?

如果描述是TEXT列，并且您对不需要该描述的表运行查询，那肯定是更好的选择.

If the description is a TEXT column and you run queries against this table that doesn't need the description it would certainly be preferable.

这篇关于MySQL varchar(2000)vs文字?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！