Add a field (field name, whether it is blank, default value) to the table in the MySQL command line _ MySQL

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 )!