Is it logically correct to have an integer column of length 1 in SQL if we don't need leading z-CodePudding

Let's say we have a simple table of 2 columns with two scenarios.

With ID column of length 10

CREATE TABLE `members` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
)

With ID column of length 1

CREATE TABLE `members` (
      `id` int(1) unsigned NOT NULL AUTO_INCREMENT,
      `name` varchar(50) NOT NULL,
    )

Can I define the length of column ID by 1 if I don't need the leading zeros in the ID column? I don't have an idea where the leading zeros are helpful and what are the cons if I define the length of the integer column by 1 if there is no need for leading zeros.

CodePudding user response：

The short answer is: You can set the zerofill length to 1 if you absolutely have no use of it. However, sometimes you may want your numbers to be neatly aligned . e.g Instead of displaying ID values from 1 all the way up to 9999, defining an int(4) zerofill makes the values show up from 0001 to 9999. That could be helpful in certain formal scenarios.

CodePudding user response：

If you don't need leading zeroes, then simply do not use the ZEROFILL column option, which I see is already absent from your create table statement.

The "length" of an integer is meaningless except for display. It does not affect the storage size of the integer, or the maximum value that can be stored in it. The length is optional, and I always omit it.

Example:

CREATE TABLE `members` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  ...

This has been the source of confusion for MySQL users for many years. Finally in MySQL 8.0, they stopped outputting this misleading syntax when you use SHOW CREATE TABLE. Use of the integer length is discouraged.