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

插入多個行

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

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

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在導入資料的過程中產生新值。

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子句以過濾插入的資料。