mysqltypes

MySQL fixed-length and variable-length data types

I am optimizing DB and there is something which is not clear to me about fixed-length data types.

As far as I know these data types are fixed-length:

And these are variable-length:

Please correct me if I am wrong!

Now, my question is: Are these data types fixed-length or variable-length?

Thanks.

Solution

  • You are not correct on some datatypes.

    DATETIME/TIMESTAMP

    TIMESTAMP and DATETIME are similar, however TIMESTAMP uses only half the storage space when compared to DATETIME. There are however notable disadvantages to using TIMESTAMP so you should check the official docs to see which one you need. But most of the the DATETIME would be the better choice.

    You don't have to worry about fix/variable lengths for this datatype

    NUMBERS

    You are able to store integer by setting a length i.e int(20) but in terms of storage it has no difference and hence has no meaning.

    TINYINT SMALL INT MEDIUMINT BIGINT

    These datatypes require 8, 16, 24, 32, and 64 bits of storage space respectively. Each of them has different maximum and minimum values. If you are sure your values will not exceed a certain number, you should choose the smallest int datatype to conserve space and improve performance. You should consult the official docs for more information on the figures.

    FLOAT and DOUBLE are approximate calculations and you don't have to specify a precision. Setting a precision and inserting a value beyond the specification, your value will be rounded to the specification. Again see the official docs for this.

    VARCHAR

    VARCHAR, as you expect is variable-length, but keep in mind, don't specify something like VARCHAR(100) when you only need 5 characters, because although the length is variable when stored on disk, it takes the length you specify when working with RAM, so you will be wasting precious memory.

    BLOB and TEXT

    BLOB and TEXT are stored in a specially way, depending on the storage engine you use. They are usually stored in an "external" area. You should avoid using this unless if the string is VERY long. This datatype cannot index the full length and also is not supported by the Memory and so is not used by the system's memory. When calling for it a temp table will be created on disk with the MyISAM storage engine and this would cause a huge performance degradation.

    ENUM

    This is very compact and you set specific values to a field when you create a table. The strings are fixed and changing an ENUM column requires an ALTER TABLE, so you won't have to worry about fix/variable lengths for this one.

    I found a page in the official docs that answers all your questions Data Type Storage Requirements