Equivalent of varchar(max) in MySQL?

by

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)

More Related Contents:

  1. MySQL Error #1071 – Specified key was too long; max key length is 767 bytes
  2. MySQL: Large VARCHAR vs. TEXT?
  3. What is the MySQL VARCHAR max size?
  4. Difference between VARCHAR and TEXT in MySQL [duplicate]
  5. MySQL VARCHAR Lengths and UTF-8
  6. MySQL varchar index length
  7. MySQL too long varchar truncation/error setting
  8. SQL unique varchar case sensitivity question
  9. What to do when you need integers larger than 20 digits on mysql?
  10. What are the optimum varchar sizes for MySQL?
  11. Which ORM should I use for Node.js and MySQL? [closed]
  12. What does it mean by select 1 from table?
  13. MySQL select all rows from last month until (now() – 1 month), for comparative purposes
  14. Incorrect integer value: ” for column ‘id’ at row 1
  15. apache mysql – “packets out of order” on 3306
  16. find number of rows in returned mysql result (nodejs)
  17. What is difference between storing data in a blob, vs. storing a pointer to a file?
  18. How to estimate SQL query timing?
  19. mysql select distinct rows into a comma delimited list column
  20. Access mysql running on localhost from minikube
  21. Select from same table as an Insert or Update
  22. How do I limit the number of rows per field value in SQL?
  23. Disadvantages of quoting integers in a Mysql query?
  24. Error Code: 1062. Duplicate entry ‘1’ for key ‘PRIMARY’
  25. How do I add more members to my ENUM-type column in MySQL?
  26. How to get mysql random integer range?
  27. How to find nearest location using latitude and longitude from SQL database?
  28. Are Users ‘User’@’%’ and ‘User’@’localhost’ not the same?
  29. MySql – Update table using select statment from same table
  30. How to allow fulltext searching with hyphens in the search query

Leave a Comment