mysqldefault

Have I understood MySQL behavior correctly?

I created the table with such a command CREATE TABLE sometable (number INT DEFAULT (300));

+--------+------+------+-----+---------+-------------------+
| Field  | Type | Null | Key | Default | Extra             |
+--------+------+------+-----+---------+-------------------+
| number | int  | YES  |     | 300     | DEFAULT_GENERATED |
+--------+------+------+-----+---------+-------------------+

And because of expression (300) instead of simple 300 MySQL thinks it is an expression, he calculate it every time when you add default value to the table, so MySQL added a DEFAULT_GENERATED. Right? (When I create the same table but without brackets around of 300, DEFAULT_GENERATED isn't added.)

Solution

  • Yes, you have the correct understanding You can put an expression into the DEFAULT that is only a single literal value. That counts as an expression if you use the syntax cue of the parentheses.

    If you're concerned about the performance overhead of the expression, I would expect that calculating such a simple expression (a single literal) is not likely to be a significant performance problem.

    Keep in mind the DEFAULT expression is used only when you INSERT and furthermore only if you don't provide a value for that column.

    In a later SELECT query, there is no difference. It reads the stored value, whether you originally inserted the row by using DEFAULT or by specifying a value.