0 like 0 dislike
367 views
asked by (2.4k points)  
retagged by

Please log in or register to answer this question.

3 Answers

1 like 0 dislike
answered by  
ALTER TABLE <tablename> CHANGE COLUMN <colname> <colname> VARCHAR(65536);

You have to list the column name twice, even if you aren't changing its name.

Note that after you make this change, the data type of the column will be MEDIUMTEXT.

MySQL row can be only 65535 bytes (not counting BLOB/TEXT columns). If you try to change a column to be too large, making the total size of the row 65536 or greater, you may get an error. If you try to declare a column of VARCHAR(65536) then it's too large even if it's the only column in that table, so MySQL automatically converts it to a MEDIUMTEXT data type.

 

0 like 0 dislike
answered by  
You can increase the value of column what your requirements but you should have increased the size above 12000-65535
commented by  
edited
Nice and aisimp explanation
0 like 0 dislike
answered by (2k points)  
 
Best answer

Maximum length is 12000 characters
Values in VARCHAR columns are variable-length strings. The length can be specified as a value from 0 to 255 before MySQL 5.0.3, and 0 to 65,535 in 5.0.3 and later versions. The effective maximum length of a VARCHAR in MySQL 5.0.3 and later is subject to the maximum row size (65,535 bytes, which is shared among all columns) and the character set used.

What version of MySQL do I have?
select version() as myVersion

It is valid since version 5.0.3, when they changed the maximum length from 255 to 65535.
VARCHAR(65536) too, but it implicitly promotes the column to MEDIUMTEXT
CHAR has always been 255 max.

Example
CREATE TABLE t2
       (c1 VARCHAR(65535) NOT NULL)
       ENGINE = InnoDB CHARACTER SET latin1;
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
# At database:
ALTER TABLE `q2a_posts` CHANGE `content` `content` VARCHAR(20000) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;
# At application config :
define('QA_DB_MAX_CONTENT_LENGTH',20000);

Just one more example:
#Maximum data what we can store in varchar in mysql was 255. But after Mysql version 5.0.3 varchar can store maximum of 65,535 characters.
#But again this limit is having limitation of maximum row size which is 65535 bytes. It means including all columns it should be less than 65,535 bytes.
#Lets take an example as below:
I have created below table with two columns as “f1” varchar with the length of 32,765 and “f2” with 32766. Total length will be 32765+2(for storing length) + 32766 + 2 = 65535.

CREATE TABLE IF NOT EXISTS `test_table` (
  `f1` varchar(32765) NOT NULL,
  `f2` varchar(32766) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

#But when I create increase the column length with a single character as below:

CREATE TABLE IF NOT EXISTS `test_table2` (
  `f1` varchar(32766) NOT NULL,   //INCREASED LENGTH BY 1
  `f2` varchar(32766) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

#it will give me error.

#1118 - Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs

 

Related questions

0 like 0 dislike
0 answers
0 like 0 dislike
1 answer
asked Mar 31, 2019 in MySQL Database Forum by nijamutheen j (13.4k points)  
0 like 0 dislike
2 answers
...