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

Source: Internet
Author: User
Tags how to use sql
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 )!

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.