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

Question

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?

Amoebocyte

Can we see the query you used? – Technician

Are you sure you want a TEXT column, not a VARCHAR one? TEXT columns are for things which can become more than 255 bytes long. – Benedict

This should be a comment. Also,yes, he does mean TEXT - those columns can't have a default value. VARCHAR can. – Conformal

If you are using phpmyadmin to setup your database, might want to investigate the mysql gui tools / workbench... ;) – Darnel

I know what he means, I was asking whether he really needs a TEXT column. Because if he doesn’t, using (VAR)CHAR instead would be a plausible solution. This was why I’ve decided to post this as an answer. No need to vote it down (and leave an answer not talking about TEXT colums at all at 0). – Benedict

Yes, I need more than 255 characters unfortunately. – Amoebocyte

@pekka, I think it's just good practice - while phpmyadmin is a great package, if you really want to delve deeper into the structure of the database, and discover what mysql is capable of, then a decent GUI package will always pay off. Workbench is really in a different planet to phpmyadmin, but I realise that it's not always an option. Mainly - I find that beginners with mysql can assume that phpmyadmin is the "only" way to admin their schema, and from my own experience, it was like light at the end of the tunnel after using the gui tools for the first time. – Darnel

@Darnel sorry, I misunderstood you entirely. I thought you were implying that phpMyAdmin is somehow at fault for the "can't have a default value" message. I agree: For Windows users, there's nothing greater than V5 of HeidiSQL (www.heidisql.com) – Conformal

at least the docs say that default is not allowed: > BLOB, TEXT, GEOMETRY, and JSON columns cannot be assigned a default value. dev.mysql.com/doc/refman/5.7/en/data-type-defaults.html – Hookworm

If it were supported, I would want to use a default value for a text column in MySQL and my reason is that for some fields I don't want to distinguish NULL from the empty string, but if I specify NOT NULL on a text field, I am now forced to specify a value for that field every time I insert a row, which is a bad outcome. The lack of a default value for these fields thus forces me to set all text fields to allow NULL or face cumbersome coding constraints on inserting data. It's not a huge loss but it's not how I'd prefer it to work. I wish they at least allowed an empty string default. – Henigman

Answer 1

Windows MySQL v5 throws an error but Linux and other versions only raise a warning. This needs to be fixed. WTF?

Also see an attempt to fix this as bug #19498 in the MySQL Bugtracker:

Bryce Nesbitt on April 4 2008 4:36pm:
On MS Windows the "no DEFAULT" rule is an error, while on other platforms it is often a warning. While not a bug, it's possible to get trapped by this if you write code on a lenient platform, and later run it on a strict platform:

Personally, I do view this as a bug. Searching for "BLOB/TEXT column can't have a default value" returns about 2,940 results on Google. Most of them are reports of incompatibilities when trying to install DB scripts that worked on one system but not others.

I am running into the same problem now on a webapp I'm modifying for one of my clients, originally deployed on Linux MySQL v5.0.83-log. I'm running Windows MySQL v5.1.41. Even trying to use the latest version of phpMyAdmin to extract the database, it doesn't report a default for the text column in question. Yet, when I try running an insert on Windows (that works fine on the Linux deployment) I receive an error of no default on ABC column. I try to recreate the table locally with the obvious default (based on a select of unique values for that column) and end up receiving the oh-so-useful BLOB/TEXT column can't have a default value.

Again, not maintaining basic compatability across platforms is unacceptable and is a bug.

---

How to disable strict mode in MySQL 5 (Windows):

• Edit /my.ini and look for line

  sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
  

• Replace it with

  sql_mode='MYSQL40'
  

• Restart the MySQL service (assuming that it is mysql5)

  net stop mysql5
  net start mysql5
  

If you have root/admin access you might be able to execute

mysql_query("SET @@global.sql_mode='MYSQL40'");

Gizela

If you have root access and are using phpMyAdmin, go to the main page (click the phpMyAdmin logo), go to the Variables tab, find the sql_mode variable, and click Edit. – Marcelinomarcell

I'm on a CentOS 5.8 and MySQL v 14.14 Distrib 5.1.71 throws an error instead of a warning when trying to set a default value to a TEXT field. Just would like to notice it's not working on every Linux platform. – Fulcrum

OS X at lest seems to throw an error these days. The docs dev.mysql.com/doc/refman/5.7/en/blob.html say "BLOB and TEXT columns cannot have DEFAULT values." FWIW (but not why) – Drear

I also view this as a bug. It's frustrating for me because the lack of default value forces you to either set every TEXT column to allow NULL, or it forces you to specify a value whenever you insert, because you can't even specify the empty string as a default value. Turning off STRICT_TRANS_TABLES is not a good workaround for me because I want that on for a long list of reasons. Also, I've run into compatibility issues with this. Postgres for instance allows defaults on text columns; this factor makes me want to migrate only to, not from postgres. – Henigman

Answer 2

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.

Conformal

-1: Storing data, such as city names, in a TEXT column actually takes less total memory than storing the same data in a CHAR or VARCHAR column. – Advent

@david the manual chapter I'm quoting is not about storage, but retrieval. – Conformal

