mysql 专业笔记 -- 第 10 章：INSERT

第 10.1 节：INSERT ... ON DUPLICATE KEY UPDATE

INSERT INTO table_name (index_field, other_field_1, other_field_2)
VALUES ('index_value', 'insert_value', 'other_value')
ON DUPLICATE KEY UPDATE
    other_field_1 = 'update_value',
    other_field_2 = VALUES(other_field_2);

这将向 table_name 中插入指定的值，但如果唯一键已存在，它将把 other_field_1 更新为新值。

有时，在重复键更新时，使用 VALUES() 函数来访问传递给 INSERT 的原始值会很方便，而不是直接设置值。这样，你可以通过 INSERT 和 UPDATE 设置不同的值。请参见上面的示例：在 INSERT 时 other_field_1 设置为 insert_value，在 UPDATE 时设置为 update_value，而 other_field_2 始终设置为 other_value。

要使 INSERT ... ON DUPLICATE KEY UPDATE（IODKU）正常工作，关键在于表结构中包含一个会发出重复键冲突信号的唯一键。这个唯一键可以是主键，也可以不是。它可以是单列上的唯一键，也可以是多列上的复合唯一键。

第 10.2 节：插入多行

INSERT INTO my_table (field_1, field_2)
VALUES
    ('data_1', 'data_2'),
    ('data_1', 'data_3'),
    ('data_4', 'data_5');

这是一种用一个 INSERT 语句一次添加多行的简单方法。

这种“批量”插入比逐行插入快得多。通常，一次批量插入 100 行比逐行插入 100 行快 10 倍。

忽略已存在的行

在导入大型数据集时，在某些情况下跳过那些通常会导致查询失败的行（例如由于主键重复）可能是更可取的。这可以通过使用 INSERT IGNORE 来实现。

考虑下面的示例数据库：

SELECT * FROM people;
-- 结果：
+----+------+
| id | name |
+----+------+
| 1  | john |
| 2  | anna |
+----+------+

INSERT IGNORE INTO people (id, name) VALUES
    (2, 'anna'),   -- 如果不使用 IGNORE 关键字，这条记录会导致错误
    (3, 'mike');

SELECT * FROM people;
-- 结果：
+----+------+
| id | name |
+----+------+
| 1  | john |
| 2  | anna |
| 3  | mike |
+----+------+

需要记住的重要一点是，INSERT IGNORE 也会静默地跳过其他错误。以下是 MySQL 官方文档的说明：

如果未指定 IGNORE，则会导致错误的数据转换将中止语句。使用 IGNORE 时，无效值会被调整为最接近的值并插入；会产生警告，但语句不会中止。

注意： 下面的内容是为了完整性而添加的，但不被认为是最佳实践（例如，如果表中添加了另一列，这种方法将会失败）。

如果你为表中的所有列都指定了值，你可以在 INSERT 语句中省略列列表，如下所示：

INSERT INTO my_table VALUES ('data_1', 'data_2'), ('data_1', 'data_3'), ('data_4', 'data_5');

第 10.3 节：基本插入

INSERT INTO table_name (field_one, field_two) VALUES ('value_one', 'value_two');

在这个简单的例子中，table_name 是要添加数据的目标表，field_one 和 field_two 是要设置数据的字段，而 value_one 和 value_two 分别是这两个字段要设置的数据。

在代码中列出要插入数据的字段是一种好习惯，因为如果表结构发生变化（例如添加了新列），你的插入语句可能会因缺少对应列而失败。

第 10.4 节：带 AUTO_INCREMENT 的 INSERT 和 LAST_INSERT_ID()

当一个表有 AUTO_INCREMENT 主键时，通常不向该列插入数据。相反，应指定所有其他列，然后查询新生成的 ID。

CREATE TABLE t (
    id SMALLINT UNSIGNED AUTO_INCREMENT NOT NULL,
    this ... ,
    that ... ,
    PRIMARY KEY(id)
);

INSERT INTO t (this, that) VALUES (..., ...);
SELECT LAST_INSERT_ID() INTO @id;
INSERT INTO another_table (..., t_id, ...) VALUES (..., @id, ...);

注意：LAST_INSERT_ID() 与会话绑定，因此即使多个连接同时向同一个表插入数据，每个连接也会获得自己的 ID。

你的客户端 API 可能提供了另一种获取 LAST_INSERT_ID() 的方法，无需实际执行 SELECT 并将值留在 MySQL 的 @variable 中，而是直接将其返回给客户端。这种方式通常更可取。

更长的详细示例

IODKU 的“正常”用法是基于某个 UNIQUE 键（而不是 AUTO_INCREMENT 主键）触发“重复键”。下面的示例演示了这一点。注意，它在 INSERT 中没有提供 id。

示例设置：

CREATE TABLE iodku (
    id INT AUTO_INCREMENT NOT NULL,
    name VARCHAR(99) NOT NULL,
    misc INT NOT NULL,
    PRIMARY KEY(id),
    UNIQUE(name)
) ENGINE=InnoDB;

INSERT INTO iodku (name, misc) VALUES
    ('Leslie', 123),
    ('Sally', 456);
-- 查询 OK, 2 rows affected (0.00 sec)
-- Records: 2  Duplicates: 0  Warnings: 0

