-1

According to the MySQL Doc, I think VARCHAR and TEXT are stored in the same way in MySQL, because in the introduction to InnoDB Row Formats, both VARCHAR and TEXT are treated as the variable-length column values.

However, the MySQL doc also says "The internal representation of a MySQL table has a maximum row size limit of 65,535 bytes, even if the storage engine is capable of supporting larger rows. BLOB and TEXT columns only contribute 9 to 12 bytes toward the row size limit because their contents are stored separately from the rest of the row." (from here). According to this sentence, the VARCHAR and TEXT should be stored differently in MySQL, because VARCHAR(65535) contributes more than 12 bytes toward the row size.

So, which one is right? Are VARCHAR and TEXT stored differently or the same in MySQL?

About this question, I saw some discussions in the answer posted by Code Commander below question Difference between VARCHAR and TEXT in MySQL. However, those discussions did not answer my question.

Shadow
  • 33,525
  • 10
  • 51
  • 64
Sihao Xie
  • 13
  • 1
  • Your first paragraph states _'... both VARCHAR and TEXT are treated as the variable-length column values'._ This does not mean that they are stored the same way, it means they are both treated as columns of variable length (as opposed to an INT(11) column for example, which has a non-variable size). The info in the second paragraph is correct, they are stored differently. – lukas.j Nov 28 '21 at 11:48
  • It's a common pattern to have specific types eg `text` to have its content stored *off-row* with just a *pointer* stored in row data. – Stu Nov 28 '21 at 12:02
  • @lukas.j Thank you for your answer. The doc says "Tables that use the REDUNDANT row format store the first 768 bytes of variable-length column values (VARCHAR, VARBINARY, and BLOB and TEXT types) in the index record within the B-tree node, with the remainder stored on overflow pages." Doesn't this mean that VARCHAR and TEXT are stored in the same way? – Sihao Xie Nov 29 '21 at 08:54
  • That's correct, but this is about the first 768 bytes only. The rest of the needed space is stored differently. – lukas.j Nov 29 '21 at 09:00
  • @Stu Thank you for your answer. According to your answer, Is VARCHAR stored in the same way as text? – Sihao Xie Nov 29 '21 at 09:01
  • @lukas.j Could you please explain more about "The rest of the needed space is stored differently"? Because according to the doc, the remainder of both VARCHAR and TEXT will be stored on overflow pages. What is the difference between them? – Sihao Xie Nov 29 '21 at 09:12

1 Answers1

0

The documentation is correct, but the answer is complex in MySQL because of its architecture that supports multiple storage engines.

Each storage engine may have its own way of storing data, and its own limit on row size.

Then after MySQL Server fetches a row from the storage engine, the part of the server that is independent of storage engine must also store in the row in RAM as it does joins, aggregation, and finally returns rows in the result set.

Yes, it is true that variable-length data types (VARCHAR, VARBINARY, TEXT, BLOB, and their variations) do not count fully toward the 64KB row size limit. Only fixed-width data types line INT, DATETIME, CHAR, etc. count in that way, and a small part of the long variable-length columns.

InnoDB has a row size limit more like 8KB for fixed-length data types and the small portion of variable-length data types. Even within InnoDB, there are different answers, because ROW_FORMAT=COMPACT means the first 768 bytes of a long string column is stored within the 8KB row, whereas ROW_FORMAT=DYNAMIC means only a 20-byte pointer is stored within the 8KB row.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Thank you for your answer. When we create a table, who counts the row size and applies the row size limit, the server or the storage engine or both of them? – Sihao Xie Nov 29 '21 at 09:41
  • Both of them have different limits, so they both need to check. There are row size checks at CREATE TABLE time, and also when you INSERT. – Bill Karwin Nov 29 '21 at 13:48
  • I think this sentence, "BLOB and TEXT columns only contribute 9 to 12 bytes toward the row size limit", is for the server instead of for the storage engines. And the storage engines have their own way to calculate the row size. Am I right? – Sihao Xie Nov 30 '21 at 15:54
  • Yes, that's right. In the case of InnoDB, it depends on the [ROW_FORMAT](https://dev.mysql.com/doc/refman/8.0/en/innodb-row-format.html) option of each table. – Bill Karwin Nov 30 '21 at 16:01