Member Avatar for freedompeace

Erm, well I'm having heaps of trouble telling the difference between what the bracket value bracket thingies do... [like tinyint(1), tinyint(2), tinyint(3)]

I've read this, but I can't seem to understand it >.<

Another extension is supported by MySQL for optionally specifying the display width of integer data types in parentheses following the base keyword for the type (for example, INT(4)). 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 the number of digits that are displayed for values having a width exceeding that specified for the column. 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 characters are displayed using more than three characters.

Please help me ):

  • 3 Contributors
  • 6 Replies
  • 359 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by drjohn
Member Avatar for rch1231

It is defined as the number spaces (characters)to be used in the output and has nothing to do with the size of the number to be stored.

TINYINT(m)                         8-bit integer (1 byte, -128 to +127); the optional value m gives the desired
                                   column width in SELECT results (Maximum Display Width), but has no
                                   influence on the permitted range of numbers.
SMALLINT(m)                        16-bit integer (2 bytes, -32,768 to + 32,767)
MEDIUMINT(m)                       24-bit integer (3 bytes, -8,388,608 to +8,388,607)
INT(m), INTEGER(m)                 32-bit integer (4 bytes, -2.147,483,648 to +2,147,483,647)
                                   64-bit integer (8 bytes, ±9.22*1018)
BIGINT(m)
SERIAL                             Synonym for BIGINT AUTO_INCREMENT NOT NULL PRIMARY KEY

Optionally, in the definition of an integer field, the desired column width (number of digits) can be specified, such as, for example, INT(4). This parameter is called M (for Maximum Display Size) in the literature. It assists MySQL as well as various user interfaces in presenting query results
in a readable format.

■ Note Note that with the INT data types, the M restricts neither the allowable range of numbers nor the possible number of digits. In spite of setting INT(4), for example, you can still store numbers greater than 9999. However, in certain rare cases (such as in complex queries for the evaluation of which MySQL constructs a temporary table), the numerical values in the temporary tables can be truncated, with incorrect results as a consequence.

Edited by rch1231 because: n/a
Member Avatar for freedompeace

Umm, so, if I had say, the values "1", "11" and "200" stored into a column of value type tinyint(2), what would my output be when I retrieved them from the database? /:

Member Avatar for rch1231

From what I have seen it depends on how you referred to the data. If a temp table is created then it is possible your data would be truncated. Basically the number is stored correctly but may display wrong. Just make sure you provide a large enough area to display your worst case or largest possible number.

Member Avatar for freedompeace

From what I have seen it depends on how you referred to the data. If a temp table is created then it is possible your data would be truncated. Basically the number is stored correctly but may display wrong. Just make sure you provide a large enough area to display your worst case or largest possible number.

What about with a standard, non-temporary table?

Member Avatar for rch1231

I never tried using a value too small. Give it a shot is all I can suggest. The documentation on MySQL states it can give unpredictable results so why take the chance.

Member Avatar for drjohn

Why are you wasting your time with tinyint, which as pointed out, has a max value of 127??? Just use int.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.