CODEWITHSUNDEEP
mysqlsqldatabasedatabase-design
Parijat Kalia
Parijat Kalia

Reputation: 5095

Specifying lengths of datatypes

Here is something that troubles me as I am creating a database table columns. For each of these there is a data type which has it's length. For e.g say one of the tables is a file path, and I assume this file path to be not longer than 100 in length at max, obviously i specify this as

filepath Varchar(100)

However, this still takes the same amount of memory space as say varchar(255) which is 1 byte. Given this, what is the benefit of me specifying the length as 100. Taking an outlier example, if my filepath exceeds varchar(100), does the database reject/trim down the filepath value to fit it to 100? Or does it allow it to exceed beyond 100 since the allotted memory space is still around 1 byte?

Essentially the above explanation frames my question as should one try and be very specific about the expected maximum length for a table column? Or just play it safe and specify the upper limit of the expected length of the table column depending on the memory requirement ?

Thanks much !

Parijat

Upvotes: 1

Views: 124

Answers (2)

Kce
Kce

Reputation: 91

very specific about the expected maximum length for a table column? Or just play it safe

If one would make a table containing addresses, you undoubtedly know that there will be some kind of limit to the length of the address. It would be useless to allow longer fields in the database.

You should play it safe, and be very careful.

Upvotes: 1

Marc B
Marc B

Reputation: 360812

MySQL will auto-truncate the value down to 100 characters. The number in the brackets for text/char fields is the MAXIMUM length. Note that this is a CHARACTER limit. If you've got a multibyte collation on that field, you can store more than 100 bytes in the field, but only 100 characters worth of text.

This is different than saying int(10), where the bracketed number is for display purposes only. An int is an int internally and takes up 16bits, regardless of how many digits you allow with the (#), but you'll never SEE more than those # digits.

Upvotes: 1

Related Questions