2

I use the Navicat MySQL GUI and have noticed there are Length and Decimal settings for all columns:

enter image description here

I understand the how the length and decimals settings work for float and decimal data types, but do they matter for other column types such as int and datetime?

Michael Berkowski
  • 267,341
  • 46
  • 444
  • 390
Nate
  • 26,164
  • 34
  • 130
  • 214
  • You're referring to declarations like `INT(11)`? – Michael Berkowski Jul 05 '14 at 00:44
  • @MichaelBerkowski I guess so. I'm not used to writing table declarations by hand, though, and am used to seeing "Length" and "Decimals" columns in WYSIWYG editors. – Nate Jul 05 '14 at 00:45
  • 1
    Ohhh. You should consult the GUI editor's docs to see how it maps those terms into the different data types, because MySQL doesn't use the term "Decimals". What you should do is play with the values for Length and Decimals on the types in question, then run `SHOW CREATE TABLE tablename` to see what your GUI ultimately constructed when creating the the table. – Michael Berkowski Jul 05 '14 at 00:48
  • 1
    But the `(11)` after `INT(11)` relates to how the value is displayed, not how it is stored. I'm looking for the MySQL docs, as they're not easy to find for that – Michael Berkowski Jul 05 '14 at 00:49
  • 1
    Here's something about the length and zerofill: http://dev.mysql.com/doc/refman/5.5/en/numeric-type-attributes.html – Michael Berkowski Jul 05 '14 at 00:50
  • @MichaelBerkowski Oh wow, `SHOW CREATE TABLE` is super helpful. It looks like those settings don't affect the `datetime` column at all and only the length setting affects the `int` column. Would you like to make your comments an answer? They may help some other newb out :-) – Nate Jul 05 '14 at 00:52
  • On the INT columns, did it map "Length" into the display length like `(11)`? – Michael Berkowski Jul 05 '14 at 00:55
  • @MichaelBerkowski Yes, it shows the int columns as `int(11)`, while the datetime column doesn't have anything following the datatype. – Nate Jul 05 '14 at 00:56
  • 1
    Ok, I'll try to formulate this into an answer. Meanwhile, you should edit your question to make mention of the fact that these are settings you encountered in a GUI editor. – Michael Berkowski Jul 05 '14 at 00:58

2 Answers2

2

MySQL doesn't use the term "Decimals", so that must be a term decided upon in the GUI tool you're using when defining DECIMAL or FLOAT types. For authoritative information, consult the GUI tool's documentation on how these settings affect types they wouldn't normally seem to apply to.

Searching around Navicat's documentation turns up little of any use on how it expects "Length" and "Decimals" to apply to integer and date types:

se the Length edit box to define the length of the field and use Decimals edit box to define the number of digits after the decimal point (the scale) for Floating Point data type.

...so not really helpful.

MySQL has a few options for storage length of integer types (which limit the maximum size of the integer that the column can hold), but those limits are specified by the name of the data type rather than a numeric length specified in the column definition.

This table of INT types explains the numeric ranges possible for each named type.

MySQL also offers an option on the integer types in the form of a display length like INT(11) which affects the displayed value rather than the stored value when using the ZEROFILL option.. Your GUI editor appears to map its "Length" option to the integer zerofill attribute.

See also: What is the benefit of ZEROFILL in MySQL?

How to find out, absent good documentation:

However, when working with a GUI client and not understanding what it is doing under the hood, the best advice I can offer would be to try out different settings and then examine the output of SHOW CREATE TABLE <tablename> to see what DDL statement the GUI ultimately constructed and sent to the RDMBS.

Syntax reference: SHOW CREATE TABLE

Community
  • 1
  • 1
Michael Berkowski
  • 267,341
  • 46
  • 444
  • 390
1

For datetime, it specifies the precision, and is a number up to 6. 3 will give you milliseconds and 6 will give you microseconds (millionth of a second). The length of an INT changes how the value is displayed, but it will still store the same value.

vcapra1
  • 1,988
  • 3
  • 26
  • 45