MySQL 如何實現資料插入

使用MySQL插入資料時，可以根據需求場景選擇合適的插入語句，例如當資料重複時如何插入資料，如何從另一個表匯入資料，如何批次插入資料等場景。本文透過給出每個使用場景下的例項來說明資料插入的實現過程和方法。

一、方法分類

二、具體方法

使用場景	作用	語句	注意
常規插入
	忽略欄位名
		insert into 表名 values (值1, 值2,...,值n)	預設value中的值依次填充所有字，如果出現唯一性衝突，就會丟擲異常
	按照欄位插入
		insert into 表名(欄位1, 欄位2,...,欄位n) values (值1, 值2,...,值n)	欄位和值一一對應
	一次性插入多條資料
		insert into 表名(欄位1, 欄位2,...,欄位n) values (值a1, 值a2,...,值an),(值b1, 值b2,...,值bn)	多行之間用逗號隔開，不需要再次寫insert into語句
從另一個表匯入	匯出A表的某些資料插入到B表	insert into 表名B(欄位B1, 欄位B2,...,欄位Bn) select 欄位A1, 欄位A2,...,欄位An from 表名A where [執行條件]	欄位A和B可以欄位名稱不一樣，但是資料型別必須一致
插入時資料重複
	如果記錄存在報錯
		insert into 表名(欄位1, 欄位2,...,欄位n) values (值1, 值2,...,值n)	如果插入的資料記錄存在，報錯並捕獲異常，不存在則直接新增記錄
	如果記錄存在不插入記錄
		insert ignore into 表名(欄位1, 欄位2,...,欄位n) values (值1, 值2,...,值n)	如果插入的資料記錄存在就儲存舊記錄忽略新記錄，不存在則直接新增記錄
	不論記錄是否存在都要插入記錄
		replace 表名(欄位1, 欄位2,...,欄位n) values (值1, 值2,...,值n)	如果插入的資料記錄存在就先刪除再更新，不存在則直接新增記錄
	如果記錄存在更新指定欄位
		insert into … on duplicate key update	如果插入的資料記錄存在就更新指定欄位，不存在則直接新增記錄

三、例項

students 表 (id表示主鍵，name是姓名，score是平均成績)

id	name	score
1	李明	67

（1）常規插入

• 忽略欄位名

insert into 
	students 
values(null, '張三', '74');

執行後結果

id	name	score
1	李明	67
2	張三	74

• 按照欄位插入

insert into 
	students(name) 
values('孫華');

執行後結果

id	name	score
1	李明	67
2	張三	74
3	孫華

• 一次性插入多條資料

insert into 
	students(name, score) 
values('劉平', '56'),('周雨', '90');

執行後結果

id	name	score
1	李明	67
2	張三	74
3	孫華
4	劉平	56
5	周雨	90

（2）從另一個表匯入

students 表 (id表示主鍵，name是姓名，score是平均成績)

id	user_name	mobile_phone_number
1	馬化騰	13800000000
2	任正非	13800000011
3	馬雲	13800000022

• 匯出users表的某些資料插入到students表

insert into 
	students(name,score) 
select 
	user_name,
	mobile_phone_number 
from users where id <> 3;

執行結果

id	name	score
1	李明	67
2	張三	74
3	孫華
4	劉平	56
5	周雨	90
6	馬化騰	13800000000
7	馬雲	13800000022

注意：只要對應欄位的型別一樣，欄位不一樣也可以匯入資料，不會衝突。

（3）插入時資料重複

• 如果記錄存在報錯

insert into 
	students 
values(1, '張三', '74');

執行結果: 報錯

Duplicate entry '1' for key 'PRIMARY'

• 如果記錄存在不插入記錄

insert ignore into 
	students(id,name,score) 
values(1, '張三', '74');

執行結果：不插入不報錯

Affected rows:0

id	name	score
1	李明	67
2	張三	74
3	孫華
4	劉平	56
5	周雨	90
6	馬化騰	13800000000
7	馬雲	13800000022

• 不論記錄是否存在都要插入記錄

replace 
	students
values(1, '張三', '74');

執行結果

id	name	score
1	張三	74
2	張三	74
3	孫華
4	劉平	56
5	周雨	90
6	馬化騰	13800000000
7	馬雲	13800000022

• 如果記錄存在更新指定欄位

insert into 
	students(id)
values(1) on duplicate key 
update 
	name = '李明',
	score = '67';

執行結果

id	name	score
1	李明	67
2	張三	74
3	孫華
4	劉平	56
5	周雨	90
6	馬化騰	13800000000
7	馬雲	13800000022

建立 students 表的程式碼

-- ----------------------------
-- Table structure for students
-- ----------------------------
DROP TABLE IF EXISTS `students`;
CREATE TABLE `students`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '學生id',
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '姓名',
  `score` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '成績',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of students
-- ----------------------------
INSERT INTO `students` VALUES (1, '李明', '67');

建立 users 表的程式碼

-- ----------------------------
-- Table structure for students
-- ----------------------------
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '使用者id',
  `user_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '使用者名稱',
  `mobile_phone_number` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '手機號碼',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of students
-- ----------------------------
INSERT INTO `users` VALUES (1, '馬化騰', '13800000000');
INSERT INTO `users` VALUES (2, '任正非', '13800000011');
INSERT INTO `users` VALUES (3, '馬雲', '13800000022');

四、注意事項

（1）不寫欄位名，需要填充自增ID

• [使用]：0或者null或者default，自增id預設從1開始。
• [使用]：或者沒有在自增id中出現的（不重複）數（例如-1，-2），浮點型資料例如3.4，最後顯示3，會進行四捨五入。即使定義了int型別，輸入‘3’或者浮點型，都會強制轉化為int型別，但是輸入'a'會報錯。具體細節可以看原始碼。

問題：第一個欄位id為什麼可以寫null？
如果建表的時候寫了id為自增id，而寫0或者null或者default或者沒有在自增id中出現的（不重複）數（例如-1，-2），系統都會自動填充id。如果建表的時候沒有寫明是自增id，那麼主鍵一定是不能為空的，這個時候寫null就會報錯。

（2）按欄位名填充，可以不錄入id

• [注意]：欄位要與值一一對應。

其餘注意事項：

• 欄位名可以省略，預設所有列；
• 錄入值的型別和欄位的型別要一致或相容；
• 欄位和值的個數必須一致。不能出現一行記錄5個值，另外一行6個值的情況；
• 如果寫了欄位，即使是空值也不能空著，用null代替；