Like Share Discussion Bookmark Smile

J.J. Huang   2020-07-12   MySQL   瀏覽次數:

MySQL - 第二十一章 | 插入資料(下)

插入多個行

INSERT可以插入一行到一個表中。但如果你想插入多個行怎麼辦?

可以使用多條INSERT語句,甚至一次提交它們,每條語句用一個分號結束。

1
2
3
4
5
mysql> INSERT INTO `testdb`.`customers` (`cust_name`, `cust_address`, `cust_city`, `cust_state`, `cust_zip`, `cust_country`) VALUES ('Pep E. LaPwe', '100 Main Street', 'Los Angeles', 'CA', '90046', 'USA');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `testdb`.`customers` (`cust_name`, `cust_address`, `cust_city`, `cust_state`, `cust_zip`, `cust_country`) VALUES ('M. Martian', '42 Galaxy Way', 'New York', 'NY', '11213', 'USA');
Query OK, 1 row affected (0.01 sec)

或者,只要每條INSERT語句中的列名(和次序)相同。

1
2
3
mysql> INSERT INTO `testdb`.`customers` (`cust_name`, `cust_address`, `cust_city`, `cust_state`, `cust_zip`, `cust_country`) VALUES ('Pep E. LaPwe', '100 Main Street', 'Los Angeles', 'CA', '90046', 'USA'), ('M. Martian', '42 Galaxy Way', 'New York', 'NY', '11213', 'USA');
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0

分析:其中單條INSERT語句有多組值,每組值用一對圓括號括起來,用逗號分隔。

提示:提高INSERT的性能 此技術可以提高資料庫處理的性能,因為MySQL用單條INSERT語句處理多個插入比使用多條INSERT語句快。

插入檢索出的資料

INSERT一般用來給表插入一個指定列值的行。但是,INSERT還存在另一種形式,可以利用它將一條SELECT語句的結果插入表中。這就是所謂的INSERT SELECT,顧名思義,它是由一條INSERT語句和一條SELECT語句組成的。

假如你想從另一表中合併客戶列表到你的customers表。不需要每次讀取一行,然後再將它用INSERT插入。

說明:新例子的說明 這個例子把一個名為custnew的表中的資料導入customers表中。為了試驗這個例子,應該首先創建和填充custnew表。custnew表的結構與附錄B中描述的customers表的相同。在填充custnew時,不應該使用已經在customers中使用過的cust_id值(如果主鍵值重復,後續的INSERT操作將會失敗)或僅省略這列值讓MySQL在導入資料的過程中產生新值。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
CREATE TABLE `custnew` (
`cust_id` int NOT NULL AUTO_INCREMENT,
`cust_name` char(50) NOT NULL,
`cust_address` char(50) DEFAULT NULL,
`cust_city` char(50) DEFAULT NULL,
`cust_state` char(5) DEFAULT NULL,
`cust_zip` char(10) DEFAULT NULL,
`cust_country` char(50) DEFAULT NULL,
`cust_contact` char(50) DEFAULT NULL,
`cust_email` char(255) DEFAULT NULL,
PRIMARY KEY (`cust_id`)
) ENGINE=InnoDB AUTO_INCREMENT=20000 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `custnew` (`cust_name`, `cust_address`, `cust_city`, `cust_state`, `cust_zip`, `cust_country`, `cust_contact`, `cust_email`) VALUES
('A', '123 Maple Lane', 'Detroit', 'MI', '44444', 'USA', 'Y Lee', 'ylee@coyote.com'),
('B', '345 Fromage Lane', 'Columbus', 'OH', '43333', 'USA', 'Jerry Mouse', NULL),
('C', '567 Sunny Place', 'Muncie', 'IN', '42222', 'USA', 'Jim Jones', 'rabbit@wascally.com'),
('D', '890 Riverside Drive', 'Phoenix', 'AZ', '88888', 'USA', 'Y Sam', 'sam@yosemite.com'),
('E', '4545 53rd Street', 'Chicago', 'IL', '54545', 'USA', 'E Fudd', NULL);
1
2
3
mysql> INSERT INTO `testdb`.`customers` (`cust_name`, `cust_contact`, `cust_email`, `cust_address`, `cust_city`, `cust_state`, `cust_zip`, `cust_country`) SELECT cust_name, cust_contact, cust_email, cust_address, cust_city, cust_state, cust_zip, cust_country FROM custnew;
Query OK, 5 rows affected (0.02 sec)
Records: 5 Duplicates: 0 Warnings: 0

分析:這個例子使用INSERT SELECT從custnew中將所有資料導入customers。SELECT語句從custnew檢索出要插入的值,而不是列出它們。SELECT中列出的每個列對應於customers表名後所跟的列表中的每個列。這條語句將插入多少行有賴於custnew表中有多少行。如果這個表為空,則沒有行被插入(也不產生錯誤,因為操作仍然是合法的)。如果這個表確實含有資料,則所有資料將被插入到customers。

這個例子沒有導入了cust_id,這樣MySQL就會生成新值。假設你能夠確保cust_id的值不重復,你也可以加入這列(從INSERT和SELECT中)。

注意:INSERTSELECT中的列名 為簡單起見,這個例子在INSERT和SELECT語句中使用了相同的列名。但是,不一定要求列名匹配。事實上,MySQL甚至不關心SELECT返回的列名。它使用的是列的位置,因此SELECT中的第一列(不管其列名)將用來填充 表列中指定的第一個列,第二列將用來填充表列中指定的第二個列,如此等等。這對於從使用不同列名的表中導入資料是非常有用的。

INSERT SELECT中SELECT語句可包含WHERE子句以過濾插入的資料。

結語

加入多個資料,只有一行INSERT可以大大提升效能,這點很重要~當在寫腳本的時候請盡量這樣寫。
另外使用SELECT的資料來當作VALUES,這還滿常使用的,其要注意的就是只需要把位置對應好,名稱其實是不重要的,好閱讀即可。

註:以上參考了
MySQL必知必会 MySQL Crash Course