Why does VARCHAR need length specification?

问题:

Why do we always need to specify VARCHAR(length) instead of just VARCHAR? It is dynamic anyway.

UPD: I'm puzzled specifically by the fact that it is mandatory (e.g. in MySQL).

回答1:

The "length" of the VARCHAR is not the length of the contents, it is the maximum length of the contents.

The max length of a VARCHAR is not dynamic, it is fixed and therefore has to be specified.

If you don't want to define a maximum size for it then use VARCHAR(MAX).

回答2:

First off, it does not needed it in all databases. Look at SQL Server, where it is optional.

Regardless, it defines a maximum size for the content of the field. Not a bad thing in itself, and it conveys meaning (for example - phone numbers, where you do not want international numbers in the field).

回答3:

You can see it as a constraint on your data. It ensures that you don't store data that violates your constraint. It is conceptionally similar to e.g. a check constraint on a integer column that ensure that only positive values are entered.

回答4:

There's possible performance impact: in MySQL, temporary tables and MEMORY tables store a VARCHAR column as a fixed-length column, padded out to its maximum length.

If you design VARCHAR columns much larger than the greatest size you need, you will consume more memory than you have to. This affects cache efficiency, sorting speed, etc.

So you give the max length which is under your string come. like if you max length of character 10 so don't give his length 100 or more.

回答5:

The more the database knows about the data it is storing, the more optimisations it can make when searching/adding/updating data with requests.

回答6:

The answer is you don't need to, it's optional.

It's there if you want to ensure that strings do not exceed a certain length.

回答7:

From Wikipedia:

Varchar fields can be of any size up to the limit. The limit differs from types of databases, an Oracle 9i Database has a limit of 4000 bytes, a MySQL Database has a limit of 65,535 bytes (for the entire row) and Microsoft SQL Server 2005 8000 bytes (unless varchar(max) is used, which has a maximum storage capacity of 2,147,483,648 bytes).