MySQL Tutorial/Table/Column Modifiers

Column Modifiers

Modifier Name Applicable Types AUTO_INCREMENT All INT Types BINARY CHAR, VARCHAR DEFAULT All, except BLOB, TEXT NOT NULL All Types NULL All Types PRIMARY KEY All Types UNIQUE All Types UNSIGNED Numeric Types ZEROFILL Numeric Types

The BINARY modifier causes the values to treated as binary strings, making them case sensitive.

The DEFAULT modifier specifies the default value.

The MySQL default value is NULL for all types except ENUM.

For ENUM, MySQL uses the first value of the enumerated list as the default.

For SET types, MySQL uses the empty string for the default.

To specify a DEFAULT value, use the following syntax:

mysql>
mysql> CREATE TABLE Test(State char(2) NOT NULL DEFAULT "KY");
Query OK, 0 rows affected (0.02 sec)
mysql>
mysql> insert into Test (state) value (default);
Query OK, 1 row affected (0.02 sec)
mysql>
mysql> select * from Test;
+-------+
| State |
+-------+
| KY    |
+-------+
1 row in set (0.00 sec)
mysql>
mysql> drop table Test;
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql>