为什么文本列在 MySQL 中不能有默认值? [英] Why can't a text column have a default value in MySQL?

问题描述

如果你尝试在表上创建一个 TEXT 列，并在 MySQL 中给它一个默认值，你会得到一个错误(至少在 Windows 上).我看不出文本列不应具有默认值的任何原因.MySQL 文档没有给出任何解释.这对我来说似乎不合逻辑(有点令人沮丧，因为我想要一个默认值！).有人知道为什么不允许这样做吗?

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?

推荐答案

Windows MySQL v5 引发错误，但 Linux 和其他版本仅引发警告.这需要修复.WTF?

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

还可以在 MySQL Bugtracker 中看到尝试将此修复为错误 #19498:

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

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

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:

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

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.

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

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.

如何在 MySQL 5 (Windows) 中禁用严格模式:

• 编辑/my.ini 并查找行

• 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'

重启MySQL服务(假设是mysql5)

Restart the MySQL service (assuming that it is mysql5)

net stop mysql5
net start mysql5

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

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

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

这篇关于为什么文本列在 MySQL 中不能有默认值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！