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]>
