mysql自动编号_MySQL自动编号与主键

1、自动编号(AUTO_INCREMENT)，必须与主键组合使用

默认情况下，起始值为1，增量也为1。

2、主键(PRIMARY KEY)

每张数据表只能存在一个主键

主键保证记录的唯一性

主键自动为NOT NULL

3、AUTO_INCREMENT必须与主键一起使用，主键不一定必须与AUTO_INCREMENT一起使用。

mysql> CREATE TABLE tb3(

-> id SMALLINT UNSIGNEDAUTO_INCREMENT PRIMARY KEY,

-> username VARCHAR(30) NOT NULL

-> );

Query OK, 0 rows affected (0.35 sec)

mysql>

mysql> SHOW COLUMNS FROM tb3; #查看数据表结构

+----------+----------------------+------+-----+---------+----------------+

| Field        | Type                       | Null | Key | Default   |       Extra          |

+----------+----------------------+------+-----+---------+----------------+

| id            | smallint(5) unsigned | NO  | PRI  | NULL      | auto_increment |

| username | varchar(30)             | NO  |        | NULL     |

+----------+----------------------+------+-----+---------+----------------+

2 rows in set (0.14 sec)

mysql> INSERT tb3(useRname) VALUES('JOHN');

Query OK, 1 row affected (0.04 sec)

mysql> INSERT tb3(useRname) VALUES('Tom');

Query OK, 1 row affected (0.04 sec)

mysql> INSERT tb3(useRname) VALUES('Rose');

Query OK, 1 row affected (0.02 sec)

mysql> INSERT tb3(useRname) VALUES('Dimi tar');

Query OK, 1 row affected (0.04 sec)

mysql> SELECT * FROM tb3;

+----+----------+

| id | username |

+----+----------+

| 1  | JOHN       |

| 2  | Tom        |

| 3  | Rose        |

| 4  | Dimi tar    |

+----+----------+

4、创建一张表只有主键，没有AUTO_INCREMENT

mysql> CREATE TABLE tb4(

-> id SMALLINT UNSIGNED PRIMARY KEY,

-> usename VARCHAR(20) NOT NULL

-> );

Query OK, 0 rows affected (0.22 sec)

mysql>

mysql> SHOW COLUMNS FROM tb4;

+---------+----------------------+------+-----+---------+-------+

| Field       | Type                        | Null    | Key | Default   | Extra |

+---------+----------------------+------+-----+---------+-------+

|id         | smallint(5) unsigned  | NO     | PRI | NULL      |          |

| usename | varchar(20)              | NO     |       | NULL      |          |

+---------+----------------------+------+-----+---------+-------+

2 rows in set (0.07 sec)

主键允许赋值，但不允许存在两个相同的值

mysql> INSERT tb4 VALUES(4, 'Tom');

Query OK, 1 row affected (0.03 sec)

mysql> INSERT tb4 VALUES(22, 'Tom');

Query OK, 1 row affected (0.05 sec)

mysql> SELECT * FROM tb4;

+----+---------+

| id    | usename|

+----+---------+

| 4    | Tom       |

|22  | Tom       |

+----+---------+

2 rows in set (0.00 sec)

mysql> INSERT tb4 VALUES(22, 'Li');

ERROR 1062 (23000): Duplicate entry '22' for key 'PRIMARY'