f MySQL Renumbering an Existing Sequence ~ 迪貝之家

Pages

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...........
這麼厲害.....