加入收藏 | 设为首页 | 会员中心 | 我要投稿 银川站长网 (https://www.0951zz.com/)- 云通信、基础存储、云上网络、机器学习、视觉智能!
当前位置: 首页 > 站长学院 > MySql教程 > 正文

如何了解mysql自增长列

发布时间:2023-07-26 13:10:49 所属栏目:MySql教程 来源:
导读:本篇文章给大家分享的是有关如何理解mysql自增长列,小编觉得挺实用的,因此分享给大家学习,希望大家阅读完这篇文章后可以有所收获,话不多说,跟着小编一起来看看吧。自增长列必须是索引列,否则无法创建成功表,对

本篇文章给大家分享的是有关如何理解mysql自增长列,小编觉得挺实用的,因此分享给大家学习,希望大家阅读完这篇文章后可以有所收获,话不多说,跟着小编一起来看看吧。

自增长列必须是索引列,否则无法创建成功表,对myisma和innodb都一样

(localhost@testdb)[root]> create table test5 (id int auto_increment,name varchar(10)) engine=innodb;

ERROR 1075 (42000): 

(localhost@testdb)[root]> 

(localhost@testdb)[root]> 

(localhost@testdb)[root]> create table test5 (id int auto_increment,name varchar(10),index(id)) engine=innodb;

Query OK, 0 rows affected (0.01 sec)

(localhost@testdb)[root]> create table test5 (id int auto_increment,name varchar(10)) engine=myisam;

ERROR 1075 (42000): 

(localhost@testdb)[root]> create table test5 (id int auto_increment,name varchar(10),index(id)) engine=myisam;

Query OK, 0 rows affected (0.00 sec)

(localhost@testdb)[root]> 

(localhost@testdb)[root]> 

(localhost@testdb)[root]> 

创建成功后id列没有插入数据,但是可以自动增长

(localhost@testdb)[root]> insert into test5(name) values('aa'),('bb'),('cc');

Query OK, 3 rows affected (0.00 sec)

(localhost@testdb)[root]> select * from test5;

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

| id | name |

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

| 1 | aa |

| 2 | bb |

| 3 | cc |

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

3 rows in set (0.00 sec)

索引

(localhost@testdb)[root]> (localhost@testdb)[root]> show index from test5;

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

| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

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

| test5 |     1 | id   |      1 | id     | A    |    NULL |  NULL | NULL |   | BTREE   |    |       |

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

1 row in set (0.00 sec)

删除表里的数据,在插入数据id列会依据原来的值继续增长

(localhost@testdb)[root]> delete from test5;

Query OK, 3 rows affected (0.00 sec)

(localhost@testdb)[root]> 

(localhost@testdb)[root]> 

(localhost@testdb)[root]> select * from test5;

Empty set (0.00 sec)

(localhost@testdb)[root]> insert into test5(name) values('aa'),('bb'),('cc');

Query OK, 3 rows affected (0.00 sec)

(localhost@testdb)[root]> select * from test5;

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

| id | name |

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

| 4 | aa |

| 5 | bb |

| 6 | cc |

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

3 rows in set (0.00 sec)

truncate 表里的数据后在插入数据,id列会从1开始增长。

(localhost@testdb)[root]> truncate table test5;

Query OK, 0 rows affected (0.00 sec)

(localhost@testdb)[root]> select * from test5;

Empty set (0.00 sec)

(localhost@testdb)[root]> insert into test5(name) values('aa'),('bb'),('cc');

Query OK, 3 rows affected (0.00 sec)

(localhost@testdb)[root]> select * from test5;

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

| id | name |

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

| 1 | aa |

| 2 | bb |

| 3 | cc |

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

3 rows in set (0.00 sec)

(localhost@testdb)[root]> 

对于复合索引的自增长列

myisam引擎的自增长列,在索引中是非前导列可以创建成功

innodb引擎的自增长列,在索引中必须是前导列,表才能创建成功

(localhost@testdb)[root]> create table test4 (id1 int auto_increment,id2 int,name varchar(10),index(id2,id1)) engine=myisam;

Query OK, 0 rows affected (0.00 sec)

(localhost@testdb)[root]> 

(localhost@testdb)[root]> 

(localhost@testdb)[root]> drop table test4;

Query OK, 0 rows affected (0.00 sec)

(localhost@testdb)[root]> create table test4 (id1 int auto_increment,id2 int,name varchar(10),index(id2,id1)) engine=innodb;

ERROR 1075 (42000): 

(localhost@testdb)[root]> 

(localhost@testdb)[root]> 

(localhost@testdb)[root]> create table test4 (id1 int auto_increment,id2 int,name varchar(10),index(id1,id2)) engine=innodb;

Query OK, 0 rows affected (0.01 sec)

(localhost@testdb)[root]> 

(localhost@testdb)[root]> 

(localhost@testdb)[root]> 

以上就是如何理解mysql自增长列,小编相信有部分知识点可能是我们日常工作会见到或用到的。

(编辑:银川站长网)

【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!

    推荐文章