Why does mysqldump convert default value of a integer column to a string?

The default value for a integer column should be literal 65534. however when the table is dumped using mysqldump, it becomes:

CREATE TABLE `users2` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT 'Primary key',
  `skey` varchar(255) DEFAULT (uuid_short()) COMMENT 'Surrogate key', 
  `user_name` varchar(255) NOT NULL COMMENT 'User display name',
  `phone` varchar(255) DEFAULT NULL COMMENT 'User''s telephone or mobile phone number',
  `created` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Created time',
  `modified` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Modified time',
  `user_type` int NOT NULL DEFAULT '65534' COMMENT 'The user type',
  PRIMARY KEY (`id`),
  UNIQUE KEY `skey` (`skey`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

why does ‘65534’ come with single quotes?

>Solution :

It’s not a problem if the string literal can normally convert to the data type of the column.

In other words, you can use a string literal explicitly like this:

INSERT INTO users2 (user_type) VALUES ('65534');

The integer value 65534 is inserted to the column, because MySQL defines string-to-integer conversion that way.

https://dev.mysql.com/doc/refman/8.0/en/data-type-defaults.html says:

If a default value evaluates to a data type that differs from the declared column type, implicit coercion to the declared type occurs according to the usual MySQL type-conversion rules.

I don’t know exactly why the SHOW CREATE TABLE changes the data type to a string literal, but I can guess it’s because INFORMATION_SCHEMA.COLUMNS (and presumably the data dictionary internally) stores all column defaults as strings.

https://github.com/mysql/mysql-server/blob/8.0/sql/dd/types/column.h#LL219C1-L220C72

  virtual const String_type &default_value() const = 0;
  virtual void set_default_value(const String_type &default_value) = 0;

Leave a Reply