Skip to main content

MySQL默认约束的实例说明

MySQL默认约束简介

MySQL默认约束允许您为列指定默认值。以下是默认约束的语法:

column_name data_type DEFAULT default_value;

在此语法中,指定默认关键字,后跟列的默认值。默认值的类型与列的数据类型匹配。

默认_值必须是文字常量,例如数字或字符串。它不能是函数或表达式。但是,MySQL允许您将当前日期和时间(current_TIMESTAMP)设置为TIMESTAMP和DATETIME列。

定义不带NOT NULL约束的列时,该列将隐式地将NULL作为默认值。

如果列有默认约束,而INSERT或UPDATE语句没有为该列提供值,MySQL将使用默认约束中指定的默认值。

通常,在创建表时为列设置默认约束。MySQL还允许向现有表的列添加默认约束。如果不想对列使用默认值,可以删除默认约束。

MySQL默认约束示例

以下示例创建了一个名为cart_items的新表,其中包含四列item_id、name、quantity和sales_tax:

CREATE TABLE cart_items 
(
item_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
quantity INT NOT NULL,
price DEC(5,2) NOT NULL,
sales_tax DEC(5,2) NOT NULL DEFAULT 0.1,
CHECK(quantity > 0),
CHECK(sales_tax >= 0)
);

“销售税”列的默认值为0.1(10%)。下面的语句显示cart_items表:

DESC cart_items;

输出:

+-----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+----------------+
| item_id | int | NO | PRI | NULL | auto_increment |
| name | varchar(255) | NO | | NULL | |
| quantity | int | NO | | NULL | |
| price | decimal(5,2) | NO | | NULL | |
| sales_tax | decimal(5,2) | NO | | 0.10 | |
+-----------+--------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)

下面的INSERT语句将一个新项目添加到cart_items表中:

INSERT INTO cart_items(name, quantity, price)
VALUES('Keyboard', 1, 50);

在本例中,INSERT语句不为sales_tax列提供值。sales_tax列使用默认约束中指定的默认值:

SELECT * FROM cart_items;

输出:

+---------+----------+----------+-------+-----------+
| item_id | name | quantity | price | sales_tax |
+---------+----------+----------+-------+-----------+
| 1 | Keyboard | 1 | 50.00 | 0.10 |
+---------+----------+----------+-------+-----------+
1 row in set (0.00 sec)

此外,在cart_items表中插入新行时,可以显式使用默认关键字:

INSERT INTO cart_items(name, quantity, price, sales_tax)
VALUES('Battery',4, 0.25 , DEFAULT);

在这种情况下,sales_tax列采用默认值:

SELECT * FROM cart_items;

输出:

+---------+----------+----------+-------+-----------+
| item_id | name | quantity | price | sales_tax |
+---------+----------+----------+-------+-----------+
| 1 | Keyboard | 1 | 50.00 | 0.10 |
| 2 | Battery | 4 | 0.25 | 0.10 |
+---------+----------+----------+-------+-----------+
2 rows in set (0.01 sec)

向列添加默认约束

要向现有表的列添加默认约束,请使用ALTER table语句:

ALTER TABLE table_name
ALTER COLUMN column_name SET DEFAULT default_value;

以下示例将默认约束添加到购物车列表的quantity列:

ALTER TABLE cart_items
ALTER COLUMN quantity SET DEFAULT 1;

如果描述cart_items表,您将看到以下更改:

DESC cart_items;

输出:

+-----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+----------------+
| item_id | int | NO | PRI | NULL | auto_increment |
| name | varchar(255) | NO | | NULL | |
| quantity | int | NO | | 1 | |
| price | decimal(5,2) | NO | | NULL | |
| sales_tax | decimal(5,2) | NO | | 0.10 | |
+-----------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

以下语句在cart_items表中插入新行,而不指定quantity列的值:

INSERT INTO cart_items(name, price, sales_tax)
VALUES('Maintenance services',25.99, 0)

“数量”列的值默认为1:

SELECT * FROM cart_items;

输出:

+---------+----------------------+----------+-------+-----------+
| item_id | name | quantity | price | sales_tax |
+---------+----------------------+----------+-------+-----------+
| 1 | Keyboard | 1 | 50.00 | 0.10 |
| 2 | Battery | 4 | 0.25 | 0.10 |
| 3 | Maintenance services | 1 | 25.99 | 0.00 |
+---------+----------------------+----------+-------+-----------+
3 rows in set (0.00 sec)

从列中删除默认约束

要从列中删除默认约束,请使用ALTER TABLE语句:

ALTER TABLE table_name
ALTER column_name DROP DEFAULT;

以下示例从cart_items表的quantity列中删除默认约束:

ALTER TABLE cart_items
ALTER COLUMN quantity DROP DEFAULT;

以下是新的购物车商品结构:

DESC cart_items;

输出:

+-----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+----------------+
| item_id | int | NO | PRI | NULL | auto_increment |
| name | varchar(255) | NO | | NULL | |
| quantity | int | NO | | NULL | |
| price | decimal(5,2) | NO | | NULL | |
| sales_tax | decimal(5,2) | NO | | 0.10 | |
+-----------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

总结