When I create an attribute column with "MEDTEXT" does mySql Reserve 16 chars each row

When I create an attribute column with "MEDTEXT" does mySql Reserve 16 chars for each row?
I am making an "account system" and I have Meta data that can be associated with users.
Some meta data is 1 or 0. But sometimes it can be a lengthy description for the user.
That's a lot of space to reserve for parts of the table that don't need it.

Thank you for the help.

12-24-2012, 08:12 PM

Old Pedant

Depends on how you specify the column.

If you do

Code:

CREATE TABLE yourTableName (
medtext CHAR(16),
...
);

Then MySQL indeed reserves space for exactly 16 characters in every record. And you will get an error (or warning, depending on how you set up MySQL) when you try to put more than 16 characters into the field.

However, if you use

Code:

medtext VARCHAR(16),

Then MySQL will store only as many characters as you actually put into the field. Again, though, 16 will be the maximum allowed. You could do

Code:

medtext VARCHAR(255),

and still MySQL will only need as much space as you actually use.

NOTE: This is not quite true. VARCHAR, because it does use a variable amount of space, incurs OVERHEAD in the form of a hidden length value that remembers how much data is stored. So if you only need to store, say, up to 4 characters, it will be more efficient to use CHAR(4). But a caution: CHAR() of any size *PADS* your text with SPACES up to the given size.

Wow. That was jam packed. Thanks!
I have moved away from meta-data though because what I'm providing is better to be manually configured on that level than some API.
This information is still very useful though, thank you.

12-29-2012, 08:43 PM

Old Pedant

I don't know how you can "move away from metadata." Virtually any system, and especially any software system, has metadata, either explicitly declared and created or at the least implied by its very structure.

Even your manual configuration implies that there is metadata involved. In your head, if no place else.