MySQL實現資料插入操作的範例詳解

2023-02-24 06:02:52

一、方法分類

二、具體方法

使用場景	作用	語句	注意
常規插入	忽略欄位名	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）常規插入

忽略欄位名

執行後結果

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');