Hi guys.

There is something that is really unclear in a php book I have been learning from.

I have been learning about the different data type that you can use in mysql (such as CHAR, VARCHAR, INT, TINYINT,, etc, etc).

The one thing that has confused the hell out of me is the following paragraph:

With the character types — CHAR , VARCHAR , TEXT , and so on — the amount you can store may be less
than the maximum shown, depending on the character set used. For example, the UTF - 8 (Unicode)
character set commonly uses up to 3 bytes per character, so a VARCHAR field may only be able to store up
to 21,844 UTF - 8 characters.

Now taking this paragraph into account, it sounds like I should treat each of the data types (in mysql) as only being able to hold one 3rd of what it states in the book I am reading.

For example, it states in my book that 'CHAR' holds between 0-255 characters. But taking the above extract (paragraph) into account, should I treat it as 0-85 characters instead (to be on the safe side)?

Paul.

    I had a look at what the MySQL manual says: http://dev.mysql.com/doc/refman/5.7/en/char.html
    The storage requirements are described on:
    http://dev.mysql.com/doc/refman/5.7/en/storage-requirements.html#idm140597658501072

    If I understand it (hah) then:

    The value used for m in CHAR(m) and VARCHAR(m) is the number of characters stored; depending on the character set, the number of bytes needed to store that many characters may be different (a CHAR(255) field using a UTF-8 character set will fit 255 UTF-8 characters and take up 765 bytes in the row). VARCHAR is limited by the fact that a row can't contain more than 65536 bytes - at which point you should be asking yourself if you're using the right type for the purpose.

    The type system is one of the weakest things about MySQL.

      So I guess a simple rule would be (for example) if I need 60000 characters, I am better off using MEDIUMTEXT instead of TEXT?

      At least this way I will know for sure that there won't be any problems storing the amount of data I want to store?

        I guess so. The DBMS I use doesn't have "tinytext", "mediumtext", or "longtext"; it just has "text" with a capacity of 1GB. So the issue doesn't come up for me.

          Write a Reply...