MySQL and char field = 2048 maximum

[Gordon - Phosphor Media]

I am creating a table in MySQL for use in a Merchant module. The module is MySQL only.

I have a character field that needs to be 2048 characters maximum. If I was to do this to be compatible with MivaSQL, I believe I would need to make it a MEDIUMTEXT (MEMO) field.

Can I use VARCHAR(2048) (knowing it won't be MivaSQL compatible) or is there some gotcha I'm not aware of?

[RayYates]

Re: MySQL and char field = 2048 maximum

If your version of MySQL is 5.0.3 or later you can use VARCHAR up to 65535 characters long. When the length is under 254 characters it has a 1 byte length prefix for each record, 2 bytes if longer. So empty records take up 1 byte of space.


http://dev.mysql.com/doc/refman/5.0/en/char.html


Contrast with MEDIUMTEXT which uses a 3 byte prefix allowing very large data sets with empty records taking up a bit more space.


So in theory, VARCHAR might be somewhat faster. Since this is a dedicated MySQL database I don't see any downside.

[Kent Multer]

Re: MySQL and char field = 2048 maximum

Wow, Ray, you really know your MySQL! Where do you learn internal details like that?

I'd like to learn more about MySQL myself; it's an impressive piece of software. When I'm writing a complex query, I sometimes think about what a nightmare it would have been to do the same thing with the old DBF files ...

Thanks --

[Gordon - Phosphor Media]

Re: MySQL and char field = 2048 maximum

Thanks. Using VARCHAR(2048).

[RayYates]

Re: MySQL and char field = 2048 maximum

Wow, Ray, you really know your MySQL! Where do you learn internal details like that?

I'd like to learn more about MySQL myself; it's an impressive piece of software. When I'm writing a complex query, I sometimes think about what a nightmare it would have been to do the same thing with the old DBF files ...

Thanks --

Mostly from playing around with the Sherlock module. I also post on Experts Exchange when I get stuck, and of course by searching the docs. http://dev.mysql.com/doc/

Most of the internal SQL statements in Toolbelt were originally created and tested with Sherlock. For example the Search_Product command is able to search custom product fields. This is done with a single SQL statement that acts like a MSAccess Pivot Table.