为什么文本列在 MySQL 中不能有默认值？

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

提问by Russ

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?

如果您尝试在表上创建 TEXT 列，并在 MySQL 中为其指定默认值，则会出现错误（至少在 Windows 上）。我看不出为什么文本列不应该有默认值的任何原因。MySQL 文档没有给出任何解释。这对我来说似乎不合逻辑（而且有点令人沮丧，因为我想要一个默认值！）。有人知道为什么不允许这样做吗？

采纳答案by Ku Logix

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

Windows MySQL v5 引发错误，但 Linux 和其他版本仅引发警告。这需要修复。 跆拳道？

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

另请参阅 MySQL Bugtracker 中将其修复为错误 #19498 的尝试：

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:

Bryce Nesbitt 于 2008 年 4 月 4 日下午 4:36：
在 MS Windows 上，“no DEFAULT”规则是一个错误，而在其他平台上，它通常是一个警告。虽然不是错误，但如果您在宽松的平台上编写代码，然后在严格的平台上运行它，则可能会陷入困境：

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.

就个人而言，我确实认为这是一个错误。在 Google 上搜索“BLOB/TEXT 列不能有默认值”会返回大约 2,940 个结果。其中大多数是在尝试安装在一个系统上运行但在其他系统上运行的数据库脚本时不兼容的报告。

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.

我现在在为我的一个客户端修改的 webapp 上遇到了同样的问题，最初部署在 Linux MySQL v5.0.83-log 上。我正在运行 Windows MySQL v5.1.41。即使尝试使用最新版本的 phpMyAdmin 来提取数据库，它也不会报告相关文本列的默认值。然而，当我尝试在 Windows 上运行插入（在 Linux 部署上运行良好）时，我在 ABC 列上收到无默认值的错误。我尝试使用明显的默认值（基于该列的唯一值选择）在本地重新创建表，最终收到非常有用的BLOB/TEXT 列不能有默认值。

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

同样，不保持跨平台的基本兼容性是不可接受的，并且是一个错误。

---

---

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

如何在 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
  

• 编辑 /my.ini 并查找行

  sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
  

• 将其替换为

  sql_mode='MYSQL40'
  

• 重启MySQL服务（假设是mysql5）

  net stop mysql5
  net start mysql5
  

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

如果您有 root/admin 访问权限，您也许可以执行

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

回答by Pekka

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:

如果对 mySQL 引擎没有任何深入了解，我会说这听起来像是一种节省内存的策略。我认为原因在文档中的这一段后面：

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.

每个 BLOB 或 TEXT 值在内部由一个单独分配的对象表示。这与所有其他数据类型形成对比，其他数据类型在打开表时每列分配一次存储空间。

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

似乎预填充这些列类型会导致内存使用和性能下降。

回答by Tim Child

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 ;

回答by David Cary

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

“在 TEXT/BLOB 列中支持 DEFAULT”是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.

我知道我不是唯一一个想在 TEXT 列中放置默认值的人。我认为这个特性应该会在更高版本的 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.

这在 MySQL 5.0 版本中无法修复，因为如果有人试图在不支持该功能的（当前）数据库和任何支持该功能的数据库之间来回传输数据库，显然会导致不兼容和数据丢失那个功能。

回答by Marten Koetsier

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:

仍然没有回答，我进行了快速搜索，并在MySQL Bugs 上找到了 MySQL 开发人员的一个相对较新的补充：

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

[2017 年 3 月 17 日 15:11] St?le Deraas

开发者发表：

这确实是一个有效的功能请求，乍一看，添加它似乎微不足道。但是 TEXT/BLOBS 值并不直接存储在用于读取/更新表的记录缓冲区中。因此，为它们分配默认值要复杂一些。

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

这不是明确的答案，但至少是为什么问题的起点。

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

同时，我将围绕它编写代码，并使列可以为空或从应用程序代码中''为每个显式分配一个（默认）值insert......

回答by Darrell Greenhouse

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.

我通常在 Linux 上运行站点，但我也在本地 Windows 机器上开发。我多次遇到这个问题，当我遇到问题时只是修复了表格。我昨天安装了一个应用程序来帮助某人，当然又遇到了这个问题。所以，我决定是时候弄清楚发生了什么 - 并找到了这个线程。我真的不喜欢将服务器的 sql_mode 更改为早期模式（默认情况下）的想法，所以我想出了一个简单的（我认为）解决方案。

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.

该解决方案当然需要开发人员包装他们的表创建脚本以补偿在 Windows 上运行的 MySQL 问题。您将在转储文件中看到类似的概念。一个重要的警告是，如果使用分区，这可能/将导致问题。

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

就是这样。

回答by Shiv Buyya

For Ubuntu 16.04:

对于 Ubuntu 16.04：

How to disable strict mode in MySQL 5.7:

如何在 MySQL 5.7 中禁用严格模式：

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

编辑文件/etc/mysql/mysql.conf.d/mysqld.cnf

If below line exists in mysql.cnf

如果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

只需在 mysqld.cnf 中添加以下行

sql_mode='MYSQL40'

This resolved problem.

这解决了问题。