One of our customers wants to create a table having a column of data type TEXT with the default value, but they encountered an error: ERROR 1101 (42000): BLOB, TEXT, GEOMETRY or JSON column 'b' can't have a default value. It seems reasonable at first glimpse, as we know that each BLOB, TEXT, GEOMETRY, or JSON value is represented internally by a separately allocated object. This is in contrast to all other data types, for which storage is allocated once per column when the table is opened. But in the real world, the restriction is not that reasonable; for example, we may need a default value for a GEOMETRY column as a starting point.
It is easy for us as a DBA to return to our client and tell them: “Please do not try to assign a default value to a column of data type BLOB, TEXT, GEOMETRY, or JSON.” But as an engineer with curiosity, since the request from the real world is reasonable, I would like to think out of the box: Is there a way to assign a default value to the BLOB, TEXT, GEOMETRY, and JSON data types?
I searched online and found the messages from https://dev.mysql.com/doc/ are kind of confusing/conflicting. Some places say:
“BLOB and TEXT columns cannot have DEFAULT values.”; “The BLOB, TEXT, GEOMETRY, and JSON data types cannot be assigned a default value.”
And another place says:
“A JSON column cannot have a non-NULL default value.”
Yet another place says:
”Prior to MySQL 8.0.13, a JSON column cannot have a non-NULL default value.”
And one more place says:
To make things clearer, let us do some tests and see.
1.) We can assign a default null value for BLOB, TEXT, GEOMETRY, and JSON data types, MySQL 5.7 and/or MySQL 8.0.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 | mysql 5.7.44 ============ mysql [localhost:5744] {msandbox} (test) > CREATE TABLE t1_default (b BLOB DEFAULT null,t TEXT DEFAULT null,j JSON DEFAULT null,p POINT DEFAULT null); Query OK, 0 rows affected (0.01 sec) mysql [localhost:5744] {msandbox} (test) > show create table t1_defaultG *************************** 1. row *************************** Table: t1_default Create Table: CREATE TABLE `t1_default` ( `b` blob, `t` text, `j` json DEFAULT NULL, `p` point DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 mysql 8.0.13 ============ mysql [localhost:8013] {msandbox} (test) > CREATE TABLE t1_default (b BLOB DEFAULT null,t TEXT DEFAULT null,j JSON DEFAULT null,p POINT DEFAULT null); Query OK, 0 rows affected (0.11 sec) mysql [localhost:8013] {msandbox} (test) > show create table t1_defaultG *************************** 1. row *************************** Table: t1_default Create Table: CREATE TABLE `t1_default` ( `b` blob, `t` text, `j` json DEFAULT NULL, `p` point DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
2.) Before MySQL 8.0.13, the BLOB, TEXT, GEOMETRY, and JSON data types can NOT be assigned a non-NULL as a default value, putting them in expression or not.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | mysql 5.7.44 ============ mysql [localhost:5744] {msandbox} (test) > CREATE TABLE t1_default (b BLOB DEFAULT 'Blob',t TEXT DEFAULT 'Text',j JSON DEFAULT '{"key1": "value1", "key2": "value2"}',p POINT DEFAULT 'Point(0,0)'); ERROR 1101 (42000): BLOB, TEXT, GEOMETRY or JSON column 'b' can't have a default value mysql [localhost:5744] {msandbox} (test) > CREATE TABLE t2_default(b BLOB DEFAULT ('Blob'),t TEXT DEFAULT ('Text'),j JSON DEFAULT ('{"key1": "value1", "key2": "value2"}'),p POINT DEFAULT (Point(0,0))); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '('Blob'),t TEXT DEFAULT ('Text'),j JSON DEFAULT ('{"key1": "value1", "key2": "va' at line 1 mysql 8.0.12 ============ mysql [localhost:8012] {msandbox} (test) > CREATE TABLE t1_default (b BLOB DEFAULT 'Blob',t TEXT DEFAULT 'Text',j JSON DEFAULT '{"key1": "value1", "key2": "value2"}',p POINT DEFAULT 'Point(0,0)'); ERROR 1101 (42000): BLOB, TEXT, GEOMETRY or JSON column 'b' can't have a default value mysql [localhost:8012] {msandbox} (test) > CREATE TABLE t2_default(b BLOB DEFAULT ('Blob'),t TEXT DEFAULT ('Text'),j JSON DEFAULT ('{"key1": "value1", "key2": "value2"}'),p POINT DEFAULT (Point(0,0))); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '('Blob'),t TEXT DEFAULT ('Text'),j JSON DEFAULT ('{"key1": "value1", "key2": "va' at line 1 |
3.) After (including) MySQL 8.0.13, the BLOB, TEXT, GEOMETRY, and JSON data types cannot directly assign a default value.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | mysql 8.0.13 ============ mysql [localhost:8013] {msandbox} (test) > CREATE TABLE t1_default (b BLOB DEFAULT 'Blob',t TEXT DEFAULT 'Text',j JSON DEFAULT '{"key1": "value1", "key2": "value2"}',p POINT DEFAULT 'Point(0,0)'); ERROR 1101 (42000): BLOB, TEXT, GEOMETRY or JSON column 'b' can't have a default value mysql 8.0.14 ============ mysql [localhost:8014] {msandbox} (test) > CREATE TABLE t1_default (b BLOB DEFAULT 'Blob',t TEXT DEFAULT 'Text',j JSON DEFAULT '{"key1": "value1", "key2": "value2"}',p POINT DEFAULT 'Point(0,0)'); ERROR 1101 (42000): BLOB, TEXT, GEOMETRY or JSON column 'b' can't have a default value mysql 8.0.35 ============ mysql [localhost:8035] {msandbox} (test) > CREATE TABLE t1_default (b BLOB DEFAULT 'Blob',t TEXT DEFAULT 'Text',j JSON DEFAULT '{"key1": "value1", "key2": "value2"}',p POINT DEFAULT 'Point(0,0)'); ERROR 1101 (42000): BLOB, TEXT, GEOMETRY or JSON column 'b' can't have a default value |
4.) After (including) MySQL 8.0.13, the BLOB, TEXT, GEOMETRY, and JSON data types can be assigned a default value when the value is written as an expression.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 | mysql 8.0.13 ============ mysql [localhost:8013] {msandbox} (test) > CREATE TABLE t2_default(b BLOB DEFAULT ('Blob'),t TEXT DEFAULT ('Text'),j JSON DEFAULT ('{"key1": "value1", "key2": "value2"}'),p POINT DEFAULT (Point(0,0))); Query OK, 0 rows affected (0.07 sec) mysql [localhost:8013] {msandbox} (test) > show create table t2_defaultG *************************** 1. row *************************** Table: t2_default Create Table: CREATE TABLE `t2_default` ( `b` blob DEFAULT (_utf8mb4'Blob'), `t` text DEFAULT (_utf8mb4'Text'), `j` json DEFAULT (_utf8mb4'{"key1": "value1", "key2": "value2"}'), `p` point DEFAULT (point(0,0)) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.01 sec) mysql 8.0.14 ============ mysql [localhost:8014] {msandbox} (test) > CREATE TABLE t2_default(b BLOB DEFAULT ('Blob'),t TEXT DEFAULT ('Text'),j JSON DEFAULT ('{"key1": "value1", "key2": "value2"}'),p POINT DEFAULT (Point(0,0))); Query OK, 0 rows affected (0.03 sec) mysql [localhost:8014] {msandbox} (test) > show create table t2_defaultG *************************** 1. row *************************** Table: t2_default Create Table: CREATE TABLE `t2_default` ( `b` blob DEFAULT (_utf8mb4'Blob'), `t` text DEFAULT (_utf8mb4'Text'), `j` json DEFAULT (_utf8mb4'{"key1": "value1", "key2": "value2"}'), `p` point DEFAULT (point(0,0)) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.01 sec) mysql 8.0.35 ============ mysql [localhost:8035] {msandbox} (test) > CREATE TABLE t2_default(b BLOB DEFAULT ('Blob'),t TEXT DEFAULT ('Text'),j JSON DEFAULT ('{"key1": "value1", "key2": "value2"}'),p POINT DEFAULT (Point(0,0))); Query OK, 0 rows affected (0.03 sec) mysql [localhost:8035] {msandbox} (test) > show create table t2_defaultG *************************** 1. row *************************** Table: t2_default Create Table: CREATE TABLE `t2_default` ( `b` blob DEFAULT (_utf8mb4'Blob'), `t` text DEFAULT (_utf8mb4'Text'), `j` json DEFAULT (_utf8mb4'{"key1": "value1", "key2": "value2"}'), `p` point DEFAULT (point(0,0)) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.02 sec) |
Conclusion
Put all the information together, we are safe to make the statement as the conclusion:
- Before MySQL 8.0.13, the BLOB, TEXT, GEOMETRY, and JSON data types can NOT be assigned a non-NULL default value.
- After (including) MySQL 8.0.13, 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 literal.
We would also suggest Orcale review the documentation and make all the information more accurate and aligned with each other.
That the default values have to be an expression seems a bit unnecessary, although using expressions is a fair workaround. Literal default values for blob and text columns have been supported in MariaDB since 10.2.1. I’m sure MySQL will support this as well one day.