The length can be specified as a value from 0 to 65,535. The effective maximum length of a VARCHAR is subject to the maximum row size (65,535 bytes, which is shared among all columns) and the character set used.
MySQL VARCHAR Maximum Length Although VARCHAR supports the maximum size at 65535 characters, the actual maximum value depends on other columns in the table and character set: Maximum row size is 65535 bytes in MySQL that shared among all columns in the table, except TEXT/BLOB columns.
varchar [ ( n | max ) ] Variable-size string data. Use n to define the string size in bytes and can be a value from 1 through 8,000 or use max to indicate a column constraint size up to a maximum storage of 2^31-1 bytes (2 GB).
CHAR, VARCHAR, and VARCHAR(MAX) CHAR columns should be used for columns that vary little in length. String values that vary significantly in length and are no longer than 8,000 bytes should be stored in a VARCHAR column. If you have huge strings (over 8,000 bytes), then VARCHAR(MAX) should be used.
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
Here are some examples:
The maximum row size is 65535, but a varchar also includes a byte or two to encode the length of a given string. So you actually can't declare a varchar of the maximum row size, even if it's the only column in the table.
mysql> CREATE TABLE foo ( v VARCHAR(65534) );
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
But if we try decreasing lengths, we find the greatest length that works:
mysql> CREATE TABLE foo ( v VARCHAR(65532) );
Query OK, 0 rows affected (0.01 sec)
Now if we try to use a multibyte charset at the table level, we find that it counts each character as multiple bytes. UTF8 strings don't necessarily use multiple bytes per string, but MySQL can't assume you'll restrict all your future inserts to single-byte characters.
mysql> CREATE TABLE foo ( v VARCHAR(65532) ) CHARSET=utf8;
ERROR 1074 (42000): Column length too big for column 'v' (max = 21845); use BLOB or TEXT instead
In spite of what the last error told us, InnoDB still doesn't like a length of 21845.
mysql> CREATE TABLE foo ( v VARCHAR(21845) ) CHARSET=utf8;
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
This makes perfect sense, if you calculate that 21845*3 = 65535, which wouldn't have worked anyway. Whereas 21844*3 = 65532, which does work.
mysql> CREATE TABLE foo ( v VARCHAR(21844) ) CHARSET=utf8;
Query OK, 0 rows affected (0.32 sec)
TLDR; MySql does not have an equivalent concept of varchar(max)
, this is a MS SQL Server feature.
varchar(max)
is a feature of Microsoft SQL Server.
The amount of data that a column could store in Microsoft SQL server versions prior to version 2005 was limited to 8KB. In order to store more than 8KB you would have to use TEXT
, NTEXT
, or BLOB
columns types, these column types stored their data as a collection of 8K pages separate from the table data pages; they supported storing up to 2GB per row.
The big caveat to these column types was that they usually required special functions and statements to access and modify the data (e.g. READTEXT
, WRITETEXT
, and UPDATETEXT
)
In SQL Server 2005, varchar(max)
was introduced to unify the data and queries used to retrieve and modify data in large columns. The data for varchar(max)
columns is stored inline with the table data pages.
As the data in the MAX column fills an 8KB data page an overflow page is allocated and the previous page points to it forming a linked list. Unlike TEXT
, NTEXT
, and BLOB
the varchar(max)
column type supports all the same query semantics as other column types.
So varchar(MAX)
really means varchar(AS_MUCH_AS_I_WANT_TO_STUFF_IN_HERE_JUST_KEEP_GROWING)
and not varchar(MAX_SIZE_OF_A_COLUMN)
.
In order to get the same amount of storage as a varchar(max)
in MySql you would still need to resort to a BLOB
column type. This article discusses a very effective method of storing large amounts of data in MySql efficiently.
The max length of a varchar is
65535
divided by the max byte length of a character in the character set the column is set to (e.g. utf8=3 bytes, ucs2=2, latin1=1).
minus 2 bytes to store the length
minus the length of all the other columns
minus 1 byte for every 8 columns that are nullable. If your column is null/not null this gets stored as one bit in a byte/bytes called the null mask, 1 bit per column that is nullable.
alter table prg_ar_report_colors add Text_Color_Code VARCHAR(max);
alter table prg_ar_report_colors add Text_Color_Code longtext;
alter table prg_ar_report_colors add Text_Color_Code CLOB;
Mysql Converting column from VARCHAR to TEXT when under limit size!!!
mysql> CREATE TABLE varchars1(ch3 varchar(6),ch1 varchar(3),ch varchar(4000000))
;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> SHOW WARNINGS;
+-------+------+---------------------------------------------+
| Level | Code | Message |
+-------+------+---------------------------------------------+
| Note | 1246 | Converting column 'ch' from VARCHAR to TEXT |
+-------+------+---------------------------------------------+
1 row in set (0.00 sec)
mysql>
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With