Why do integer DEFAULT values get quotes?

Question

Simple test table, created on MySQL 5.6.23:

CREATE TABLE `test` (
  `tinyint` tinyint(1) NOT NULL DEFAULT 0,
  `int` int(11) NOT NULL DEFAULT 0
);

Note how the DEFAULT values are written: literally 0. As soon as it's created, I do:

SHOW CREATE TABLE `test`;

and get:

CREATE TABLE `test` (
  `tinyint` tinyint(1) NOT NULL DEFAULT '0',
  `int` int(11) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=latin1

The DEFAULT values have gained quotes: '0'!

Why does that happen? More importantly, was I supposed to be surrounding numeric default values with quotes this whole time?

Answer 1

Of course, SHOW CREATE TABLE does not retrieve the original CREATE TABLE code. Even if it was actually stored somewhere, it wouldn't necessarily reflect the current table definition (you can ALTER TABLE afterwards as much as you want). That code needs to be recreated on demand. MySQL Server is mostly written in C with some bits of C++. I can't tell it for sure (I'm just speculating because I know very little C and I'm not familiar with MySQL codebase) but the function that does so could be this:

int store_create_info(THD *thd, TABLE_LIST *table_list, String *packet,
                      HA_CREATE_INFO *create_info_arg, bool show_database)

... which calls this other one:

static bool print_default_clause(THD *thd, Field *field, String *def_value,
                                 bool quoted)

If you check the source code you'll realise it's a long tedious string concatenation that spans across hundreds of lines. (C is lightning fast because it's closer to the machine than modern high level languages, but that comes at the price of being less suitable for quick development.)

There's no need to quote a number. But whoever wrote this code generator years ago didn't care about this detail. And MySQL will just cast strings to numbers automatically whenever it needs to do so, with better or worse results:

mysql> SELECT 1+99, '1'+99, '1x' + '99x', '1' + 'x99';
+------+--------+--------------+-------------+
| 1+99 | '1'+99 | '1x' + '99x' | '1' + 'x99' |
+------+--------+--------------+-------------+
|  100 |    100 |          100 |           1 |
+------+--------+--------------+-------------+
1 row in set, 3 warnings (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+-----------------------------------------+
| Level   | Code | Message                                 |
+---------+------+-----------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: '99x' |
| Warning | 1292 | Truncated incorrect DOUBLE value: '1x'  |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'x99' |
+---------+------+-----------------------------------------+
3 rows in set (0.00 sec)

Since the quoted number comes from the actual default value of an actual column, it's never going to be an invalid number (you couldn't have created the table otherwise). So it's not a big deal.

To sum up: that code is just a template someone wrote years ago, it's not meant to be a style guide or a coding standard.

Answer 2

It's likely just the way the SHOW CREATE TABLE syntax is using it. I was able to submit it using both methods and it worked for me.

/*[2:40:11 PM][16 ms]*/ CREATE TABLE `test` ( `tinyint` TINYINT(1) NOT NULL DEFAULT 0, `int` INT(11) NOT NULL DEFAULT 0 ) ENGINE=INNODB DEFAULT CHARSET=latin1; 
/*[2:40:22 PM][30 ms]*/ CREATE TABLE `test2` ( `tinyint` TINYINT(1) NOT NULL DEFAULT '0', `int` INT(11) NOT NULL DEFAULT '0' ) ENGINE=INNODB DEFAULT CHARSET=latin1;