What does the number in parenthesis really mean?

mysql sql ddl
luckytaxi picture luckytaxi · Oct 29, 2010 · Viewed 15.5k times · Source

I always thought that the number in the parenthesis represented the field length?

However, I understand that is not always the case. Maybe it's a MySQL issue? Someone told me if I set a field to 9 characters long, I can add a value that's more than 9 characters but only the first 9 will be saved.

Example:

CREATE TABLE `person` (
    id INT,
    age INT(2)
);

If that's the case, shouldn't I select something like TINYINT instead of INT for age?

Answer

Pekka picture Pekka · Oct 29, 2010

INT(2) will generate an INT with the minimum display width of 2:

MySQL supports an extension for optionally specifying the display width of integer data types in parentheses following the base keyword for the type. For example, INT(4) specifies an INT with a display width of four digits. This optional display width may be used by applications to display integer values having a width less than the width specified for the column by left-padding them with spaces. (That is, this width is present in the metadata returned with result sets. Whether it is used or not is up to the application.)

The display width does not constrain the range of values that can be stored in the column. Nor does it prevent values wider than the column display width from being displayed correctly. For example, a column specified as SMALLINT(3) has the usual SMALLINT range of -32768 to 32767, and values outside the range permitted by three digits are displayed in full using more than three digits.

this does not affect the range of possible values that can be stored in the field; neither is it the number of bytes used to store it. It seems to be only a recommendation for applications how to show the value, unless ZEROFILL is used (see the linked page).

A unsigned TINYINT (0...255) would probably do as well, unless cryopreservation takes a big step forward during the lifetime of your application.

Related questions

How can I avoid getting this MySQL error Incorrect column specifier for column COLUMN NAME?

How can I avoid getting this MySQL error Incorrect column specifier for column topic_id ? MySQL Error... #1063 - Incorrect column specifier for column 'topic_id' SQL Schema... CREATE TABLE discussion_topics ( topic_id char(36) NOT NULL AUTO_INCREMENT, project_id …

Read More
ALTER TABLE without locking the table?

When doing an ALTER TABLE statement in MySQL, the whole table is read-locked (allowing concurrent reads, but prohibiting concurrent writes) for the duration of the statement. If it's a big table, INSERT or UPDATE statements could be blocked for a …

Read More
Is there a tool to generate a full database DDL for SQL Server? What about Postgres and MySQL?

Using Toad for Oracle, I can generate full DDL files describing all tables, views, source code (procedures, functions, packages), sequences, and grants of an Oracle schema. A great feature is that it separates each DDL declaration into different files (a …

Read More