mysql – What is the difference between TEXT and LONGTEXT?

Question:

I'm designing a system where I'm going to import data from an Excel. As this data will be temporary, I intend to save the information of this imported column in JSON format in the database.

So, I was in doubt between using TEXT or LONGTEXT .

Of course, this case is just an example, but there are other cases where the doubt comes to mind, for example, when saving a post from a blogging system in the bank. Would I have to use TEXT or LONGTEXT ?

I imagine that there is a difference just because of the names, and not because I know the technical details of the difference between one and the other.

So my question is:

  • What is the difference between LONGTEXT and TEXT ?

  • Would using LONGTEXT make my database use more resources than using TEXT ?

  • Is there a difference in reading speed or performance when choosing one or the other?

  • In what cases would I have to use LONGTEXT instead of TEXT ?

NOTE : Actually, I don't even want someone pointing out what I should do with my data that I'm importing in the answer, but I just want to know what is the difference between the two types (LONGTEXT and TEXT). Even because I know the new version of MYSQL is in JSON format, but that's another story, as I don't use the new version of MYSQL*.

Answer:

In the Documentation you will find a lot about each type, summarizing in Sizes:

      Tipo | Tamanho Máximo
-----------+--------------------------------------
  TINYTEXT |           255 (2^ 8−1) bytes         
      TEXT |        65,535 (2^16−1) bytes = 64 KiB
MEDIUMTEXT |    16,777,215 (2^24−1) bytes = 16 MiB
  LONGTEXT | 4,294,967,295 (2^32−1) bytes =  4 GiB

  • What is the difference between LONGTEXT and TEXT? Maximum Memory Allocation (Size) as Table above;

  • Would using LONGTEXT make my database use more resources than using TEXT? Exactly, for each line in your Bank it would be necessary to reserve, clean, validate more memory.

  • Is there a difference in reading speed or performance when choosing one or the other? Performance differences are small on small Bases (you practically don't care about performance on bases with tens or a few hundred results), but can be very palpable for large ones (1TB+), so the answer is: yes.
  • In what cases would I have to use LONGTEXT instead of TEXT? When storing more than 16Mi Chars. Store an entire book, or all the information in a RAW image, for example.
Post Views: 67
Scroll to Top