According to 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.
1 votes
Un blog détaillé: goo.gl/Hli6G3