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.
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.