Why mysql has many different field types?

Question

Since data is categorized as int, decimal, text(string), date, enum, Why mysql still need to categorized them into more groups like text is seperated into char, varchar, text, tinytext, etc? I knew this would make it faster, but what is the relation between categorized into more groups and making it faster? What is the reason?

Sorry for my poor english.

Answer 1

The number of variations for character string types is the sign of progress. In an ideal world, the old types would be removed and the new and improved types would replace them. But, in the real world, legacy compatibility requires fields types to, for example, behave like 1960s COBOL string handling for applications which expect it. So the char(20) type pads with trailing spaces to act like an IBM keypunch card.

Think of the various subtypes as a set of screwdrivers, some small, some big. Each has a narrow range of appropriate use. If you are working on a pocket watch, the big screwdrivers won't be needed. But if the application is bridge building, then the micro precision screwdrivers remain unused and the big ones get used. Databases are a lot like that.

Answer 2

Well the main reason behind is the memory. See the address is a text information, name is a text information, if you want to store any value like yes and no is also a text value, If you want to take the comment or post from any one it is also a text value. All these values needs different memories. When you talk about large system then memory is an important factor. For example if you set text type for all text information it is highly a bad practice. So mysql has classified into different things. Like if you want to store the name you can use varchar(50), if you want to store address you can even use varchar(255). If you want to store the post or comment and description or any thing you can use text. If you want to use yes and no like information you can use char. I hope you understood.