I don't see how that would lead to any abnormal memory usage and performance penalties. Obviously when a user defines a default value he expects a performance hit no matter what the data type is (especially on bulk operations). However as far as I understand you state that for a BLOB/TEXT field this performance hit is relatively high compared to other data types? And how is that related to the fact that BLOB/TEXT is stored internally as a separated object? This doesn't make any sense to me. – Uprising

IMHO it's not a memory saving strategy. It's either a bug or people who wrote it are insane. And I think it's the latter since they can't fix it for at least 8 years now. The basic functionality that every other database has. – Uprising

MySQL is updateless for quite a while, since Oracle bought it hehe. For real, updates still coming out, but to my mind they are doing less effort,'cause Oracle probably don't want to spend money and time to maintain an open source DBMS and since the "real" developpers switched to their new baby, MariaDB. So don't count on a bug fix for this I guess – Fulcrum

Doesn't matter, it should still be an option for people who want to use it. – Cryosurgery

This is just idle speculation. – Metre

It's particularly insane given that it doesn't even allow the empty string as a default value. What this means is that in MySQL, if you specify NOT NULL on a text field, you MUST specify a value for it whenever you insert a row into the table or it will return an error. Incredibly inconvenient, especially if you don't want to distinguish NULL from the empty string. – Henigman

Answer 3

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...

Cristencristi

Isn't there another type you could use instead of TEXT? if 255 characters are enough, use VARCHAR(255). but what about TINYTEXT? It seems like VARCHAR(4096) also works – Fogged

Answer 4

Support for using expression as default values was added to MySQL 8.0.13, released 2018-10-22, and works for TEXT, JSON, BLOB and GEOMETRY.

You still cannot write :

create table foo(bar text default 'baz')

But you can now write:

create table foo(bar text default ('baz'))

Which achieve the same thing.

Uraeus

Thanks a lot! It was a pain to find this – Dunbarton

Wtf. why is there the difference? – Flesher

Answer 5

"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.

Advent

It seems to me you should be able to change it between "" and NULL for a TEXT column that allows null. It doesn't seem possible to do. – Cupp

Answer 6

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 ;

Diaphanous

Answer 7

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.

Harding

This doesn't address the question of why MySQL has the behavior at all, but thanks for sharing your approach so others can benefit too. Welcome to Stack Overflow! – Photoconductivity

Ya I know... I'll have to look into the STRICT mode further to see if it makes sense - since MySQL throws a warning on Nix' boxes, but fails on Windows boxes. That's an indication that something might be wrong with the implementation regardless of the platform. You'll notice that in MySQL's documentation, there is this notice: "BLOB and TEXT columns cannot have DEFAULT values." So logically, it appears that the implementation in versions before 5 were actually broken on all platforms. – Harding

Answer 8

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.

Abrasion

This is great, if you want to disable strict mode. However, if you want to keep it enabled, I think there are probably no solutions, which is unfortunate because I'm in this boat. I'm thinking the only option is to settle on allowing NULL values (which I don't want) and having that be the default. – Henigman

Answer 9

This is a very old question but still it doesn't seems to have been answered properly. And, my this answer isn't actual answer to the question - "WHY can't a text column have a default value", but as it isn't possible to write long text in comment, and as my comment could help someone to prevent the error, here it is as a separate answer:

Some are saying that the error is occurring because of OS - Windows-Linux; but this isn't directly related to OS. (However, there may be differences in default settings of MySQL within different installers for different OSes, I am not sure.)

The main reason is the flag STRICT_TRANS_TABLES for sql_mode setting. if a value is not specified in INSERT statement for TEXT datatype column and if the flag exist in the sql_mode setting then MySQL is reporting an error; and if the flag doesn't exist then MySQL is only reporting a warning and inserts the record.

So, to prevent this error, one can remove the STRICT_TRANS_TABLES from sql_mode setting of MySQL. (He my need to reset the mode to the previous value if it can affect other operations on the database.)

According to the documentation of SQL mode in MySQL ...

For STRICT_TRANS_TABLES, MySQL converts an invalid value to the closest valid value for the column and inserts the adjusted value. If a value is missing, MySQL inserts the implicit default value for the column data type. In either case, MySQL generates a warning rather than an error and continues processing the statement. Implicit defaults are described in Section 11.6, “Data Type Default Values”.

... and documentation of Data Type Default Values ...

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

... TEXT column can not have a default value, but if STRICT_TRANS_TABLES is removed from sql_mode then MySQL inserts empty string '' if no value is specified for TEXT column in INSERT statement.

Stoneblind

Answer 10

Old question, but no so old resolution :)

Legacy reason :

This is indeed a valid feature request [having default TEXT value], 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.

Starting MySQL 8.0.13 we can define default TEXT/GEOMETRY/JSON value with an expression :

CREATE TABLE product (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name TINYTEXT NOT NULL DEFAULT('<NO-NAME>'),
    description TEXT NOT NULL DEFAULT('<NO-DESCRIPTION>'),
    caracs JSON NOT NULL DEFAULT(JSON_ARRAY())
);

Note DEFAULT() and not simple classical DEFAULT.

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

Sequel