约束条件一

Null，Default

不允许为空，传值为空则默认写入male

mysql> create table t1(
    -> id int,
    -> name char(6),
    -> sex enum('male','female') not null default 'male'
    -> );

 查看表结构

mysql> desc t1;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type                  | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id    | int(11)               | YES  |     | NULL    |       |
| name  | char(6)               | YES  |     | NULL    |       |
| sex   | enum('male','female') | NO   |     | male    |       |
+-------+-----------------------+------+-----+---------+-------+

 性别传空值，默认设置成了male

mysql> insert into t1(id,name) values(1,'ya'); Query OK, 1 row affected (0.00 sec) mysql> select * from t1; +------+--------+------+ | id | name | sex | +------+--------+------+ | 1 | ya | male | +------+--------+------+ 1 row in set (0.00 sec)

 unique限制数据唯一性

单列唯一

方式一：

mysql> create table t2(
    -> id int,
    -> name char(10) unique);
Query OK, 0 rows affected (0.01 sec)

mysql> desc t2;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | YES  |     | NULL    |       |
| name  | char(10) | YES  | UNI | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.01 sec)

 方式二：

mysql> create table department( id int, name char(10), unique(name));
Query OK, 0 rows affected (0.01 sec)

mysql> desc department;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | YES  |     | NULL    |       |
| name  | char(10) | YES  | UNI | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

 联合唯一

多列在一起保证唯一

mysql> create table services(
    -> id int,
    -> ip char(15),
    -> port int,
    -> unique(ip,port),
    -> unique(id));

mysql> desc services;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | YES  | UNI | NULL    |       |
| ip    | char(15) | YES  | MUL | NULL    |       |
| port  | int(11)  | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

 同时插入多行数据

mysql> insert into services values (1,'192.168.10.1',80),(2,'192.168.10.1',81),(3,'192.168.10.2',80);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from services;
+------+-----------------+------+
| id   | ip              | port |
+------+-----------------+------+
|    1 | 192.168.10.1    |   80 |
|    2 | 192.168.10.1    |   81 |
|    3 | 192.168.10.2    |   80 |
+------+-----------------+------+
3 rows in set (0.00 sec)

 插入多列不唯一时会报错

mysql> insert into services values (4,'192.168.10.1',80);
ERROR 1062 (23000): Duplicate entry '192.168.10.1   -80' for key 'ip'

 pirmary key

约束：不为空，且唯一

存储引擎：Innodb，一张表内必须有一个主键

show create table servicesG
*************************** 1. row ***************************
       Table: services
Create Table: CREATE TABLE `services` (
  `id` int(11) DEFAULT NULL,
  `ip` char(15) DEFAULT NULL,
  `port` int(11) DEFAULT NULL,
  UNIQUE KEY `ip` (`ip`,`port`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

 单列主键

mysql> create table t17(
    -> id int primary key,
    -> name char(16));
Query OK, 0 rows affected (0.01 sec)

mysql> desc t17;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | NO   | PRI | NULL    |       |
| name  | char(16) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

 primary key重复

mysql> insert into t17 values(1,'huangya');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

 复合主键

mysql> create table t18(
    -> ip char(15),
    -> port int,
    -> primary key(ip,port));
Query OK, 0 rows affected (0.01 sec)

mysql> desc t18;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| ip    | char(15) | NO   | PRI | NULL    |       |
| port  | int(11)  | NO   | PRI | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

 插入多列重复值报错

mysql> insert into t18 values('10.10.10.1',80),('10.10.10.1',80);
ERROR 1062 (23000): Duplicate entry '10.10.10.1     -80' for key 'PRIMARY'
mysql>

 auto_increment，自增长

mysql> create table t20(
    -> id int primary key auto_increment,
    -> name char(15)
    -> );

仅传入name字段即可，也可手动传入，只要保证不为空且唯一即可。

mysql> insert into t20(name) values('huang'),('yaya'),('m');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from t20;
+----+-----------------+
| id | name            |
+----+-----------------+
|  1 | huang           |
|  2 | huang           |
|  3 | yaya            |
|  4 | m               |
+----+-----------------+
4 rows in set (0.00 sec)

 自增长默认从1开始增长，步长为1

mysql> show variables like 'auto_inc%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 1     |
| auto_increment_offset    | 1     |
+--------------------------+-------+
2 rows in set (0.00 sec)

mysql>

 手动调整布长

会话级别调整，仅本次会话有效

mysql> set session auto_increment_increment=5;
Query OK, 0 rows affected (0.00 sec)

 全局设置步长

mysql> set global auto_increment_increment=5;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'auto_inc%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 5     |
| auto_increment_offset    | 1     |
+--------------------------+-------+
2 rows in set (0.00 sec)

起始偏移量设置，一定要小于等于步长，否则设置失效。

mysql> set session auto_increment_offset=2;
Query OK, 0 rows affected (0.00 sec)

mysql> set global auto_increment_offset=2;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'auto_inc%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 5     |
| auto_increment_offset    | 2     |
+--------------------------+-------+
2 rows in set (0.01 sec)