看到這個段落時 |
---|
我好訝異 |
因為我昨天才剛看過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........... |
這麼厲害..... |