mysql 主键自增
程序员文章站
2022-04-21 10:22:44
...
#表中主键的定义:
mysql> create table t12 (
-> id int primary key,
-> name char(2)
-> );
#表中插入数据:
mysql> create table t13 ( id int, name char(2), primary key(id) );
Query OK, 0 rows affected (0.01 sec)
查看两张表的表结构.
mysql> desc t12;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | char(2) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> desc t13
-> ;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | NO | PRI | 0 | |
| name | char(2) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
#注意primary key是唯一不允许重复的.
mysql> insert into t12 values (3,"aa");
Query OK, 1 row affected (0.01 sec)
mysql> insert into t12 values (3,"bb");
ERROR 1062 (23000): Duplicate entry '3' for key 'PRIMARY'
主键的自增(aotu_increment):
mysql> create table t14 ( id int auto_increment, name char(4) );
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
报错提示:表中只能有1个自增长的key.
auto_increment列只能有1列为auto_increment,且此列必须加索引(index/key).
mysql> create table t14 ( id int auto_increment, name char(4),key id(id) );
Query OK, 0 rows affected (0.02 sec)
在t14表中name列插入两条数据,查看primary key(id)是否自增.
mysql> insert into t14 (name) values ("lisi");
Query OK, 1 row affected (0.00 sec)
mysql> insert into t14 (name) values ("zhan");
Query OK, 1 row affected (0.00 sec)
mysql> select * from t14;
+----+------+
| id | name |
+----+------+
| 1 | lisi |
| 2 | zhan |
+----+------+
2 rows in set (0.00 sec)
最常见的主键的自增建表写法.
mysql> create table t15 (
-> id int primary key auto_increment,
-> name char(10)
-> );
Query OK, 0 rows affected (0.01 sec)
转载于:https://blog.51cto.com/215687833/2350195