Varchar或文本数据类型，字符串长度最多为数千个字符 [英] Varchar or Text Data Types for strings length up to few thousands of chars

问题描述

我有一个小型的社交网站，上面有文章和评论.
我决定让用户创建具有任意数量字符的帖子，并且我认为存储此类内容的最佳数据类型是文本.
但对于注释，在大多数情况下，用户只写一行或几行文本.这就是为什么我认为我必须将注释字符串的长度限制为最多3000甚至6000个字符.
那么，我应该使用哪种数据类型进行评论?
Varchar 或文本?

I have a small social networking website, with posts and comments.
I decided to let users create posts with any number of charس they want, and I think the best Datatype for storing such things is Text.
But for comments, in most cases users write only one or a few lines of text. That's why I think that I have to limit comment string length to something like 3000 or even 6000 chars maximum.
So, what Datatype should I use for my comments ?
Varchar or Text?

推荐答案

VARCHAR和TEXT在不同的地方有不同的地方.我说了几句话:

There are various places where VARCHAR and TEXT do or do not differ. Some things I say:

• 如果最大长度超过512个字符，请使用TEXT.
• 从不使用TINYTEXT-相对于VARCHAR(255)只有负数.
• 不要使用VARCHAR(255);选择一个合理的最大值而不是255.(这是与复杂查询中的临时表有关的次要优化.)
• 仅将CHAR用于长度固定的东西.在几乎所有此类情况下，请粘贴CHARACTER SET ascii(或latin1). (否则，它将占用比您预期更多的空间.
• 使用CHARACTER SET ut8mb4. (例外变得越来越罕见.)

• If the maximum length is more than 512 characters, go with TEXT.
• Never use TINYTEXT -- it has only negatives relative to VARCHAR(255).
• Don't use VARCHAR(255); pick a reasonable max instead of 255. (This is a minor optimization relating to temp table in complex queries.)
• Use CHAR only for things that are truly fixed length. In almost all such case, tack on CHARACTER SET ascii (or latin1). (Else it will take more space than you expect.
• Use CHARACTER SET ut8mb4. (Exceptions are becoming more an more rare.)

(对不起，我离题了.回到正题...)

(Sorry, I digress. Back on topic...)

I索引，在InnoDB行的布局(有4个不同的ROW_FORMATs)等中，VARCHAR(513)与TEXT本质上没有区别.

I indexing, in layout of InnoDB rows (there are 4 different ROW_FORMATs), etc, VARCHAR(513) will be essentially indistinguishable from TEXT.

VARCHAR的少数几个参数之一是它将存储限制为给定的长度.但是，这有多重要?

One of the few arguments for VARCHAR is that it limits the store to the length given. But how often is that important?

这篇关于Varchar或文本数据类型，字符串长度最多为数千个字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！