Add a field to the table from the MySQL command line (field name null default)
- 2020-12-22 17:48:17
- OfStack
In the simplest example, in test, add a field named birth of type date.
mysql
>
alter table test add column birth date;
Query OK, 0 rows affected (0.36 sec)
Records: 0 Duplicates: 0 Warnings: 0
Query 1 data to see the results:
mysql
>
select * from test;
+------+--------+----------------------------------+------------+-------+
| t_id | t_name | t_password | t_birth | birth |
+------+--------+----------------------------------+------------+-------+
| 1 | name1 | 12345678901234567890123456789012 | NULL | NULL |
| 2 | name2 | 12345678901234567890123456789012 | 2013-01-01 | NULL |
+------+--------+----------------------------------+------------+-------+
2 rows in set (0.00 sec)
As you can see from the above results, the default value for the inserted birth field is empty. Let's try 1 again, add an birth1 field, and set it not to be null.
mysql
>
alter table test add column birth1 date not null;
Query OK, 0 rows affected (0.16 sec)
Records: 0 Duplicates: 0 Warnings: 0
It worked! ? Surprise! I didn't think this would work because I didn't give it a default value. Let's look at the data:
mysql
>
select * from test;
+------+--------+----------------------------------+------------+-------+------------+
| t_id | t_name | t_password | t_birth | birth | birth1 |
+------+--------+----------------------------------+------------+-------+------------+
| 1 | name1 | 12345678901234567890123456789012 | NULL | NULL | 0000-00-00 |
| 2 | name2 | 12345678901234567890123456789012 | 2013-01-01 | NULL | 0000-00-00 |
+------+--------+----------------------------------+------------+-------+------------+
2 rows in set (0.00 sec)
Oh, I see. The system automatically sets the value of date with a default value of 0000-00-00. Let me directly specify a default value to see:
mysql
>
alter table test add column birth2 date default '2013-1-1';
Query OK, 0 rows affected (0.28 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql
>
select * from test;
+------+--------+----------------------------------+------------+-------+------------+------------+
| t_id | t_name | t_password | t_birth | birth | birth1 | birth2 |
+------+--------+----------------------------------+------------+-------+------------+------------+
| 1 | name1 | 12345678901234567890123456789012 | NULL | NULL | 0000-00-00 | 2013-01-01 |
| 2 | name2 | 12345678901234567890123456789012 | 2013-01-01 | NULL | 0000-00-00 | 2013-01-01 |
+------+--------+----------------------------------+------------+-------+------------+------------+
2 rows in set (0.00 sec)
See, the birth2 field that will be added has a default value that we manually specified.
So much for adding a field to the table in MySQL. I hope it will be helpful to you. Thank you!