What is the MySQL VARCHAR max size

Question

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

User · Answer

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 http   dev mysql com doc refman 5 7 en blob html Ref for MySQLv5 6 http   dev mysql com doc refman 5 6 en blob html Ref for MySQLv5 5 http   dev mysql com doc refman 5 5 en blob html Ref for MySQLv5 1 http   dev mysql com doc refman 5 1 en blob html Ref for MySQLv5 0 http   dev mysql com doc refman 5 0 en blob html   Even more  Checkout more details on Data Type Storage Requirements which deals with storage requirements for all data types

User · Answer

Source     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

User · Answer

You can use TEXT type  which is not limited to 64KB

User · Answer

you can also use MEDIUMBLOB LONGBLOB or MEDIUMTEXT LONGTEXT  A BLOB type in MySQL can store up to 65 534 bytes  if you try to store more than this much data MySQL will truncate the data  MEDIUMBLOB can store up to 16 777 213 bytes  and LONGBLOB can store up to 4 294 967 292 bytes

User · Answer

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

User · Answer

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  quot real quot  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  quot only quot  about half a million characters   16M-1  3   5 592 405  but it still greatly expands your range

User · Answer

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   goo gl Hli6G3

[mysql] What is the MySQL VARCHAR max size?

Examples related to mysql

Examples related to varchar

Examples related to maxlength