Search code examples
mysqlnavicat

Do length and decimals settings matter for datetime or int columns?

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?

Solution

  • 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