[mysql] Why can't a text column have a default value in MySQL?

If you try to create a TEXT column on a table, and give it a default value in MySQL, you get an error (on Windows at least). I cannot see any reason why a text column should not have a default value. No explanation is given by the MySQL documentation. It seems illogical to me (and somewhat frustrating, as I want a default value!). Anybody know why this is not allowed?

For Ubuntu 16.04:

How to disable strict mode in MySQL 5.7:

Edit file /etc/mysql/mysql.conf.d/mysqld.cnf

If below line exists in mysql.cnf

sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

Then Replace it with

sql_mode='MYSQL40'

Otherwise

Just add below line in mysqld.cnf

sql_mode='MYSQL40'

This resolved problem.

Without any deep knowledge of the mySQL engine, I'd say this sounds like a memory saving strategy. I assume the reason is behind this paragraph from the docs:

Each BLOB or TEXT 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.

It seems like pre-filling these column types would lead to memory usage and performance penalties.

As the main question:

Anybody know why this is not allowed?

is still not answered, I did a quick search and found a relatively new addition from a MySQL developer at MySQL Bugs:

[17 Mar 2017 15:11] Ståle Deraas

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.

This is no definite answer, but at least a starting point for the why question.

In the mean time, I'll just code around it and either make the column nullable or explicitly assign a (default '') value for each insert from the application code...

You can get the same effect as a default value by using a trigger

create table my_text

(
   abc text
);

delimiter //
create trigger mytext_trigger before insert on my_text
for each row
begin
   if (NEW.abc is null ) then
      set NEW.abc = 'default text';
   end if;
end
//
delimiter ;

"Support for DEFAULT in TEXT/BLOB columns" is a feature request in the MySQL Bugtracker (Bug #21532).

I see I'm not the only one who would like to put a default value in a TEXT column. I think this feature should be supported in a later version of MySQL.

This can't be fixed in the version 5.0 of MySQL, because apparently it would cause incompatibility and dataloss if anyone tried to transfer a database back and forth between the (current) databases that don't support that feature and any databases that did support that feature.

I normally run sites on Linux, but I also develop on a local Windows machine. I've run into this problem many times and just fixed the tables when I encountered the problems. I installed an app yesterday to help someone out and of course ran into the problem again. So, I decided it was time to figure out what was going on - and found this thread. I really don't like the idea of changing the sql_mode of the server to an earlier mode (by default), so I came up with a simple (me thinks) solution.

This solution would of course require developers to wrap their table creation scripts to compensate for the MySQL issue running on Windows. You'll see similar concepts in dump files. One BIG caveat is that this could/will cause problems if partitioning is used.

// Store the current sql_mode
mysql_query("set @orig_mode = @@global.sql_mode");

// Set sql_mode to one that won't trigger errors...
mysql_query('set @@global.sql_mode = "MYSQL40"');

/**
 * Do table creations here...
 */

// Change it back to original sql_mode
mysql_query('set @@global.sql_mode = @orig_mode');

That's about it.