Why Can't MySQL TEXT Columns Have Default Values?

Why MySQL Text Columns Cannot Have Default Values

Question: Why do text columns in MySQL not allow default values?

Answer:

Although MySQL documentation does not provide a clear explanation, this restriction is attributed to inconsistencies between Windows and other MySQL versions.

Windows Behavior:

On Windows, MySQL version 5 raises an error when attempting to create a TEXT column with a default value.

Linux and Other Versions:

On Linux and other operating systems, MySQL raises only a warning. However, this behavior is inconsistent and can cause problems when moving database scripts between different platforms.

Bug Report:

This inconsistency has been reported as bug #19498 in the MySQL Bugtracker. The issue was acknowledged but not resolved, due to potential compatibility issues.

Workaround:

To disable the strict mode that prevents default values in text columns on Windows, follow these steps:

1. Edit the "/my.ini" file and locate the "sql-mode" line.
2. Replace the current value with "sql_mode='MYSQL40'".
3. Restart the MySQL service (mysql5).

Alternatively, you can execute the following SQL query (assuming root/admin access):

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

This will allow you to create text columns with default values on Windows MySQL while maintaining compatibility with other versions.

The above is the detailed content of Why Can't MySQL TEXT Columns Have Default Values?. For more information, please follow other related articles on the PHP Chinese website!