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!


Related articles: