MySQL 将 TEXT 列默认值从 null 更改为 ''（空字符串）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16141727/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Change TEXT column default from null to '' (empty string)
提问by Tushar Trivedi
I have a table where default value is not specified at the time of table creation. Now I want to change default value to '' (empty string). When I run alter table query it get success but still on new row insertion table consider NULL as default value if column value is not specified.
我有一个表，其中在创建表时未指定默认值。现在我想将默认值更改为 '' （空字符串）。当我运行 alter table query 时，它会成功，但如果未指定列值，则仍在新行插入表中将 NULL 视为默认值。
TABLE Schema::
表架构::
CREATE TABLE `table1` (
`col1` INT(11) NOT NULL AUTO_INCREMENT,
`col2` TEXT,
`col3` INT(11) DEFAULT NULL,
`col4` TINYINT(1) DEFAULT '0',
PRIMARY KEY (`id`)
);
ALTER Query::
更改查询::
ALTER TABLE `table1` change `col2` `col2` text default '';
回答by Salman A
回答by Swatantra Kumar
First of all the Table schema is incorrect. It will throw error: Key column 'id' doesn't exist in table
首先，表模式不正确。它会抛出错误：表中不存在键列“id”
CREATE TABLE `test_table1` (
`col1` INT(11) NOT NULL AUTO_INCREMENT,
`col2` TEXT,
`col3` INT(11) DEFAULT NULL,
`col4` TINYINT(1) DEFAULT '0',
PRIMARY KEY (`id`)
);
Correcting the question itself
更正问题本身
CREATE TABLE `test_table1` (
`col1` INT(11) NOT NULL AUTO_INCREMENT,
`col2` TEXT,
`col3` INT(11) DEFAULT NULL,
`col4` TINYINT(1) DEFAULT '0',
PRIMARY KEY (`col1`)
);
Now if you'll run the Alter statment
现在，如果您要运行 Alter 语句
ALTER TABLE `table1` change `col2` `col2` text default '';
The error message is: BLOB, TEXT, GEOMETRY or JSON column 'col2' can't have a defaultvalue. Reference
错误消息是：BLOB、TEXT、GEOMETRY 或 JSON 列“col2”不能有default值。参考
Good Read: Why can't a text column have a default value in MySQL?
回答by Sashi Kant
Use modify
用 modify
ALTER TABLE `table1` MODIFY column `col2` text default '';
回答by Zaffy
ALTER TABLE `table1` change `col2` `col2` text NOT NULL default '';
回答by JDGuide
Try this
尝试这个
ALTER TABLE yourtable_name ALTER COLUMN columnname SET DEFAULT ' ' ;
Its working for me.Hope it will help you.
它对我有用。希望它会帮助你。
回答by Gourav Agaste
In phpmyadmin go to "change" option for specific Field and select Default2 as As defined: and leave below text box empty
在 phpmyadmin 中，转到特定字段的“更改”选项并选择 Default2 作为定义：并将下面的文本框留空
For example:
例如：
Field frequency
Type VARCHAR
Length/Values1 20
Default2 As defined: and Text box empty
字段频率
类型 VARCHAR
长度/值 1 20
默认值 2 定义：和文本框为空
Last click on Save button
最后点击保存按钮
回答by billmask
Alter table structure using the following query
USE MODIFY ALTER TABLE <table> MODIFY <column> VARCHAR(<XX>) DEFAULT '';
使用以下查询更改表结构
USE MODIFY ALTER TABLE <table> MODIFY <column> VARCHAR(<XX>) DEFAULT '';
