Re: How to determine a binary column's default value

[Peter Brawley]

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'...'.