MySQL: field length. Does it really matter?

Question

I work with some levels of database abstraction, and most of them use attributes such as "String", which is a VARCHAR 250 or INTEGER, which is 11 digits long. But, for example, I have something that will be less than 250 characters long. Should I go and do less? Does it really matter?

Thanks in advance!

Answer 1

An INT length does nothing. All INTs are 4 bytes. The number you can set is used only for zerofill (and who uses this !?).

VARCHAR is longer. This is the maximum field length. VARCHAR is saved so that only the actual data is saved, so the length is not matte. These days, you can have larger VARCHARs than 255 bytes (256 × 2-1). The difference is in bytes, which are used for the length of the field. VARCHAR (100) and VARCHAR (8) and VARCHAR (255) use 1 byte to keep the field length. VARCHAR (1000) uses 2.

Hope that helps =)

change
I almost always make my VARCHARs 250 long. The actual length should be checked in the app anyway. For large fields, I use TEXT (and they are stored in different ways, so they can be significantly increased).

change
I do not know how relevant this is, but it helped me (understand): http://help.scibit.com/Mascon/masconMySQL_Field_Types.html

Answer 2

Not so sure about MySQL, but in MS SQL it matters only for large enough databases. As a rule, I like to use smaller fields for: a) saving space (it is never harmful to practice good habits) and b) for implied verification (if you know that some field should be no more than 10 characters, why eleven , let only 250?).

Answer 3

First, remember that the database is designed to store facts and is designed to protect against bad data. Thus, the reason you do not want the user to enter 250 characters for the name is because the user will put all kinds of data there that are not the first name. They will put all their name, the size of their underwear, a novel about what they did last summer and so on. Thus, you want to strive to ensure that the data is as correct as possible. It is a mistake to assume that the application is the only protector against bad data. You want users to inform you that they had a problem with overlaying “War on Peace” in this column.

Thus, the most important question is: "What is the most appropriate value of the stored data?" Ideally, you should use int and a check constraint to ensure that the values ​​have an appropriate range (e.g., greater than zero, less than a billion, etc.). Unfortunately, this is one of the weaknesses of MySQL: it does not comply with control restrictions. It just means that you have to integrate these integrity checks into triggers, which admittedly are more cumbersome.

Will the difference between int (4 bytes) be a noticeable difference from tinyint (1 byte)? Obviously, this depends on the amount of data. If you have no more than 10 lines, the answer will obviously not be. If you have 10 billion lines, the answer is obviously yes. However, IMO is premature optimization. It is much better to focus on ensuring correctness first.

In the text, you should ask if your data should support Chinese, Japanese or non-ANSI values ​​(i.e. use nvarchar or varchar)? Does this value mean a real world code, for example, a currency code or a bank code that has a specific specification?

Answer 4

I think Rudie is wrong, not all INTs are 4 bytes ... in MySQL you have:

tinyint = 1 byte, smallint = 2 bytes, mediumint = 3 bytes, int = 4 bytes, bigint = 8 bytes.

I think Rudie refers to "display with" - this is the number you put between parentheses when you create a column, for example:

age INT (3)

You tell RDBMS to only SHOW no more than three numbers.

And VARCHAR is a variable length charcter string, so if you declare let say say varchar (5000) and you save a name like "Mario", you only use 7 bytes (5 for data and 2 for length value).

Answer 5

The correct field size is used to limit the bad data that may be entered. For example, suppose you have a phone number. If you have made 250 characters, things like the following often happen in the phone’s field: (//)>

 Call the good-looking blonde secretary instead. 

So, the first length restriction is part of how we apply data integrity rules. In fact, this is very important.

Secondly, there is so much space in datapage, and while some databases allow you to create tables where the potential record is longer than the width of the data page, they often will not allow you to actually exceed it when storing data. This can make it very difficult to find errors when a single record cannot be saved. I do not know about MySql and whether it does it, but I know what SQL Server does, and it is very difficult to understand what is wrong. Thus, when creating data, the correct size can be critical to prevent errors.