WL#13528: Remove integer display width from SHOW CREATE output

Affects: Server-8.0   —   Status: Complete 

In WL#13127, the display width attribute was deprecated for integer types.
Currently SHOW CREATE statements still always uses this deprecated attribute. 
This means that we generate statements that we ourselves have deprecated.

This WL is about changing SHOW CREATE to not output integer display width unless 
ZEROFILL is also used. Without ZEROFILL, integer display width has no effect.

Example from 8.0.19:
  mysql> create table t1(a int);
  Query OK, 0 rows affected (0,02 sec)

  mysql> show create table t1;
  +-------+--------------------------------------------------------------------- 
  | Table | Create Table                                                                                                      
  |
  +-------+---------------------------------------------------------------------
  | t1    | CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
  +-------+--------------------------------------------------------------------- 
  1 row in set (0,01 sec)

Notice that int(11) is part of SHOW CREATE output.

We should only omit integer display width in SHOW CREATE if ZEROFILL is not also 
given as display width + ZEROFILL has consequences for current behavior.

The example below illustrates the current 5.7 behavior:
  mysql> create table t1(a int(4));
  Query OK, 0 rows affected (0,01 sec)

  mysql> insert into t1 values (1),(10),(100),(1000),(10000),(100000);
  Query OK, 6 rows affected (0,01 sec)
  Records: 6  Duplicates: 0  Warnings: 0

  mysql> select * from t1;
  +--------+
  | a      |
  +--------+
  |      1 |
  |     10 |
  |    100 |
  |   1000 |
  |  10000 |
  | 100000 |
  +--------+
  6 rows in set (0,00 sec)

Notice that the output above for int(4) is no different from int.

  mysql> create table t2(a int(4) zerofill);
  Query OK, 0 rows affected (0,00 sec)

  mysql> insert into t2 values (1),(10),(100),(1000),(10000),(100000);
  Query OK, 6 rows affected (0,01 sec)
  Records: 6  Duplicates: 0  Warnings: 0

  mysql> select * from t2;
  +--------+
  | a      |
  +--------+
  |   0001 |
  |   0010 |
  |   0100 |
  |   1000 |
  |  10000 |
  | 100000 |
  +--------+
  6 rows in set (0,01 sec)

Notice that the output above for int(4) + ZEROFILL is different from int.


Copyright (c) 2000, 2025, Oracle Corporation and/or its affiliates. All rights reserved.