MySQL Forums
Forum List  »  General

Re: How to determine a binary column's default value
Posted by: Peter Brawley
Date: August 08, 2021 07:22AM

Well, it's a dog's breakfast ...

-- 5.7, X operator
drop table if exists binary_test;
CREATE TABLE `binary_test` (
`col` varbinary(100) DEFAULT X'aaaa00aaaaaaaaffffff'
) ENGINE=InnoDB;
show columns from binary_test;
+-------+----------------+------+-----+---------+-------+
| Field | Type           | Null | Key | Default | Extra |
+-------+----------------+------+-----+---------+-------+
| col   | varbinary(100) | YES  |     |         |       |
+-------+----------------+------+-----+---------+-------+

-- 8.0, X operator
drop table if exists binary_test;
CREATE TABLE `binary_test` (
`col` varbinary(100) DEFAULT X'aaaa00aaaaaaaaffffff'
) ENGINE=InnoDB;
show columns from binary_test;
+-------+----------------+------+-----+---------+-------+
| Field | Type           | Null | Key | Default | Extra |
+-------+----------------+------+-----+---------+-------+
| col   | varbinary(100) | YES  |     | 0xAAAA  |       |
+-------+----------------+------+-----+---------+-------+

-- 5.7, no X operator:
drop table binary_test;
CREATE TABLE `binary_test` (
`col` varbinary(100) DEFAULT 'ªª ªªªªÿÿÿ'
) ENGINE=InnoDB;
show columns from binary_test;
+-------+----------------+------+-----+---------------------+-------+
| Field | Type           | Null | Key | Default             | Extra |
+-------+----------------+------+-----+---------------------+-------+
| col   | varbinary(100) | YES  |     | ªª ªªªªÿÿÿ          |       |
+-------+----------------+------+-----+---------------------+-------+

-- 8.0, no X operator:
drop table binary_test;
CREATE TABLE `binary_test` (
`col` varbinary(100) DEFAULT 'ªª ªªªªÿÿÿ'
) ENGINE=InnoDB;
show columns from binary_test;
+-------+----------------+------+-----+------------------------------------------+-------+
| Field | Type           | Null | Key | Default                                  | Extra |
+-------+----------------+------+-----+------------------------------------------+-------+
| col   | varbinary(100) | YES  |     | 0xC2AAC2AA20C2AAC2AAC2AAC2AAC3BFC3BFC3BF |       |
+-------+----------------+------+-----+------------------------------------------+-------+

The inconsistencies might get an informative response from bugs.mysql.com, meanwhile the moral of the story seems to be, don't define default values with X'...'.

Options: ReplyQuote


Subject
Written By
Posted
Re: How to determine a binary column's default value
August 08, 2021 07:22AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.