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

Question

如果你在MySQL的表上尝试创建一个TEXT列，并给它设置默认值，在Windows操作系统上会出错。我不明白为什么一个TEXT列不能有默认值，MySQL文档也没有解释原因。对我来说，这似乎有些不合逻辑（并且有些令人沮丧，因为我需要一个默认值！）。有人知道为什么不允许这样吗？

- Russ

---

你能让我们看看你使用的查询吗？ - Robert

你确定要使用TEXT列，而不是VARCHAR列吗？TEXT列适用于长度超过255个字节的内容。 - scy

这应该是一条评论。同时，是的，他的意思是“TEXT” - 这些列不能有默认值。“VARCHAR”可以。 - Pekka

是的，不幸的是我需要超过255个字符。 - Russ

至少文档中说不允许使用默认值来赋值BLOB、TEXT、GEOMETRY和JSON列。 http://dev.mysql.com/doc/refman/5.7/en/data-type-defaults.html - stmllr

Answer 1

Windows MySQL v5会引发错误，而Linux和其他版本只会引发警告。这需要修复。 什么鬼？

此外，在MySQL Bugtracker中尝试通过错误＃19498进行修复：

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

个人认为这是一个错误。 搜索“BLOB / TEXT列不能具有默认值”在Google上返回约2,940个结果。 其中大多数是报告在尝试安装在一个系统上工作但在其他系统上无法工作的DB脚本时的不兼容性。

我现在遇到了同样的问题，我正在修改其中一个客户的Web应用程序，最初在Linux MySQL v5.0.83-log上部署。 我运行的是Windows MySQL v5.1.41。 即使尝试使用最新版本的phpMyAdmin提取数据库，它也不会报告有关该文本列的默认值。 但是，当我尝试在Windows上运行插入（在Linux部署上运行良好）时，我会收到ABC列无默认值的错误。 我尝试使用显而易见的默认值（基于该列唯一值的选择），结果收到了如此有用的错误信息 BLOB / TEXT列不能具有默认值。

再次强调，不跨平台地维护基本兼容性是不可接受的，是一个bug。

---

如何在MySQL 5（Windows）中禁用严格模式：

• 编辑/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

如果您拥有root/admin权限，您可能能够执行：

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

- Ku Logix

---

如果您拥有根访问权限并正在使用phpMyAdmin，请转到主页面（单击phpMyAdmin标志），转到变量选项卡，找到sql_mode变量，然后单击编辑。 - Gavin

我正在使用CentOS 5.8和MySQL v14.14 Distrib 5.1.71。当尝试为TEXT字段设置默认值时，会抛出错误而不是警告。只想提醒一下，在某些Linux平台上可能无法正常工作。 - Alex

现在看来，OS X至少会抛出一个错误。文档http://dev.mysql.com/doc/refman/5.7/en/blob.html说：“BLOB和TEXT列不能有默认值。”顺便提一下（但不是原因）。 - rogerdpack

我也认为这是一个错误。这让我感到沮丧，因为缺少默认值要么强制你将每个TEXT列设置为允许NULL，要么强制你在插入时指定一个值，因为你甚至不能指定空字符串作为默认值。对我来说，关闭STRICT_TRANS_TABLES不是一个好的解决方法，因为我想长期保留它，有很多原因。此外，我遇到了与此相关的兼容性问题。例如，Postgres允许在文本列上设置默认值；这一点让我想迁移到Postgres，而不是从Postgres迁移。 - cazort

Answer 2

没有深入了解mySQL引擎，但我认为这听起来像是一种节省内存的策略。我猜想原因在于 文档 中的这段话：

每个BLOB或TEXT值都由一个单独分配的对象在内部表示。这与所有其他数据类型不同，在这些数据类型中，当打开表时，会为每列分配存储空间。

似乎预先填充这些列类型将导致内存使用和性能损失。

- Pekka

---

在TEXT列中存储数据（例如城市名称）实际上比在CHAR或VARCHAR列中存储相同数据占用更少的内存。 - David Cary

@david，我引用的手册章节不是关于存储，而是检索。 - Pekka

我不认为这会导致任何异常的内存使用和性能惩罚。显然，当用户定义默认值时，无论数据类型如何（特别是在批量操作中），他都期望会有性能损失。但是，据我所知，您声称对于BLOB/TEXT字段，与其他数据类型相比，这种性能损失相对较高？而且，这与BLOB/TEXT作为单独对象存储的事实有什么关系呢？这对我来说毫无意义。 - freakish

在我看来，这不是一种节省内存的策略。要么是个bug，要么就是编写代码的人疯了。而我认为是后者，因为他们至少8年来都无法解决这个问题。这是其他任何数据库都具备的基本功能。 - freakish

无关紧要，对于想要使用它的人来说，它仍然应该是一个选项。 - jurchiks

Answer 3

作为主要问题：

有人知道为什么不允许这样做吗？

仍未得到回答，我快速搜索并发现一个来自MySQL开发人员的相对较新的补充，位于MySQL Bugs：

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

由开发人员发布：

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

这并不是最终答案，但至少可以作为“为什么”问题的起点。

与此同时，我将编写绕过该问题的代码，要么使列可空，要么在应用程序代码中显式地分配（默认''）值以供每个insert使用...

