MySQL Bugs: #21532: Support for DEFAULT in TEXT/BLOB columns

Bug #21532	Support for DEFAULT in TEXT/BLOB columns
Submitted:	9 Aug 2006 9:28	Modified:	5 Oct 2008 10:05
Reporter:	Ask Hansen (Basic Quality Contributor)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S4 (Feature request)
Version:	5.0.25	OS:	Any (All)
Assigned to:		CPU Architecture:	Any
Tags:	qc, text columntype

Description:
TEXT columns don't support DEFAULT values.  It'd be nice if they did.

Before 5.0.25 there was some limited (although inconsistent support).  Notably you could specify a default value of ''.  As Jim said in http://bugs.mysql.com/bug.php?id=19498, it makes life a little easier for applications that automate some logic from the schema.

How to repeat:
See http://bugs.mysql.com/bug.php?id=19498

Suggested fix:
Implement DEFAULT for TEXT columns. 

To restore the (unintentional) pre-5.0.25 functionality it'd be helpful if just DEFAULT '' was supported.

Thank you for a reasonable feature request.

Which case specifically doesn't work?

Konstantin, there are some pretty specific examples in http://bugs.mysql.com/bug.php?id=19498

Test case:

drop table if exists bar1, bar3;
create table bar1 ( i int, abc text not null default '' );
show warnings;
create table bar3 ( i int, abc text not null default 'test' );

Results: 

mysql> create table bar1 ( i int, abc text not null default '' );
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> show warnings;
+---------+------+---------------------------------------------------+
| Level   | Code | Message                                           |
+---------+------+---------------------------------------------------+
| Warning | 1101 | BLOB/TEXT column 'abc' can't have a default value | 
+---------+------+---------------------------------------------------+
1 row in set (0.00 sec)

mysql> create table bar3 ( i int, abc text not null default 'test' );
ERROR 1101 (42000): BLOB/TEXT column 'abc' can't have a default value

As can be observed from the execution log, there is no consistency (presence of a default sometimes yield an error, sometimes a warning), and, well, no support.

Bug#26798 ERROR 1101 (42000): BLOB/TEXT column 'b' can't have a default value was marked a duplicate of this bug.

Bug #51259 was marked as a duplicate of this one.

Still the case. Would be great to be able to give default-values just like for a varchar.

Missing to set a text-column in an insert-statement gives an error since no default-value is set. And trying to set for example an empty string as default doesn't work because blob/text can't have a default-value.

I expect it's not such a big thing to add this, is it?

Posted by developer:
 
This is indeed a valid feature request, and at first glance it might seem trivial to add. But TEXT/BLOBS values are not stored directly in the record buffer used for reading/updating tables. So it is a bit more complex to assign default values for them.

It would be nice to see it fixed after 12 years of opening the ticket...

+1 This is still a thing in 2019

Is it supported 8.0.13 and later?

https://dev.mysql.com/doc/refman/8.0/en/data-type-defaults.html

Almost 2023, 17 years since the request was posted and still no solution. Is it that hard?

At least we can now use functionnal default values :

create table bag (
    created_at datetime(6),
    v text default('<default-value>') -- Functionnal default
);

insert into bag (created_at) values(current_timestamp);
insert into bag (created_at) values(current_timestamp);

select all * from bag;