43

In a MySQL database, how would I set a varchar to have unlimited length, so that I can store long web pages? If not, then what is the Maximum Size?

I know about Text Types to store larger strings. Is there any limitations on using test data type which I have to handle?

Pang
  • 9,564
  • 146
  • 81
  • 122
Birlla
  • 1,700
  • 2
  • 15
  • 17

5 Answers5

78

VARCHAR can store upto 255 chars before MySQL 5.0.3 and 65,535 chars in 5.0.3 and later versions.

To store large data in Mysql database you can use

TEXT , MEDIUMTEXT , LONGTEXT

TEXT can store 65,535 characters (approx 64KB)
MEDIUMTEXT = 16,777,215 characters (approx 16 MB) 
LONGTEXT = 4,294,967,295 chars (approx 4GB)
user2635399
  • 3
  • 1
Rupam
  • 1,502
  • 13
  • 23
  • It looks like a TEXT is 2^16 - 2 bytes aka characters, or 64 KiB - 2 bytes, or 65,536 - 2 bytes, based on the mysql documentation: https://dev.mysql.com/doc/refman/8.0/en/storage-requirements.html#data-types-storage-reqs-strings ... I recommend checking in that mysql doc for a more clear/detailed specification. – user3773048 Mar 07 '23 at 00:55
6

You can try using varchar(max)

HaydnJW
  • 337
  • 1
  • 13
4

you should try this method: refer this link:

MySQL - How to increase varchar size of an existing column in a database without breaking existing data?

alter table table_name modify col_name varchar(10000)

ALTER TABLE `table_name`
   CHANGE COLUMN `col_name` `col_name` VARCHAR(10000);

or otherwise you should use this method: 

TEXT can store 65,535 characters (approx 64KB)
MEDIUMTEXT = 16,777,215 characters (approx 16 MB) 
LONGTEXT = 4,294,967,295 chars (approx 4GB)
Community
  • 1
  • 1
jmail
  • 5,944
  • 3
  • 21
  • 35
1

To answer the other part of his question ...

Are there any limitations which i have to handle?

There can be all sorts of unforeseen limits depending on versions of software, data storage engine, etc.

I encountered these.

ALTER TABLE reportserver.report ADD reportquery varchar(65535)

Error Code: 1074. Column length too big for column 'reportquery' (max = 21845); use BLOB or TEXT instead

ALTER TABLE reportserver.report ADD reportquery varchar(21845)

Error Code: 1118. 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

You'd probably encounter these when setting up your table or not at all, but rather than risk not being able to add more columns, or not being able to make a column wider, you might want to go the TEXT route straight away if you have seriously wide columns.

Ivan
  • 41
  • 4
-5

You can use varchar2. It is better than varchar in many ways

http://www.orafaq.com/faq/what_is_the_difference_between_varchar_varchar2_and_char_data_types

Or

You can try TEXT. It will work for you

Amrit Singh
  • 41
  • 6