- Marten Koetsier

---

除了TEXT，难道没有其他类型可用吗？如果255个字符足够，可以使用VARCHAR(255)。但是TINYTEXT呢？似乎VARCHAR(4096)也可以使用。 - rubo77

Answer 4

支持在MySQL 8.0.13，发布于2018-10-22中将表达式用作默认值，并且适用于TEXT、JSON、BLOB和GEOMETRY。

仍然不能写成：

create table foo(bar text default 'baz')

但现在你可以写：

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

实现相同的事情。

- Oreille

---

非常感谢！找到这个真是太不容易了。 - Natan Fernandes

Answer 5

"TEXT/BLOB列中的DEFAULT支持"是MySQL Bugtracker中的一个功能请求(Bug #21532)。

我看到不仅我希望在TEXT列中放置默认值，我认为这个功能应该在MySQL的后续版本中得到支持。

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

- David Cary

---

在我的看法中，你应该可以在允许为空的TEXT列之间将其更改为""和NULL。但似乎不可能这样做。 - phpguru

Answer 6

通过使用触发器，您可以获得与默认值相同的效果

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 ;

- Tim Child

Answer 7

我通常在Linux上运行网站，但我也在本地Windows机器上进行开发。我遇到过这个问题很多次，每当我遇到问题时就会修复表格。昨天我安装了一个应用程序来帮助某人，当然又遇到了这个问题。因此，我决定找出问题所在，并找到了这个线程。我真的不喜欢将服务器的sql_mode更改为早期模式（默认情况下），因此我想出了一个简单（我认为）的解决方案。

当然，这个解决方案需要开发人员包装他们的表格创建脚本以补偿在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');

就是这样了。

- Darrell Greenhouse

---

这并没有回答为什么MySQL会有这种行为，但感谢您分享您的方法，让其他人也能受益。欢迎来到Stack Overflow! - GargantuChet

是的，我知道...我需要进一步研究STRICT模式是否有意义-因为MySQL在Nix平台上会抛出警告，但在Windows平台上会失败。这表明无论平台如何，实现可能存在问题。您会注意到，在MySQL的文档中，有这样的通知：“BLOB和TEXT列不能具有默认值。”因此，从逻辑上讲，似乎在5版本之前的所有平台上实现都存在问题。 - Darrell Greenhouse

Answer 8

针对Ubuntu 16.04：

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

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

如果mysql.cnf中存在以下行：

sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

然后将其替换为：

sql_mode='MYSQL40'

否则，只需在mysqld.cnf中添加以下行即可。

sql_mode='MYSQL40'

这个问题已被解决。

- Shiv Buyya

---

这很好，如果你想禁用严格模式。然而，如果你想保持它启用，我认为可能没有解决方案，这很不幸，因为我也在这个情况中。我想唯一的选择是允许NULL值（我不想要），并将其作为默认值。 - cazort

Answer 9

这是一个非常古老的问题，但似乎仍然没有得到正确的回答。我的答案并不是对问题“为什么文本列不能有默认值”的实际回答，但由于在评论中无法写长文本，而且我的评论可能有助于防止错误，因此我将其作为单独的答案提供：

一些人认为错误是由于操作系统- Windows-Linux引起的，但这与操作系统没有直接关系。（但是，在不同操作系统的不同安装程序中，MySQL的默认设置可能存在差异，我不确定。）

主要原因是sql_mode设置中的STRICT_TRANS_TABLES标志。如果在TEXT数据类型列的INSERT语句中未指定值，并且标志存在于sql_mode设置中，则MySQL会报告错误；如果该标志不存在，则MySQL仅报告警告并插入记录。

因此，为了防止此错误，可以从MySQL的sql_mode设置中删除STRICT_TRANS_TABLES。（如果它可能影响数据库上的其他操作，则他可能需要将模式重置为先前的值。）

根据MySQL中SQL模式文档的说明...

对于STRICT_TRANS_TABLES，如果值无效，则MySQL会将其转换为最接近的有效值并插入已调整的值。如果值缺失，则MySQL会插入列数据类型的隐式默认值。在任一情况下，MySQL生成警告而不是错误，并继续处理语句。隐式默认值在第11.6节“数据类型默认值”中有描述。

...以及数据类型默认值文档... BLOB、TEXT、GEOMETRY和JSON数据类型不能被赋予默认值。

...TEXT列不能有默认值，但是如果从sql_mode中删除STRICT_TRANS_TABLES，则MySQL在INSERT语句中为TEXT列插入空字符串''，如果没有为TEXT列指定值。

- Nikunj Bhatt

Answer 10

古老的问题，但解决方案并不太旧：）

遗留原因：

确实是一个有效的功能请求[具有默认的TEXT值]，乍一看可能似乎很简单。但是，TEXT/BLOB值不是直接存储在用于读取/更新表的记录缓冲区中的。因此，为它们分配默认值稍微复杂一些。

从MySQL 8.0.13开始，我们可以使用表达式定义默认的TEXT/GEOMETRY/JSON值：

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())
);

请注意使用DEFAULT()而不是简单的经典DEFAULT。

更多信息请参阅：https://dev.mysql.com/doc/refman/8.0/en/data-type-defaults.html#data-type-defaults-explicit

- JCH77