MySQL 列选项

table schema 由列定义和其他东西组成，一个列定义由
列名，数据类型，空指定，以及完整性约束组成，也可为每个列定义添加几个选项。

语法

<column definition> ::=
    <column name> <data type> [ <null specification> ]
    [ <column integrity constraint> ] [ <column option>... ]

<column option> ::=
    DEFAULT <literal> |
    COMMENT <alphanumeric literal>

列选项–DEFAULT
当一个新行添加到表中且没有指定值，就使用这个默认值。
text,blob,geometric 不能指定默认值。

创建 penalties 表，amount 默认值是50，payment_date 默认值1990/1/1，并插入两行

CREATE TABLE PENALTIES(
    PAYMENTNO INTEGER NOT NULL PRIMARY KEY,
    PLAYERNO INTEGER NOT NULL,
    PAYMENT_DATE DATE NOT NULL DEFAULT '1990-01-01',
    AMOUNT DECIMAL(7,2) NOT NULL DEFAULT 50.00
);

INSERT INTO PENALTIES(PAYMENTNO, PLAYERNO)VALUES (15, 27);
INSERT INTO PENALTIES(PAYMENTNO, PLAYERNO, PAYMENT_DATE, AMOUNT)VALUES (16, 27, DEFAULT, DEFAULT)

mysql> SELECT * from penalties;
+-----------+----------+--------------+--------+
| PAYMENTNO | PLAYERNO | PAYMENT_DATE | AMOUNT |
+-----------+----------+--------------+--------+
|        15 |       27 | 1990-01-01   |  50.00 |
|        16 |       27 | 1990-01-01   |  50.00 |
+-----------+----------+--------------+--------+
2 rows in set (0.01 sec)

把所有罚款更新为默认值

UPDATE PENALTIES SET AMOUNT = DEFAULT;

把所有罚款年份替换为payment_date 默认值乘10

UPDATE PENALTIES    SET AMOUNT = YEAR(DEFAULT(PAYMENT_DATE))*10

列选项–COMMENT

CREATE TABLE PENALTIES(
PAYMENTNO INTEGER NOT NULL PRIMARY KEY  COMMENT 'Primary key of the table',
PLAYERNO INTEGER NOT NULL COMMENT 'Player who has incurred the penalty',
PAYMENT_DATE DATE NOT NULL COMMENT 'Date on which the penalty has been paid',
AMOUNT DECIMAL(7,2) NOT NULL COMMENT 'Amount of the penalty in dollars'
)