mysql length limits

I am changing my db so that certain fields can be longer. Right now they're "varchar(255)" can I just increase that to "varchar(500)" or are there special rules for dealing with this and I should make them "text"?

13.10.2009 22:48:24

From the MySQL manual:

"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."

So, yes, you can just extend your columns to be a maximum of 500 characters in length (though, you'll want to be sure your application logic doesn't expect the maximum length to be 255 characters).

Edit: see also cballou's note below about character sets.

13.10.2009 22:56:11
It's worth noting that the maximum size depends on the character set used as well: "The effective maximum number of bytes that can be stored in a VARCHAR or VARBINARY column is subject to the maximum row size of 65,535 bytes, which is shared among all columns. For a VARCHAR column that stores multi-byte characters, the effective maximum number of characters is less. For example, utf8 characters can require up to three bytes per character, so a VARCHAR column that uses the utf8 character set can be declared to be a maximum of 21,844 characters."
Corey Ballou 13.10.2009 22:53:08

Modifying to a longer varchar would be fine:

ALTER TABLE my_table MODIFY column_name VARCHAR(500);
13.10.2009 22:52:07