TINYTEXT，TEXT，MEDIUMTEXT和LONGTEXT最大存储容量

最新推荐文章于 2024-07-30 03:49:46 发布

xfxf996

最新推荐文章于 2024-07-30 03:49:46 发布

阅读量2.5k

点赞数

文章标签： mysql innodb

原文链接：https://oldbug.net/q/wSXm/TINYTEXT-TEXT-MEDIUMTEXT-and-LONGTEXT-maximum-storage-sizes

版权

本文探讨了在UTF-8编码下，MySQL的TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT数据类型能存储的最大字符长度。由于字符编码的影响，存储容量会有所不同。对于英文，平均每个单词4.8个字母，接近每个字母1字节，而需要多字节字符的语言如希腊语或阿拉伯语，每个字符可能需要2字节。文章还提醒，虽然VARCHAR通常优于TINYTEXT，但在宽行情况下，TINYTEXT可用于节省存储空间，但可能会有性能开销。

摘要由CSDN通过智能技术生成

本文翻译自：TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT maximum storage sizes

Per the MySQL docs , there are four TEXT types: 根据MySQL文档，有四种TEXT类型：

TINYTEXT TINYTEXT
TEXT 文本
MEDIUMTEXT MEDIUMTEXT
LONGTEXT LONGTEXT

What is the maximum length that I can store in a column of each data type assuming the character encoding is UTF-8? 假设字符编码为UTF-8，我可以在每种数据类型的列中存储的最大长度是多少？

#1楼

参考：https://stackoom.com/question/wSXm/TINYTEXT-TEXT-MEDIUMTEXT和LONGTEXT最大存储容量

#2楼

From the documentation : 从文档：

Type | Maximum length
-----------+-------------------------------------
  TINYTEXT |           255 (2⁸−1) bytes
      TEXT |        65,535 (2¹⁶−1) bytes = 64 KiB
MEDIUMTEXT |    16,777,215 (2²⁴−1) bytes = 16 MiB
  LONGTEXT | 4,294,967,295 (2³²−1) bytes =  4 GiB

Note that the number of characters that can be stored in your column will depend on the character encoding . 需要注意的是，可以存储在列中的字符数将取决于字符编码 。

#3楼

Expansion of the same answer 扩展相同的答案

This SO post outlines in detail the overheads and storage mechanisms. 这篇SO帖子详细列出了开销和存储机制。
As noted from point (1), A VARCHAR should always be used instead of TINYTEXT. 如第（1）点所述，应始终使用A VARCHAR而不是TINYTEXT。 However, when using VARCHAR, the max rowsize should not exceeed 65535 bytes. 但是，使用VARCHAR时，max rowsize不应超过65535字节。
As outlined here http://dev.mysql.com/doc/refman/5.0/en/charset-unicode-utf8.html , max 3 bytes for utf-8. 如http://dev.mysql.com/doc/refman/5.0/en/charset-unicode-utf8.html所述，utf-8最多3个字节。

THIS IS A ROUGH ESTIMATION TABLE FOR QUICK DECISIONS! 这是一个用于快速决策的粗略估计表！

So the worst case assumptions (3 bytes per utf-8 char) to best case (1 byte per utf-8 char) 所以最坏的情况假设（每个utf-8字符3个字节）到最佳情况（每个utf-8字符1个字节）
Assuming the english language has an average of 4.5 letters per word 假设英语每个单词平均有4.5个字母
x is the number of bytes allocated x是分配的字节数

xx XX

      Type | A= worst case (x/3) | B = best case (x) | words estimate (A/4.5) - (B/4.5)
-----------+---------------------------------------------------------------------------
  TINYTEXT |              85     | 255               | 18 - 56
      TEXT |          21,845     | 65,535            | 4,854.44 - 14,563.33  
MEDIUMTEXT |       5,592,415     | 16,777,215        | 1,242,758.8 - 3,728,270
  LONGTEXT |   1,431,655,765     | 4,294,967,295     | 318,145,725.5 - 954,437,176.6

Please refer to Chris V's answer as well : https://stackoverflow.com/a/35785869/1881812 请参阅Chris V的答案： https ： //stackoverflow.com/a/35785869/1881812

#4楼

Rising to @Ankan-Zerob's challenge, this is my estimate of the maximum length which can be stored in each text type measured in words : 上升到@俺看-Zerob的挑战，这是我可以存储在话测量的每个文本类型的最大长度的估计：

      Type |         Bytes | English words | Multi-byte words
-----------+---------------+---------------+-----------------
  TINYTEXT |           255 |           ±44 |              ±23
      TEXT |        65,535 |       ±11,000 |           ±5,900
MEDIUMTEXT |    16,777,215 |    ±2,800,000 |       ±1,500,000
  LONGTEXT | 4,294,967,295 |  ±740,000,000 |     ±380,000,000

In English , 4.8 letters per word is probably a good average (eg norvig.com/mayzner.html ), though word lengths will vary according to domain (eg spoken language vs. academic papers), so there's no point being too precise. 在英语中 ，每个单词4.8个字母可能是一个很好的平均值（例如norvig.com/mayzner.html ），尽管单词长度会根据域名（例如口语与学术论文）而有所不同，因此没有必要过于精确。 English is mostly single-byte ASCII characters, with very occasional multi-byte characters, so close to one-byte-per-letter. 英语主要是单字节ASCII字符，偶尔有多字节字符，因此接近每字节一个字节。 An extra character has to be allowed for inter-word spaces, so I've rounded down from 5.8 bytes per word. 字间空间必须有一个额外的字符，所以我从每个字的5.8个字节向下舍入。 Languages with lots of accents such as say Polish would store slightly fewer words, as would eg German with longer words. 有很多口音的语言，例如说波兰语，会存储稍微少一些的单词，例如德语，单词较长。

Languages requiring multi-byte characters such as Greek, Arabic, Hebrew, Hindi, Thai, etc, etc typically require two bytes per character in UTF-8. 需要多字节字符的语言，如希腊语，阿拉伯语，希伯来语，印地语，泰语等，通常需要UTF-8中每个字符两个字节。 Guessing wildly at 5 letters per word, I've rounded down from 11 bytes per word. 每个单词5个字母疯狂地猜测，我从每个单词的11个字节向下舍入。

CJK scripts (Hanzi, Kanji, Hiragana, Katakana, etc) I know nothing of; CJK剧本（汉字，汉字，平假名，片假名等）我一无所知; I believe characters mostly require 3 bytes in UTF-8, and (with massive simplification) they might be considered to use around 2 characters per word, so they would be somewhere between the other two. 我认为字符大多需要UTF-8中的3个字节，并且（大量简化）它们可能被认为每个字使用大约2个字符，所以它们将介于其他两个字符之间。 (CJK scripts are likely to require less storage using UTF-16, depending). （CJK脚本可能需要使用UTF-16来减少存储，具体取决于）。

This is of course ignoring storage overheads etc. 这当然忽略了存储开销等。

#5楼

This is nice but doesn't answer the question: 这很好，但没有回答这个问题：

"A VARCHAR should always be used instead of TINYTEXT." “应始终使用VARCHAR而不是TINYTEXT。” Tinytext is useful if you have wide rows - since the data is stored off the record. 如果你有宽行，Tinytext很有用 - 因为数据存储在记录之外。 There is a performance overhead, but it does have a use. 存在性能开销，但确实有用。