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