Description:
In MySQL, BLOB, TEXT, GEOMETRY or JSON columns can't have a default value. However, it is possible to create a table with text fields with default value after setting sql_mode to ''(doesn't include STRICT_TRANS_TABLE). But show create table doesn't show the default value. This easily makes users feel puzzled.
How to repeat:
MySQL [(none)]> show variables like 'sql_mode';
+---------------+-----------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------+-----------------------------------------------------------------------------------------------------------------------+
| sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+---------------+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
MySQL [mytest]> create table t1(id int auto_increment primary key, name varchar(20), tt text not null default '');
ERROR 1101 (42000): BLOB, TEXT, GEOMETRY or JSON column 'tt' can't have a default value
MySQL [mytest]> set sql_mode = '';
Query OK, 0 rows affected (0.00 sec)
MySQL [mytest]> create table t1(id int auto_increment primary key, name varchar(20), tt text not null default '');
Query OK, 0 rows affected, 1 warning (0.06 sec)
MySQL [mytest]> show warnings;
+---------+------+---------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------------------------------+
| Warning | 1101 | BLOB, TEXT, GEOMETRY or JSON column 'tt' can't have a default value |
+---------+------+---------------------------------------------------------------------+
1 row in set (0.00 sec)
======================================
Field tt doesn't have a default value.
======================================
MySQL [mytest]> show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
`tt` text COLLATE utf8mb4_general_ci NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.01 sec)
MySQL [mytest]> set sql_mode = 'STRICT_TRANS_TABLES';
Query OK, 0 rows affected, 1 warning (0.00 sec)
MySQL [(none)]> show variables like 'sql_mode';
+---------------+---------------------+
| Variable_name | Value |
+---------------+---------------------+
| sql_mode | STRICT_TRANS_TABLES |
+---------------+---------------------+
1 row in set (0.01 sec)
MySQL [mytest]> show warnings;
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 3135 | 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release. |
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
================================================================
But we can insert a row without providing the value of field tt.
================================================================
MySQL [mytest]> insert into t1(id, name) values(1, 'xxxx');
Query OK, 1 row affected (0.01 sec)
MySQL [mytest]> select * from t1;
+----+------+----+
| id | name | tt |
+----+------+----+
| 1 | xxxx | |
+----+------+----+
1 row in set (0.00 sec)
Suggested fix:
I think the show create table statement should show the default value of text field here because we can insert a row without providing the value of field tt.