• mysql 约束条件 unique key 唯一的键


    如果不设置unique

    会出现两条相同的记录

    mysql> create table department1(id int,name varchar(16));
    Query OK, 0 rows affected (0.01 sec)
    
    
    mysql> insert into department1 values(1 ,'mike'),(2,'mike');
    Query OK, 2 rows affected (0.01 sec)
    Records: 2  Duplicates: 0  Warnings: 0
    
    mysql> select * from department1;
    +------+------+
    | id   | name |
    +------+------+
    |    1 | mike |
    |    2 | mike |
    +------+------+
    2 rows in set (0.00 sec)
    mysql> desc department1 ;
    +-------+-------------+------+-----+---------+-------+
    | Field | Type        | Null | Key | Default | Extra |
    +-------+-------------+------+-----+---------+-------+
    | id    | int(11)     | YES  |     | NULL    |       |
    | name  | varchar(16) | YES  | UNI | NULL    |       |
    +-------+-------------+------+-----+---------+-------+
    2 rows in set (0.00 sec)
    ============设置唯一约束 UNIQUE===============


    不能插入相同记录

    方式一:
    mysql> create table department1(id int,name varchar(16) unique);
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> insert into department1 values(1 ,'mike'),(2,'mike');
    ERROR 1062 (23000): Duplicate entry 'mike' for key 'name'

    方式二:

    mysql> create table department1(id int,name varchar(16),unique(id),unique(name));
    Query OK, 0 rows affected (0.02 sec)
    
    mysql> desc department1;
    +-------+-------------+------+-----+---------+-------+
    | Field | Type        | Null | Key | Default | Extra |
    +-------+-------------+------+-----+---------+-------+
    | id    | int(11)     | YES  | UNI | NULL    |       |
    | name  | varchar(16) | YES  | UNI | NULL    |       |
    +-------+-------------+------+-----+---------+-------+
    2 rows in set (0.00 sec)
    这两种方法都叫单列唯一 针对一个字段设置唯一性


    还有一种 联合唯一
    几个字段合到一起不重复就可以


    联合唯一
    unique(ip,port)
    desc 看到有 MUL 就是联合唯一
    mysql> create table services(id int,ip char(16),port int,unique(id),unique(ip,port));
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> desc services;
    +-------+----------+------+-----+---------+-------+
    | Field | Type     | Null | Key | Default | Extra |
    +-------+----------+------+-----+---------+-------+
    | id    | int(11)  | YES  | UNI | NULL    |       |
    | ip    | char(16) | YES  | MUL | NULL    |       |
    | port  | int(11)  | YES  |     | NULL    |       |
    +-------+----------+------+-----+---------+-------+
    3 rows in set (0.00 sec)
    
    
    

    验证 插入记录

    mysql> insert into services values 
        -> (1,'192.168.10.11',80),
        -> (2,'192.168.10.11',81),
        -> (3,'192.168.10.10',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.11 |   80 |
    |    2 | 192.168.10.11 |   81 |
    |    3 | 192.168.10.10 |   80 |
    +------+---------------+------+
    3 rows in set (0.00 sec)

    再插入一条原本有的记录 报错了

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


  • 相关阅读:
    HTML基础 整理
    今天课堂总结
    课后习题
    JAVA的文件创建
    JAVA_输入输出流 异常处理
    12.23流水账号
    12.22 repeater 删除
    12.22 repeater 修改
    12.22 repeater 添加
    12.22 repeater 主页
  • 原文地址:https://www.cnblogs.com/mingerlcm/p/9811026.html
Copyright © 2020-2023  润新知