This article introduces how to use SQL statements to add a new field to an existing table in MySQL, including setting whether the field is null or the default value. let's take a look at the simplest example. in test, add a field named birth and of the date type.
mysql> alter table test add column birth date;Query OK, 0 rows affected (0.36 sec)Records: 0 Duplicates: 0 Warnings: 0
Query the 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)
The result shows that the inserted birth field is empty by default. Let's try again. add a birth1 field and set it to be empty.
mysql> alter table test add column birth1 date not null;Query OK, 0 rows affected (0.16 sec)Records: 0 Duplicates: 0 Warnings: 0
The execution was successful !? Unexpected! I thought this statement would not succeed because I didn't specify a default value for it. Let's take a 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)
The system automatically sets the default value of the date type: 0000-00-00. Next, let's directly specify a default value:
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: 0mysql> 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)
If you do not see it, there will be a default value for the added bir2's field, and this default value is manually specified.
The above section adds a field (field name, whether it is blank, default value) _ MySQL to the table in the MySQL command line. For more information, see PHP Chinese network (www.php1.cn )!