Warning Code : 1681 UNSIGNED for decimal and floating point data types is deprecated and support for-CodePudding

Today I've created a sql backup of a table using SQLyog Community Edition. Then I tried to run the created query against an up to date MySQL 8 managed database on Digital Ocean.

The query:

CREATE TABLE tblUsers (
  id bigint unsigned NOT NULL AUTO_INCREMENT,
  subscriberId bigint unsigned DEFAULT NULL,
  user varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  password varchar(64) DEFAULT NULL,
  created timestamp NULL DEFAULT NULL,
  modified timestamp NULL DEFAULT NULL,
  status varchar(16) DEFAULT NULL,
  privilege varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  firstName varchar(64) DEFAULT NULL,
  lastName varchar(64) DEFAULT NULL,
  address1 varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  city varchar(32) DEFAULT NULL,
  state char(2) DEFAULT NULL,
  postalCode char(6) DEFAULT NULL,
  phone varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  fax varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  email varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  notes varchar(1024) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  payRate varchar(16) DEFAULT NULL,
  pay decimal(8,2) unsigned DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

The warning:

Warning Code : 1681
UNSIGNED for decimal and floating point data types is deprecated and support 
for it will be removed in a future release.

The research:

https://dev.mysql.com/doc/refman/8.0/en/numeric-type-attributes.html

As of MySQL 8.0.17, AUTO_INCREMENT support is deprecated for FLOAT and DOUBLE columns; you should expect it to be removed in a future version of MySQL. Consider removing the AUTO_INCREMENT attribute from such columns, or convert them to an integer type.

The question:

I'm not certain how to interpret what I'm reading. Is it really telling me to remove my primary key auto increments? Or is it telling me the pay field should no longer be define the way you see above? Or that AUTO_INCREMENT cannot exist together with unsigned?

CodePudding user response：

This warning is not about your auto-increment column id. It's about the decimal column pay.

Change this:

pay decimal(8,2) unsigned DEFAULT NULL,

To this:

pay decimal(8,2) DEFAULT NULL,

The related excerpt from the manual page you linked to is the following. It tells you what to do if you want to restrict the column to nonnegative values:

As of MySQL 8.0.17, the UNSIGNED attribute is deprecated for columns of type FLOAT, DOUBLE, and DECIMAL (and any synonyms) and you should expect support for it to be removed in a future version of MySQL. Consider using a simple CHECK constraint instead for such columns.

In your case it would look like this:

pay decimal(8,2) DEFAULT NULL,
CHECK (pay >= 0),

Updated example:

id field remains unchanged
subscriberId field loses UNSIGNED and gains CHECK
pay field loses UNSIGNED and gains CHECK

CREATE TABLE tblUsers (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `subscriberId` BIGINT DEFAULT NULL CHECK (subscriberId >= 0),
  `user` VARCHAR(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `password` VARCHAR(64) DEFAULT NULL,
  `created` TIMESTAMP NULL DEFAULT NULL,
  `modified` TIMESTAMP NULL DEFAULT NULL,
  `status` VARCHAR(16) DEFAULT NULL,
  `privilege` VARCHAR(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `firstName` VARCHAR(64) DEFAULT NULL,
  `lastName` VARCHAR(64) DEFAULT NULL,
  `address1` VARCHAR(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `city` VARCHAR(32) DEFAULT NULL,
  `state` CHAR(2) DEFAULT NULL,
  `postalCode` CHAR(6) DEFAULT NULL,
  `phone` VARCHAR(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `fax` VARCHAR(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `email` VARCHAR(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `notes` VARCHAR(1024) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `payRate` VARCHAR(16) DEFAULT NULL,
  `pay` DECIMAL(8,2) DEFAULT NULL CHECK (pay >= 0),
  PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=465000 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;