Varchar(255) V Tinyblob V Tinytext

What's the difference between VARCHAR(255) and TINYTEXT string types in MySQL?

You cannot assign a DEFAULT value to a TINYTEXT and you cannot create an unprefixed index on the latter.

Internally, additional objects are allocated in memory to handle TEXT (incl. TINYTEXT) columns which can cause memory fragmentation on the large recordsets.

Note that this only concerns the column's internal representation in the recordsets, not how they are stored on the disk.

MySQL: Do i use TINYTEXT or VARCHAR(6) for short words

Instead of tinytext use varchar. It's the good practice

For innodb table in MySQL, which is faster: varchar(255) or tinytext?

Don't believe if anyone tells you that TINYTEXT is stored in other way, than VARCHAR.

The actual differences are:

• TINYTEXT and other TEXT fields are stored separately from in-memory row inside MySQL heap, whereas VARCHAR() fields add up to 64k limit (so you can have more than 64k in TINYTEXTs, whereas you won't with VARCHAR).

• TINYTEXT and other 'blob-like' fields will force SQL layer (MySQL) to use on-disk temporary tables whenever they are used, whereas VARCHAR will be still sorted 'in memory' (though will be converted to CHAR for the full width).

• InnoDB internally doesn't really care whether it is tinytext or varchar. It is very easy to verify, create two tables, one with VARCHAR(255), another with TINYINT, and insert a record to both. They both will take single 16k page - whereas if overflow pages are used, TINYTEXT table should show up as taking at least 32k in 'SHOW TABLE STATUS'.

I usually prefer VARCHAR(255) - they don't cause too much of heap fragmentation for single row, and can be treated as single 64k object in memory inside MySQL. On InnoDB size differences are negligible.

Is there a good reason I see VARCHAR(255) used so often (as opposed to another length)?

Historically, 255 characters has often been the maximum length of a VARCHAR in some DBMSes, and it sometimes still winds up being the effective maximum if you want to use UTF-8 and have the column indexed (because of index length limitations).

TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT maximum storage sizes

From the documentation (MySQL 8) :

      Type | Maximum length
-----------+-------------------------------------
  TINYTEXT |           255 (2 8−1) bytes
      TEXT |        65,535 (216−1) bytes = 64 KiB
MEDIUMTEXT |    16,777,215 (224−1) bytes = 16 MiB
  LONGTEXT | 4,294,967,295 (232−1) bytes =  4 GiB

Note that the number of characters that can be stored in your column will depend on the character encoding.

MySQL: varchar(64) vs. varchar(255), is there a difference for small strings?

No. Some people just use VARCHAR(255) for all short strings columns. For CHAR columns, all strings will padded with blanks up to the defined length.

---

Related Topics