mysql主键不能有null而唯一键可以有重复的null值并且都不能有重复的空值

mysql主键不能有null而唯一键可以有重复的null值并且都不能重复的空值

root@e9be1791c2c1:/# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 33893
Server version: 5.7.32-log MySQL Community Server (GPL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

root@localhost: 08:48 [3306][(none)]>

root@localhost: 08:52 [3306][db_hlf]>help create ;
Many help items for your request exist.
To make a more specific request, please type 'help <item>',
where <item> is one of the following
topics:
   CREATE DATABASE
   CREATE EVENT
   CREATE FUNCTION
   CREATE FUNCTION UDF
   CREATE INDEX
   CREATE LOGFILE GROUP
   CREATE PROCEDURE
   CREATE SCHEMA
   CREATE SERVER
   CREATE TABLE
   CREATE TABLESPACE
   CREATE TRIGGER
   CREATE USER
   CREATE VIEW
   SHOW
   SHOW CREATE DATABASE
   SHOW CREATE EVENT
   SHOW CREATE FUNCTION
   SHOW CREATE PROCEDURE
   SHOW CREATE SCHEMA
   SHOW CREATE TABLE
   SHOW CREATE USER
   SPATIAL INDEXES

root@localhost: 08:52 [3306][db_hlf]>help create index;
Name: 'CREATE INDEX'
Description:
Syntax:
CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
    [index_type]
    ON tbl_name (key_part,...)
    [index_option]
    [algorithm_option | lock_option] ...

key_part:
    col_name [(length)] [ASC | DESC]

index_option: {
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'
}

index_type:
    USING {BTREE | HASH}

algorithm_option:
    ALGORITHM [=] {DEFAULT | INPLACE | COPY}

lock_option:
    LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE}

Normally, you create all indexes on a table at the time the table
itself is created with CREATE TABLE. See [HELP CREATE TABLE]. This
guideline is especially important for InnoDB tables, where the primary
key determines the physical layout of rows in the data file. CREATE
INDEX enables you to add indexes to existing tables.

CREATE INDEX is mapped to an ALTER TABLE statement to create indexes.
See [HELP ALTER TABLE]. CREATE INDEX cannot be used to create a PRIMARY
KEY; use ALTER TABLE instead. For more information about indexes, see
https://dev.mysql.com/doc/refman/5.7/en/mysql-indexes.html.

URL: https://dev.mysql.com/doc/refman/5.7/en/create-index.html

root@localhost: 08:53 [3306][db_hlf]>

root@localhost: 08:50 [3306][(none)]>create database db_hlf;
Query OK, 1 row affected (0.01 sec)

root@localhost: 08:50 [3306][(none)]>use db_hlf;
Database changed
root@localhost: 08:51 [3306][db_hlf]>

root@localhost: 09:02 [3306][db_hlf]>create table tbl_pri(name varchar(30) primary key, address varchar(50));
Query OK, 0 rows affected (0.02 sec)

root@localhost: 09:02 [3306][db_hlf]>show create table tbl_pri\G
*************************** 1. row ***************************
       Table: tbl_pri
