BitsCN.com
A colleague asks to add a field to a table (InnoDB table with existing data) without default requirements:
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 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 + ------ + ------ + 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 the 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 + ------ + ------ + 93 rows in set (0.00 sec)
By now, we can say that this demand 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 | 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.
BitsCN.com