MySQL Renumbering an Existing Sequence

 

看到這個段落時
我好訝異
因為我昨天才剛看過SQL SERVER Internal的介紹
有primary key的欄位是沒辦法drop的
禁不住開啟VM來做一下測試
先建表,塞資料
# insect.sql
# This requires the multi-row INSERT syntax
DROP TABLE IF EXISTS insect;
#@ _CREATE_TABLE_FULL_
#@ _CREATE_TABLE_PART_1_
CREATE TABLE insect
(
#@ _CREATE_TABLE_PART_1_
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id),
#@ _CREATE_TABLE_PART_2_
name VARCHAR(30) NOT NULL, # type of insect
date DATE NOT NULL, # date collected
origin VARCHAR(30) NOT NULL # where collected
);
#@ _CREATE_TABLE_PART_2_
#@ _CREATE_TABLE_FULL_
#@ _INSERT_EXPLICIT_
INSERT INTO insect (id,name,date,origin) VALUES
(NULL,'housefly','2014-09-10','kitchen'),
(NULL,'millipede','2014-09-10','driveway'),
(NULL,'grasshopper','2014-09-10','front yard'),
(NULL,'stink bug','2014-09-10','front yard');
#@ _INSERT_EXPLICIT_
#@ _INSERT_IMPLICIT_
INSERT INTO insect (name,date,origin) VALUES
('cabbage butterfly','2014-09-10','garden'),
('ant','2014-09-10','back yard'),
('ant','2014-09-10','back yard'),
('termite','2014-09-10','kitchen woodwork');
#@ _INSERT_IMPLICIT_
SELECT * FROM insect;
+----+-------------------+------------+------------------+
| id | name | date | origin |
+----+-------------------+------------+------------------+
| 1 | housefly | 2014-09-10 | kitchen |
| 2 | millipede | 2014-09-10 | driveway |
| 3 | grasshopper | 2014-09-10 | front yard |
| 4 | stink bug | 2014-09-10 | front yard |
| 5 | cabbage butterfly | 2014-09-10 | garden |
| 6 | ant | 2014-09-10 | back yard |
| 7 | ant | 2014-09-10 | back yard |
| 8 | termite | 2014-09-10 | kitchen woodwork |
+----+-------------------+------------+------------------+
8 rows in set (0.01 sec)
mysql> show create table insect;
+--------+-----------------------------------------------+
| Table | Create Table |
+--------+-----------------------------------------------+
| insect | CREATE TABLE `insect` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(30) NOT NULL,
`date` date NOT NULL,
`origin` varchar(30) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1 |
+--------+----------------------------------------------+
1 row in set (0.00 sec) 清掉一筆資料造成gap
mysql> delete from insect where id = 3;
Query OK, 1 row affected (0.02 sec)
mysql> select * from insect;
+----+-------------------+------------+------------------+
| id | name | date | origin |
+----+-------------------+------------+------------------+
| 1 | housefly | 2014-09-10 | kitchen |
| 2 | millipede | 2014-09-10 | driveway |
| 4 | stink bug | 2014-09-10 | front yard |
| 5 | cabbage butterfly | 2014-09-10 | garden |
| 6 | ant | 2014-09-10 | back yard |
| 7 | ant | 2014-09-10 | back yard |
| 8 | termite | 2014-09-10 | kitchen woodwork |
+----+-------------------+------------+------------------+
7 rows in set (0.00 sec) 開始測試renumber sequence
mysql> ALTER TABLE insect DROP id;
Query OK, 7 rows affected (0.04 sec)
Records: 7 Duplicates: 0 Warnings: 0
mysql> show create table insect;
+--------+------------------------------------------------+
| Table | Create Table |
+--------+------------------------------------------------+
| insect | CREATE TABLE `insect` (
`name` varchar(30) NOT NULL,
`date` date NOT NULL,
`origin` varchar(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+--------+------------------------------------------------+
1 row in set (0.00 sec)
mysql> ALTER TABLE insect
-> ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,
-> ADD PRIMARY KEY (id);
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from insect;
+----+-------------------+------------+------------------+
| id | name | date | origin |
+----+-------------------+------------+------------------+
| 1 | housefly | 2014-09-10 | kitchen |
| 2 | millipede | 2014-09-10 | driveway |
| 3 | stink bug | 2014-09-10 | front yard |
| 4 | cabbage butterfly | 2014-09-10 | garden |
| 5 | ant | 2014-09-10 | back yard |
| 6 | ant | 2014-09-10 | back yard |
| 7 | termite | 2014-09-10 | kitchen woodwork |
+----+-------------------+------------+------------------+
7 rows in set (0.00 sec)
mysql> show create table insect;
+--------+------------------------------------------------+
| Table | Create Table |
+--------+------------------------------------------------+
| insect | CREATE TABLE `insect` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(30) NOT NULL,
`date` date NOT NULL,
`origin` varchar(30) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1 |
+--------+-----------------------------------------------+
1 row in set (0.00 sec) 清掉一筆資料造成gap
mysql> delete from insect where id = 3;
Query OK, 1 row affected (0.01 sec)
mysql> select * from insect;
+----+-------------------+------------+------------------+
| id | name | date | origin |
+----+-------------------+------------+------------------+
| 1 | housefly | 2014-09-10 | kitchen |
| 2 | millipede | 2014-09-10 | driveway |
| 4 | cabbage butterfly | 2014-09-10 | garden |
| 5 | ant | 2014-09-10 | back yard |
| 6 | ant | 2014-09-10 | back yard |
| 7 | termite | 2014-09-10 | kitchen woodwork |
+----+-------------------+------------+------------------+
6 rows in set (0.00 sec) 用組合語法測試renumber sequence
mysql> ALTER TABLE insect
-> DROP id,
-> ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from insect;
+----+-------------------+------------+------------------+
| id | name | date | origin |
+----+-------------------+------------+------------------+
| 1 | housefly | 2014-09-10 | kitchen |
| 2 | millipede | 2014-09-10 | driveway |
| 3 | cabbage butterfly | 2014-09-10 | garden |
| 4 | ant | 2014-09-10 | back yard |
| 5 | ant | 2014-09-10 | back yard |
| 6 | termite | 2014-09-10 | kitchen woodwork |
+----+-------------------+------------+------------------+
6 rows in set (0.00 sec)
mysql> show create table insect;
+--------+------------------------------------------------+
| Table | Create Table |
+--------+------------------------------------------------+
| insect | CREATE TABLE `insect` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(30) NOT NULL,
`date` date NOT NULL,
`origin` varchar(30) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1 |
+--------+-----------------------------------------------+
1 row in set (0.00 sec)
不過我想有被foreign key參照的話,應該就不能這樣做了吧~~

重點是書上還寫了這一段
我就覺得更神了
MySQL permits multiple actions to be done with ALTER TABLE (something not true for
all database systems). However, notice that this multiple-action statement is not simply
a concatenation of the two single-action ALTER TABLE statements. The difference is that
it is unnecessary to reestablish the PRIMARY KEY: MySQL doesn’t drop it unless the indexed
column is missing after all the actions specified in the ALTER TABLE statement
have been performed.
也就表示
drop 跟add兩個動作一起做的指令
是不會重建索引的
wow...........
這麼厲害.....

Posted in: auto_increment,MySQL