自动编号必须和主键组合使用;在默认情况下,起始值为1,每次的增量为1;
it must be defined as a key ;
报错为自动编号的字段必须定义为主键;
主键自动为NOT NULL 主键约束 保证记录的唯一性 每个数据表只能存在一个主键 ;
AUTO_INCREMENT必须和主键组合使用 但是主键未必和AUTO_INCREMENT相即使用 ;
mysql> CREATE TABLE five(
-> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
-> username VARCHAR(30) NOT NULL
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> SHOW COLUMNS FROM five; Extra字段无需赋值;
+----------+----------------------+------+-----+---------+----------------+
| 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.03 sec)
mysql> INSERT five(username)VALUES('Tom'); 给部分字段赋值 不能省去小括号;
Query OK, 1 row affected (0.00 sec)
mysql> INSERT five(username)VALUES('Daming');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT five(username)VALUES('Feiyu');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT five(username)VALUES('Jean');
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM five;
+----+----------+
| id | username |
+----+----------+ AUTO_INCREMENT必须定义为主键 相反主键未必和AUTO_INCREMENT相即使用 ;
| 1 | Tom |
| 2 | Daming |
| 3 | Feiyu |
| 4 | Jean |
+----+----------+
4 rows in set (0.00 sec)
mysql> CREATE TABLE six(
-> id SMALLINT UNSIGNED PRIMARY KEY,
-> username VARCHAR(30) NOT NULL
-> );
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW COLUMNS FROM six;
+----------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| id | smallint(5) unsigned | NO | PRI | NULL | |
| username | varchar(30) | NO | | NULL | |
+----------+----------------------+------+-----+---------+-------+
2 rows in set (0.02 sec)
mysql> INSERT six VALUES(20,'Feiyu');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT six VALUES(2,'Feiyu');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT six VALUES(21,'Feiyu');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM six; 主键字段允许赋值;
+----+----------+
| id | username |
+----+----------+
| 20 | Feiyu |
| 2 | Feiyu |
| 21 | Feiyu |
+----+----------+
3 rows in set (0.00 sec)
mysql> INSERT six VALUES(21,'Feiyu');
ERROR 1062 (23000): Duplicate entry '21' for key 'PRIMARY'
mysql> Duplicate entry报错为不能 使用重复id
可以明白AUTO_INCREMENT必须和PRIMARY一同使用 相反PRIMARY 不一定和 AUTO_INCREMENT一起使用;