Create Table: CREATE TABLE `tbl_pri` (
  `name` varchar(30) NOT NULL,
  `address` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

root@localhost: 09:02 [3306][db_hlf]>

root@localhost: 09:05 [3306][db_hlf]>insert into tbl_pri(address) values('chengdu');
ERROR 1364 (HY000): Field 'name' doesn't have a default value
root@localhost: 09:05 [3306][db_hlf]>insert into tbl_pri(name,address) values('xiaohong','chengdu');
Query OK, 1 row affected (0.01 sec)

root@localhost: 09:06 [3306][db_hlf]>insert into tbl_pri(name,address) values('xiaohong','beijing');
ERROR 1062 (23000): Duplicate entry 'xiaohong' for key 'PRIMARY'
root@localhost: 09:06 [3306][db_hlf]>insert into tbl_pri(name,address) values('','guiyang');
Query OK, 1 row affected (0.01 sec)

root@localhost: 09:06 [3306][db_hlf]>insert into tbl_pri(name,address) values('','guiyang');
ERROR 1062 (23000): Duplicate entry '' for key 'PRIMARY'
root@localhost: 09:06 [3306][db_hlf]>select * from tbl_pri;
+----------+---------+
| name     | address |
+----------+---------+
|          | guiyang |
| xiaohong | chengdu |
+----------+---------+
2 rows in set (0.00 sec)

root@localhost: 09:06 [3306][db_hlf]>

root@localhost: 08:50 [3306][db_hlf]>create table tbl_unq(id int(11) primary key,name varchar(30),address varchar(50));
Query OK, 0 rows affected (0.02 sec)

root@localhost: 08:53 [3306][db_hlf]>create unique index unq_name on tbl_unq(name);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@localhost: 08:54 [3306][db_hlf]>alter table tbl_unq add index idx_address(address);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@localhost: 08:54 [3306][db_hlf]>show create table tbl_unq\G
*************************** 1. row ***************************
       Table: tbl_unq
Create Table: CREATE TABLE `tbl_unq` (
  `id` int(11) NOT NULL,
  `name` varchar(30) DEFAULT NULL,
  `address` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unq_name` (`name`),
  KEY `idx_address` (`address`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

root@localhost: 08:54 [3306][db_hlf]>insert into tbl_unq values(1);
ERROR 1136 (21S01): Column count doesn't match value count at row 1
root@localhost: 08:55 [3306][db_hlf]>insert into tbl_unq(id) values(1);
Query OK, 1 row affected (0.01 sec)

root@localhost: 08:55 [3306][db_hlf]>insert into tbl_unq(id) values(2);
Query OK, 1 row affected (0.01 sec)

root@localhost: 08:56 [3306][db_hlf]>insert into tbl_unq(id,name) values(3,'zhangsan');
Query OK, 1 row affected (0.00 sec)

root@localhost: 08:56 [3306][db_hlf]>insert into tbl_unq(id,name) values(3,'');
ERROR 1062 (23000): Duplicate entry '3' for key 'PRIMARY'
root@localhost: 08:56 [3306][db_hlf]>insert into tbl_unq(id,name) values(4,'');
Query OK, 1 row affected (0.01 sec)

root@localhost: 08:56 [3306][db_hlf]>insert into tbl_unq(id,name) values(4,'');
ERROR 1062 (23000): Duplicate entry '4' for key 'PRIMARY'
root@localhost: 08:56 [3306][db_hlf]>insert into tbl_unq(id,name) values(5,'');
ERROR 1062 (23000): Duplicate entry '' for key 'unq_name'
root@localhost: 08:56 [3306][db_hlf]>insert into tbl_unq(id,name) values(5,'mazi');
Query OK, 1 row affected (0.00 sec)

root@localhost: 08:56 [3306][db_hlf]>insert into tbl_unq(id,name) values(6,'lisi','chengdu');
ERROR 1136 (21S01): Column count doesn't match value count at row 1
root@localhost: 08:57 [3306][db_hlf]>insert into tbl_unq(id,name,address) values(6,'lisi','chengdu');
Query OK, 1 row affected (0.01 sec)

root@localhost: 08:57 [3306][db_hlf]>insert into tbl_unq values(7,'','jiangyang');
ERROR 1062 (23000): Duplicate entry '' for key 'unq_name'
root@localhost: 08:57 [3306][db_hlf]>insert into tbl_unq values(7,'wanger','');
Query OK, 1 row affected (0.01 sec)

root@localhost: 08:57 [3306][db_hlf]>insert into tbl_unq values(8,'hlf','');
Query OK, 1 row affected (0.01 sec)

root@localhost: 08:58 [3306][db_hlf]>select * from tbl_unq;
+----+----------+---------+
| id | name     | address |
+----+----------+---------+
|  1 | NULL     | NULL    |
|  2 | NULL     | NULL    |
|  3 | zhangsan | NULL    |
|  4 |          | NULL    |
|  5 | mazi     | NULL    |
|  6 | lisi     | chengdu |
|  7 | wanger   |         |
|  8 | hlf      |         |
+----+----------+---------+
8 rows in set (0.00 sec)

root@localhost: 08:58 [3306][db_hlf]>