A colleague needs to add a field to a table (InnoDB table with existing data). If no default value is required, add the data in the previous table: 01 mysql> select * from t1; 02 + ------ + 03 | id | 04 + ------ + 05 | 1 | 06 | 2 | 07 | 3 | 08 + ------ + 093 rows in set (0.02 sec) 10 www.2cto.com 11 mysql> alter table t1 add col1 char (1); 12 Query OK, 3 rows affected (0.24 sec) 13 Records: 3 Duplicates: 0 Warnings: 0. data in the table after the field is added: 1 mysql> select * from t1; 2 + ------ + 3 | id | col1 | 4 + ------ + 5 | 1 | NULL | 6 | 2 | NULL | 7 | 3 | NULL | 8 www.2cto.com + ------ + 93 rows in set (0.00 sec) after a while, he asked to set the default value of the new field to 'N': 1 mysql> alter table t1 modify col1 char (1) default 'n'; 2 Query OK, 0 rows affected (0.05 sec) 3 Records: 0 Duplicates: 0 Warnings: 0 View data in the current table: 1 mysql> select * from t1; 2 + ------ + 3 | id | col1 | 4 + ------ + 5 | 1 | NULL | 6 | 2 | NULL | 7 | 3 | NULL | 8 www.2cto.com + ------ + ------ + 9 3 rows in set (0.00 sec) operations here, it can be said that this demand of colleagues has come to an end. However, if we put the time back to the starting point of this small demand, if our colleagues put forward a complete requirement at one time: to add a char (1) field, the default value is 'N': 01 mysql> alter table t1 add col1 char (1) default 'n'; 02 Query OK, 3 rows affected (0.19 sec) 03 Records: 3 Duplicates: 0 Warnings: 004 View table data: 05 mysql> select * from t1; 06 + ------ + 07 | id | col1 | 08 + ------ + 09 www.2cto.com | 1 | N | 10 | 2 | N | 11 | 3 | N | 12 + ------ + ------ + 133 rows in set (0.00 sec)
The two operations are basically the same, but the results of newly added fields in the old data in the table are different. I did not want to describe the default value of field type filling because it is not a complicated concept, but I think I should pay attention to some similar small details in the future.