Search code examples
mysqlsqljsoncreate-tablealter-table

Mysql set default value to a json type column

I heard that mysql version prior to 8.0.13 accept default value for json type column, so I using the cmd:

ALTER TABLE templates CHANGE COLUMN values JSON NOT NULL DEFAULT '{}' ;

but receive error:

Error Code: 1101. BLOB, TEXT, GEOMETRY or JSON column 'values' can't have a default value

So how do I fix it?

I'm using mysql version 8.0.19 and client tool Workbench

Solution

  • From version 8.0.13 onwards, the documentation says (emphasis is mine):

    The BLOB, TEXT, GEOMETRY, and JSON data types can be assigned a default value only if the value is written as an expression, even if the expression value is a literal.

    You can make your default an expression by surrounding the literal value with parentheses:

    ALTER TABLE templates CHANGE COLUMN values JSON NOT NULL DEFAULT ('{}') ;

    Or:

    ALTER TABLE templates CHANGE COLUMN values JSON NOT NULL DEFAULT (JSON_OBJECT()) ;

    Prior to version 8.0.13 of MySQL, it was not possible to set a default value on a JSON column, as the 8.0 documentation points out a few paragraphs later :

    The BLOB, TEXT, GEOMETRY, and JSON data types cannot be assigned a default value.