What I was trying to do is avoid listing my TEXT columns in the INSERTs and they would have a default '' (empty string).
I managed to set (see below) a default '' value for my TEXT column, but it generated a warning and I cannot see what changed on my table structure.
Please check the code below to reproduce the issue.
DROP TABLE IF EXISTS `text_test`;
CREATE TABLE `text_test` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`text` TEXT NOT NULL,
PRIMARY KEY (`id`)
)
COLLATE='utf8_unicode_ci'
ENGINE=InnoDB
;
-- created OK
SHOW CREATE TABLE `text_test`;
-- CREATE TABLE `text_test` (
-- `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
-- `text` text COLLATE utf8_unicode_ci NOT NULL,
-- PRIMARY KEY (`id`)
-- ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
-- no surprises here...
INSERT INTO `text_test` () VALUES ();
-- warning: text has no default value
SELECT * FROM `text_test`;
-- id=1, text=''
ALTER TABLE `text_test`
CHANGE COLUMN `text` `text` TEXT NOT NULL DEFAULT '' COLLATE 'utf8_unicode_ci' AFTER `id`;
-- warning: blob/text cannot have default...
SHOW CREATE TABLE `text_test`;
-- CREATE TABLE `text_test` (
-- `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
-- `text` text COLLATE utf8_unicode_ci NOT NULL,
-- PRIMARY KEY (`id`)
-- ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
-- no surprises, seems like the table did not change at all...
INSERT INTO `text_test` () VALUES ();
-- no warning generated, but why if the table structure is still the same??
SELECT * FROM `text_test`;
-- id=1, text=''
-- id=2, text=''
My server info:
SHOW VARIABLES LIKE "%version%";
-- "Variable_name" "Value"
-- "innodb_version" "5.1.73-14.6"
-- "protocol_version" "10"
-- "version" "5.1.73-rel14.11-log"
-- "version_comment" "(Percona Server (GPL), 14.11)"
-- "version_compile_machine" "x86_64"
-- "version_compile_os" "debian-linux-gnu"
