MySql 8 | MySql 8.1 | MySql 8.2 why fields type of int have no length

by

I upgrade the MySQL from 5.7 or 5.4 to MySQL 8 and imported all old databased into new MySQL 8 but int length is missing, why?

Incorrect integer value

int showing no length in MySQL 8

asdf

schema on the MySQL 8:

mysql> desc user;
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int unsigned | NO   | PRI | NULL    | auto_increment |
| key        | varchar(255) | YES  |     | NULL    |                |
| remark     | varchar(255) | YES  |     | NULL    |                |
| created_at | int          | YES  |     | NULL    |                |
| updated_at | int          | YES  |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

schema on the MySQL 5.7 or MySQL 5.4:

mysql> desc user;
+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| id         | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
| key        | varchar(255)     | YES  |     | NULL    |                |
| remark     | varchar(255)     | YES  |     | NULL    |                |
| created_at | int(11)          | YES  |     | NULL    |                |
| updated_at | int(11)          | YES  |     | NULL    |                |
+------------+------------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)

As mysql manual says on integer data type properties:

As of MySQL 8.0.17, the ZEROFILL attribute is deprecated for numeric data types, as is the display width attribute for integer data types. Support for ZEROFILL and display widths for integer data types will be removed in a future MySQL version.

Length property for integers did not restrict the value you couod store in an integer field anyway, it was used for display purposes only.

means you are setting priority to a string, not an integer. You should use NULL (without quotes) instead, or, to set it to the default 0, leave priority out of the query altogether.

If you are unable to make these changes and can only make changes in MariaDB, not the code, then you have two options.

You can either change the column type to a string, though be aware that this may have consequences elsewhere in the code.

Alternatively, you can disable strict mode, which will accept the query as is, with a warning, and replace the string with the default, 0. Again, this change may have consequences elsewhere in the code.

Leave a Comment