Menu
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
817
A column's default value is part of its definition, but can be modified separately from other aspects of the definition. To change a default value, use ALTER col_name SET DEFAULT : ALTER TABLE mytbl ALTER j SET DEFAULT 1000; Default values must be constants.
In MySQL, the only way to index a JSON path expression is to add a virtual column that mirrors the path expression in question and build an index on the virtual column. As you can see, the title column is mapped to the $. title path expression on the properties JSON column.
The drawback? If your JSON has multiple fields with the same key, only one of them, the last one, will be retained. The other drawback is that MySQL doesn't support indexing JSON columns, which means that searching through your JSON documents could result in a full table scan.
In MySQL, the JSON_SET() function inserts or updates values in a JSON document and returns the result. You provide the JSON document as the first argument, followed by the path to insert into, followed by the value to insert. You can provide multiple path/value pairs if you need to update multiple values.
From version 8.0.13 onwards, the documentation says (emphasis is mine):
The
BLOB,TEXT,GEOMETRY, andJSONdata 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, andJSONdata types cannot be assigned a default value.
99
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
