What is the MySQL VARCHAR max size?
Solution 1
Keep in mind that MySQL has a maximum row size limit
The internal representation of a MySQL table has a maximum row size limit of 65,535 bytes, not counting BLOB and TEXT types. 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. Read more about Limits on Table Column Count and Row Size.
Maximum size a single column can occupy, is different before and after MySQL 5.0.3
Values in VARCHAR columns are variable-length strings. The length can be specified as a value from 0 to 255 before MySQL 5.0.3, and 0 to 65,535 in 5.0.3 and later versions. The effective maximum length of a VARCHAR in MySQL 5.0.3 and later is subject to the maximum row size (65,535 bytes, which is shared among all columns) and the character set used.
However, note that the limit is lower if you use a multi-byte character set like utf8 or utf8mb4.
Use TEXT
types inorder to overcome row size limit.
The four TEXT types are TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT. These correspond to the four BLOB types and have the same maximum lengths and storage requirements.
More details on BLOB and TEXT Types
- Ref for MySQLv8.0 https://dev.mysql.com/doc/refman/8.0/en/blob.html
- Ref for MySQLv5.7 https://dev.mysql.com/doc/refman/5.7/en/blob.html
- Ref for MySQLv5.6 https://dev.mysql.com/doc/refman/5.6/en/blob.html
Even more
Checkout more details on Data Type Storage Requirements which deals with storage requirements for all data types.
Solution 2
As per the online docs, there is a 64K row limit and you can work out the row size by using:
row length = 1
+ (sum of column lengths)
+ (number of NULL columns + delete_flag + 7)/8
+ (number of variable-length columns)
You need to keep in mind that the column lengths aren't a one-to-one mapping of their size. For example, CHAR(10) CHARACTER SET utf8
requires three bytes for each of the ten characters since that particular encoding has to account for the three-bytes-per-character property of utf8
(that's MySQL's utf8
encoding rather than "real" UTF-8, which can have up to four bytes).
But, if your row size is approaching 64K, you may want to examine the schema of your database. It's a rare table that needs to be that wide in a properly set up (3NF) database - it's possible, just not very common.
If you want to use more than that, you can use the BLOB
or TEXT
types. These do not count against the 64K limit of the row (other than a small administrative footprint) but you need to be aware of other problems that come from their use, such as not being able to sort using the entire text block beyond a certain number of characters (though this can be configured upwards), forcing temporary tables to be on disk rather than in memory, or having to configure client and server comms buffers to handle the sizes efficiently.
The sizes allowed are:
TINYTEXT 255 (+1 byte overhead)
TEXT 64K - 1 (+2 bytes overhead)
MEDIUMTEXT 16M - 1 (+3 bytes overhead)
LONGTEXT 4G - 1 (+4 bytes overhead)
You still have the byte/character mismatch (so that a MEDIUMTEXT utf8
column can store "only" about half a million characters, (16M-1)/3 = 5,592,405
) but it still greatly expands your range.
Solution 3
The max length of a varchar is subject to the max row size in MySQL, which is 64KB (not counting BLOBs):
VARCHAR(65535) However, note that the limit is lower if you use a multi-byte character set:
VARCHAR(21844) CHARACTER SET utf8
Solution 4
From MySQL documentation:
The effective maximum length of a VARCHAR in MySQL 5.0.3 and later is subject to the maximum row size (65,535 bytes, which is shared among all columns) and the character set used. For example, utf8 characters can require up to three bytes per character, so a VARCHAR column that uses the utf8 character set can be declared to be a maximum of 21,844 characters.
Limits for the VARCHAR varies depending on charset used. Using ASCII would use 1 byte per character. Meaning you could store 65,535 characters. Using utf8 will use 3 bytes per character resulting in character limit of 21,844. BUT if you are using the modern multibyte charset utf8mb4 which you should use! It supports emojis and other special characters. It will be using 4 bytes per character. This will limit the number of characters per table to 16,383. Note that other fields such as INT will also be counted to these limits.
Conclusion:
utf8 maximum of 21,844 characters
utf8mb4 maximum of 16,383 characters
Solution 5
Before Mysql version 5.0.3 Varchar datatype can store 255 character, but from 5.0.3 it can be store 65,535 characters.
BUT it has a limitation of maximum row size of 65,535 bytes. It means including all columns it must not be more than 65,535 bytes.
In your case it may possible that when you are trying to set more than 10000 it is exceeding more than 65,535 and mysql will gives the error.
For more information: https://dev.mysql.com/doc/refman/5.0/en/column-count-limit.html
blog with example: http://sforsuresh.in/mysql_varchar_max_length/
user1832628
Updated on July 08, 2022Comments
-
user1832628 almost 2 years
I would like to know what the max size is for a MySQL VARCHAR type.
I read that the max size is limited by the row size which is about 65k. I tried setting the field to
varchar(20000)
but it says that that's too large.I could set it to
varchar(10000)
. What is the exact max I can set it to? -
Camden S. over 10 yearsKeep in mind that TEXT types are NOT able to be stored in memory tables, so there is a significant performance penalty for using them when a VARCHAR would suffice.
-
Richard H almost 10 yearswhat is a "long" string?
-
Robert Swisher almost 10 yearsI try to avoid TEXT columns though as they can cause temp tables to be created when present and sorting
-
Stijn de Witt almost 9 years'the three-bytes-per-character property of utf8' of MySql utf8, which isn't actually utf8 at all. In reality the max. bytes in a utf-8 char is 4. For this reason you should always set the encoding to
utf8mb4
in MySQL.utf8mb4
is MySql's name for what the rest of the word calls utf8. -
Paresh Gami over 8 yearsIf i am take varchar(200) for first name and i store only 6 char in this field then how many byte first name will be occupy?
-
rajukoyilandy over 8 years@PareshGami - 6+1=7 characters! In contrast to CHAR, VARCHAR values are stored as a 1-byte or 2-byte length prefix plus data. more...
-
paxdiablo over 8 years@StijndeWitt, thanks for that. clarified to indicate I meant MySQL's utf8 encoding method rather than UTF-8. I generally use the capitalised variant to indicate "real" UTF-8 since that's the accepted IANA convention.
-
Stijn de Witt over 8 yearsPlease stop using
CHARACTER SET utf8
in examples. It should beCHARACTER SET utf8mb4
(if you want all Unicode text to be stored properly... and who doesn't want that?) -
DreamWave over 8 yearsThis does not answer the question.
-
Wil Moore III over 7 yearsFor
CHARSET=utf8mb4
useVARCHAR(16383)
. -
kcrossen almost 7 yearsUsing utf8mb4 will put you up against the limit on index width in a situation where utf8 does not. If you examine the character sets that are included in utf8mb4 but not in utf8, you may find that including all of the various forms of hieroglyphics and other such arcane character sets is not worth the significant performance penalty (determined empirically). It's not quite as cut and dried as Stijn implies.
-
Brian Morearty about 5 yearsMany emojis are also present in utf8mb4 and missing from utf8, so that may change the equation of whether it's worthwhile.
-
Girish almost 5 yearsYour answer is not relevant to question.
-
Sumit over 3 yearsWhat's
delete_flag
?