约束条件一
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 services\G *************************** 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)
