Question:
What types of data exist in MySQL?
Which is better for storing long texts? (document in HTML form for example) Why?
Answer:
Depends on what you need. No details, context in the question.
A VARCHAR allows 65535 characters ( bytes if using an older version < 5.0). But this limit is lower in practice. The row size is also this, that's why you can't have multiple columns of this type with big sizes. A single column can fill the entire row or it can have hundreds of VARCHAR columns. As the name implies, the actual size occupied varies according to the need.
A TEXT allows for 65535 bytes . And this differentiation is important if you are using UTF-8 or other multibyte encoding as a character can take up 3 bytes (this can change depending on the charset chosen – the use of 3 bytes was a mistake in the initial standard UTF-8, for there is another charset ).
Just like TINYTEXT supports 255 bytes . And if you need more than that you have to use MEDIUMTEXT (16MB) or LONGTEXT (4GB).
The storage of these TEXT types is done outside the normal line, it is in a specific area and doesn't take up space on the line (except for the pointer, which is very small, for the location of the data, so it doesn't pay if you know that most texts will be little).
In MyISAM a small part of the text is on the line, even with this rule. The occupation of space on the line is also variable.
I won't even talk about the fixed-length CHAR that seems to be way out of line with what you need.
Thedecision is not so simple .
It is obvious that texts in VARCHAR need to have their sizes more controlled, they cannot be that big. When you don't have so much control or you know that the size will be very large, that is, it's a text and not just a simple string , you have to use TEXT , there's no way.
TEXT tends to have slower access in most situations. Nothing very significant but it is common for it to need more disk accesses than VARCHAR even though it has the same size. As the text is stored separately, it's like having another table and doing a JOIN , although the process is different.
Temporary tables with any type of TEXT need to be on disk , which is much slower than normal tables that can only be in memory.
Using a TEXT on common (non -full-text ) indexes is necessary to determine the key size. You can't index the entire column, you need to say how many characters you want in the index. And another important point is that the texts in the key are always stored with spaces so that all keys have the same size. This is inefficient and if you don't know what you are doing, you will get false results.
TEXT does not allow DEFAULT values, VARCHAR does.
I've seen some people say that you should always use TEXT . This is not quite true. He has disadvantages. Use VARCHAR until you have a reason to use TEXT .
Your case seems to require at least one MEDIUMTEXT , not only for the size, but for the semantics of what you're storing. But if some of these restrictions create a problem for you and you can guarantee that these HTMLs are small and won't have several on the same line, don't discard the VARCHAR .