CODEWITHSUNDEEP
mysql
Birlla
Birlla

Reputation: 1840

How to set a varchar to have unlimited length?

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?

Upvotes: 49

Views: 107413

Answers (5)

Rupam
Rupam

Reputation: 1662

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 and LONGTEXT data types:

TEXT can store 65,535 characters (approx 64KB)
MEDIUMTEXT = 16,777,215 characters (approx 16 MB) 
LONGTEXT = 4,294,967,295 chars (approx 4GB)

Upvotes: 89

Ivan
Ivan

Reputation: 41

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.

Upvotes: 1

jmail
jmail

Reputation: 6132

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)

Upvotes: 4

HaydnJW
HaydnJW

Reputation: 337

You can try using varchar(max)

Upvotes: 6

Amrit Singh
Amrit Singh

Reputation: 41

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

Upvotes: -5

Related Questions