如何理解mysql自增長(zhǎng)列

本篇文章給大家分享的是有關(guān)如何理解mysql自增長(zhǎng)列，小編覺得挺實(shí)用的，因此分享給大家學(xué)習(xí)，希望大家閱讀完這篇文章后可以有所收獲，話不多說，跟著小編一起來看看吧。

自增長(zhǎng)列必須是索引列，否則無法創(chuàng)建成功表，對(duì)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]> 


創(chuàng)建成功后id列沒有插入數(shù)據(jù)，但是可以自動(dòng)增長(zhǎng)
(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)




刪除表里的數(shù)據(jù)，在插入數(shù)據(jù)id列會(huì)依據(jù)原來的值繼續(xù)增長(zhǎng)
(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 表里的數(shù)據(jù)后在插入數(shù)據(jù)，id列會(huì)從1開始增長(zhǎng)。
(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]> 




對(duì)于復(fù)合索引的自增長(zhǎng)列
myisam引擎的自增長(zhǎng)列，在索引中是非前導(dǎo)列可以創(chuàng)建成功
innodb引擎的自增長(zhǎng)列，在索引中必須是前導(dǎo)列，表才能創(chuàng)建成功


(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自增長(zhǎng)列，小編相信有部分知識(shí)點(diǎn)可能是我們?nèi)粘９ぷ鲿?huì)見到或用到的。希望你能通過這篇文章學(xué)到更多知識(shí)。更多詳情敬請(qǐng)關(guān)注億速云行業(yè)資訊頻道。