SELECT * FROM iodku;
+----+--------+------+
| id | name   | misc |
+----+--------+------+
| 1  | Leslie | 123  |
| 2  | Sally  | 456  |
+----+--------+------+

IODKU 执行“更新”并且 LAST_INSERT_ID() 获取相关 ID 的情况：

INSERT INTO iodku (name, misc)
VALUES ('Sally', 3333)   -- 应该执行更新
ON DUPLICATE KEY UPDATE
    id = LAST_INSERT_ID(id),
    misc = VALUES(misc);

SELECT LAST_INSERT_ID();  -- 获取已存在的值
+------------------+
| LAST_INSERT_ID() |
+------------------+
| 2                |
+------------------+

IODKU 执行“插入”并且 LAST_INSERT_ID() 获取新 ID 的情况：

INSERT INTO iodku (name, misc)
VALUES ('Dana', 789)    -- 应该执行插入
ON DUPLICATE KEY UPDATE
    id = LAST_INSERT_ID(id),
    misc = VALUES(misc);

SELECT LAST_INSERT_ID();  -- 获取新值
+------------------+
| LAST_INSERT_ID() |
+------------------+
| 3                |
+------------------+

最终表内容：

SELECT * FROM iodku;
+----+--------+------+
| id | name   | misc |
+----+--------+------+
| 1  | Leslie | 123  |
| 2  | Sally  | 3333 |   -- IODKU 修改了这一行
| 3  | Dana   | 789  |   -- IODKU 添加了这一行
+----+--------+------+

第 10.5 节：INSERT SELECT（从另一个表插入数据）

这是使用 SELECT 语句从另一个表插入数据的基本方法。

INSERT INTO tableA (field_one, field_two)
SELECT tableB.field_one, tableB.field_two
FROM tableB
WHERE tableB.clmn <> 'someValue'
ORDER BY tableB.sorting_clmn;

你可以使用 SELECT *，但那样 tableA 和 tableB 必须有匹配的列数和相应的数据类型。

AUTO_INCREMENT 列的处理方式与使用 VALUES 子句的 INSERT 相同。

这种语法使得用其他表中的数据填充（临时）表变得容易，尤其是在插入时需要过滤数据的情况下。

第 10.6 节：丢失的 AUTO_INCREMENT ID

一些“插入”操作可能会“消耗”ID。下面是一个使用 InnoDB 的例子（其他存储引擎的行为可能不同）：

CREATE TABLE Burn (
    id SMALLINT UNSIGNED AUTO_INCREMENT NOT NULL,
    name VARCHAR(99) NOT NULL,
    PRIMARY KEY(id),
    UNIQUE(name)
) ENGINE=InnoDB;

INSERT IGNORE INTO Burn (name) VALUES ('first'), ('second');
SELECT LAST_INSERT_ID();  -- 1
SELECT * FROM Burn ORDER BY id;
+----+--------+
| id | name   |
+----+--------+
| 1  | first  |
| 2  | second |
+----+--------+

INSERT IGNORE INTO Burn (name) VALUES ('second');  -- 重复，被 'IGNORED'，但 id=3 被消耗了
SELECT LAST_INSERT_ID();  -- 仍然是 "1" —— 在这种情况下不能信任此函数
SELECT * FROM Burn ORDER BY id;
+----+--------+
| id | name   |
+----+--------+
| 1  | first  |
| 2  | second |
+----+--------+

INSERT IGNORE INTO Burn (name) VALUES ('third');
SELECT LAST_INSERT_ID();  -- 现在是 "4"
SELECT * FROM Burn ORDER BY id;  -- 注意 id=3 被跳过了
+----+--------+
| id | name   |
+----+--------+
| 1  | first  |
| 2  | second |
| 4  | third  |
+----+--------+

可以这样大致理解：首先，插入操作查看可能插入多少行。然后为该表从 auto_increment 中获取相应数量的值。最后，插入行，按需使用 ID，并消耗掉任何剩余的值。

只有当系统关闭并重新启动时，剩余的值才有可能被回收。重启后，实际上会执行 MAX(id)。这可能会重新使用那些被消耗掉的或者被 DELETE 删除的最高 ID。

基本上任何形式的 INSERT（包括 REPLACE，它是 DELETE + INSERT）都可能消耗 ID。在 InnoDB 中，全局（非会话）变量 innodb_autoinc_lock_mode 可用于控制某些行为。

当将长字符串“规范化”到 AUTO_INCREMENT ID 时，很容易发生 ID 消耗。这可能导致你选择的 INT 类型的范围被溢出。

mysql 专业笔记 -- 第 10 章：INSERT

第 10.1 节：INSERT ... ON DUPLICATE KEY UPDATE

第 10.2 节：插入多行

忽略已存在的行

第 10.3 节：基本插入

第 10.4 节：带 AUTO_INCREMENT 的 INSERT 和 LAST_INSERT_ID()

更长的详细示例

第 10.5 节：INSERT SELECT（从另一个表插入数据）

第 10.6 节：丢失的 AUTO_INCREMENT ID

阅读量高的博客

关于pycharm连接gitlab需要令牌的操作步骤

virtualbox安装fnOS

qemu使用的简易教程

站点正式发布的首篇文章