为新纪录自动生成唯一的ID
必须联合主键使用,一个表只允许一个字段使用,只能应用于整数类型列,增量为1。
CREATE TABLE tbl_name(
字段名称 字段类型 AUTO_INCREMENT
);
mysql> CREATE TABLE testAT(
-> id INT AUTO_INCREMENT PRIMARY KEY,
-> username VARCHAR(20)
-> );
Query OK, 0 rows affected (0.35 sec)
mysql> DESC testAT;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| username | varchar(20) | YES | | NULL | |
+----------+-------------+------+-----+---------+----------------+
2 rows in set (0.07 sec)
-> id INT AUTO_INCREMENT PRIMARY KEY,
-> username VARCHAR(20)
-> );
Query OK, 0 rows affected (0.35 sec)
mysql> DESC testAT;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| username | varchar(20) | YES | | NULL | |
+----------+-------------+------+-----+---------+----------------+
2 rows in set (0.07 sec)
mysql> INSERT testAT(id,username) VALUES(1,'AAA');
mysql> INSERT testAT(username) VALUES('BBB');
mysql> INSERT testAT(id,username) VALUES(11,'111AAA');
mysql> INSERT testAT(username) VALUES('2222BBB');
mysql> INSERT testAT(username) VALUES('BBB');
mysql> INSERT testAT(id,username) VALUES(11,'111AAA');
mysql> INSERT testAT(username) VALUES('2222BBB');
mysql> INSERT testAT(id,username) VALUES(5,'555AAA');
mysql> select * from testAT;
+----+----------+
| id | username |
+----+----------+
| 1 | AAA |
| 2 | BBB |
mysql> select * from testAT;
+----+----------+
| id | username |
+----+----------+
| 1 | AAA |
| 2 | BBB |
| 5 | 555AAA |
| 11 | 111AAA |
| 12 | 2222BBB |
+----+----------+
| 11 | 111AAA |
| 12 | 2222BBB |
+----+----------+
mysql> SHOW CREATE TABLE testAT;
CREATE TABLE `testat` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8 | --可以看到此时的AUTO_INCREMENT为13
CREATE TABLE `testat` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8 | --可以看到此时的AUTO_INCREMENT为13