Mysql – Why can’t a text column have a default value in MySQL

default-valueMySQL

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?

Best Answer

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'");

Related Solutions

Mysql – How to set a default value for a MySQL Datetime column

IMPORTANT EDIT: It is now possible to achieve this with DATETIME fields since MySQL 5.6.5, take a look at the other post below...

Previous versions can't do that with DATETIME...

But you can do it with TIMESTAMP:

mysql> create table test (str varchar(32), ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
Query OK, 0 rows affected (0.00 sec)

mysql> desc test;
+-------+-------------+------+-----+-------------------+-------+
| Field | Type        | Null | Key | Default           | Extra |
+-------+-------------+------+-----+-------------------+-------+
| str   | varchar(32) | YES  |     | NULL              |       | 
| ts    | timestamp   | NO   |     | CURRENT_TIMESTAMP |       | 
+-------+-------------+------+-----+-------------------+-------+
2 rows in set (0.00 sec)

mysql> insert into test (str) values ("demo");
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+------+---------------------+
| str  | ts                  |
+------+---------------------+
| demo | 2008-10-03 22:59:52 | 
+------+---------------------+
1 row in set (0.00 sec)

mysql>

CAVEAT: IF you define a column with CURRENT_TIMESTAMP ON as default, you will need to ALWAYS specify a value for this column or the value will automatically reset itself to "now()" on update. This means that if you do not want the value to change, your UPDATE statement must contain "[your column name] = [your column name]" (or some other value) or the value will become "now()". Weird, but true. I am using 5.5.56-MariaDB

Mysql – How to find all the tables in MySQL with specific column names in them

To get all tables with columns columnA or ColumnB in the database YourDatabase:

SELECT DISTINCT TABLE_NAME 
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE COLUMN_NAME IN ('columnA','ColumnB')
        AND TABLE_SCHEMA='YourDatabase';

Best Answer

Related Solutions

Mysql – How to set a default value for a MySQL Datetime column

Mysql – How to find all the tables in MySQL with specific column names in them

Related Topic