MySQL adding (num) to smallint column

Question

When I create a table in MySQL specifying smallint as a column, but then use show create table or even mysqldump, MySQL has added (5) after the smallint definition, as below.

I'm guessing it doesn't really matter as far as the data is concerned, but can anyone explain why and if/how I can stop it doing this?

As an aside, I am attempting to change an existing database table to exactly match that of a new sql script. I could always alter the new sql script, but I'd prefer to alter the existing table if possible (think software install versus software upgrade).

DROP TABLE IF EXISTS `test`;

CREATE TABLE `test` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `status` varchar(100) NOT NULL DEFAULT '',
  `port` smallint unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SHOW CREATE TABLE test;

CREATE TABLE `test` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `status` varchar(100) NOT NULL DEFAULT '',
  `port` smallint(5) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

It will do the same thing if you specify bare int instead of int(10) -- add the (10). The parenthetical attribute only has an effect if you use ZEROFILL too, Docs here: dev.mysql.com/doc/refman/5.5/en/numeric-type-attributes.html — Michael Berkowski
– Michael Berkowski, Commented Apr 23, 2015 at 2:54

spencer7593 · Accepted Answer · 2015-04-23 03:01:40Z

2

No, you can't stop the SHOW CREATE TABLE from including the display width attribute for integer types.

If a value for the display width is not included in the column declaration of an integer type, MySQL supplies a default value for it. A value of 5 is the default value for SMALLINT UNSIGNED.

The display width doesn't have any affect on the values that can be stored or retrieved. Client applications can make use of the value for formatting a resultset.

Reference: http://dev.mysql.com/doc/refman/5.6/en/numeric-type-attributes.html

answered Apr 23, 2015 at 3:01

spencer7593

109k15 gold badges122 silver badges148 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mark Unwin Over a year ago

Answer accepted - thanks. Will change my 'new' sql definition to match this.

Pepi · Accepted Answer · 2015-04-23 02:59:28Z

0

tMySQL is simply setting the (displayed) length of the column to match he data type (max value 65535, five digits). To change this, you can write:

port smallint (3) unsigned NOT NULL DEFAULT '0',

if you like.

answered Apr 23, 2015 at 2:59

Pepi

1712 silver badges8 bronze badges

Comments

Madona wambua · Accepted Answer · 2015-04-23 03:11:21Z

0

Try this start adding values in your table.

<mysql> CREATE TABLE test(
->    ID SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
->    Name VARCHAR(100) NOT NULL
-> );>

edited Apr 23, 2015 at 3:11

answered Apr 23, 2015 at 3:00

Madona wambua

1,4281 gold badge21 silver badges37 bronze badges

3 Comments

Michael Berkowski Over a year ago

SHOW CREATE TABLE tabname is valid. dev.mysql.com/doc/refman/5.0/en/show-create-table.html

sayani Over a year ago

@Madonah show create table test; is a valid MySQL statement.

Madona wambua Over a year ago

Oh Waoh i need to check that one out, it always fails. interesting to learn new thing.

Collectives™ on Stack Overflow

MySQL adding (num) to smallint column

3 Answers 3

1 Comment

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related