Hi All,
Just wondering what happens if you left the length/value field of something like the TEXT data type empty when creating table columns in MySQL? Is there a default length? It would only make sense that it would be a requirement for enum columns, but what about the others like TEXT, VARCHAR, etc.
Im storing ingredients for a recipe in a table so I need a column appropriate for storing something like: “Boneless, skinless chicken breasts, pounded thin”
Thanks!
You haven’t read the documenation, have you? 
Every database has its own unique set of types of fields. Sometimes the names of the types match and somtimes they don’t.
In MySQL, you have various types of text types
CHAR(m) m is the number of bytes you give it when you create it (byte=character in this case). Max 255
BINARY(M) m is the number of bytes for the binary string. Max 255
VARCHAR(M), VARBINARY(M), Again, m is the number of bytes. The difference here is that these types only take the amount of space the need. They are VARiable.
They take length_of_string +1 if the string is less than 255 characters, or length_of_string +2 if it is longer than 255
TINYBLOB, TINYTEXT. They will take length_of_string+1 as long as is under 2 powered 8
BLOB, TEXT They will take length_of_string+2 as long as is under 2 powered 16
MEDIUMBLOB, MEDIUMTEXT They will take length_of_string+3 as long as is under 2 powered 24
LONGBLOB, LONGTEXT They will take length_of_string+4 as long as is under 2 powered 32
ENUM(‘value1’, ‘value2’…) 1 or 2 bytes, depending on the number of enumeration value
SET(‘value1’,‘value2’…) 1, 2, 3, 4, or 8 bytes, depending on the number of set members
As you can see, you’ll do fine with VARCHAR or CHAR just for the ingredients.
May want something like TEXT for the recipe itself, which will give you a max of 65,536 bytes. That’s the equivalent of a couple of pages.
But, if uncertain, go for MEDIUMTEXT
You can always test with the longest recipe that you can find 
No trust me, I’v read the documentation on MySQL’s site but I guess I just couldnt find the DEFAULT values for the ‘length/value’ field. I found all the info about all the differences for the various data types, available memory, and MAX values for that field right away. Thanks a lot for all this info
There’s not default, really.
In the case of CHAR fields, you set it up when you create it.
For the rest of fields, it is variable with those maximums. So you really don’t care about the default. The field will take as much as it needs till it reaches maximum capacity.
You do need to be careful with your encoding. When I made the analogy of 1 byte is likely to be 1 character, it is because most character encoding only need 1 byte storage. But for UTF-8, as an example, it is not true because it requires 2 bytes of storage for each character
You may find this information useful http://stackoverflow.com/questions/7755629/varchar255-vs-tinytext-tinyblob-and-varchar65535-vs-blob-text
This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.