12 个基本的 MySQL 插入命令示例

常见的 MySQL 操作之一是将记录插入到表中。

本教程通过几个实用且有用的示例解释了如何使用 MySQL 插入命令。

以下示例将使用用户名 devuser 和密码 mysecretpwd 连接到 devdb 数据库

# mysql -u devuser -pmysecretpwd devdb 
mysql>

在本教程中，我们将向员工表中插入值。这是雇员表的结构。

mysql> desc employee;
+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| id     | int(11)     | NO   | PRI | NULL    | auto_increment |
| name   | varchar(20) | YES  |     | NULL    |                |
| dept   | varchar(10) | YES  |     | NULL    |                |
| salary | int(10)     | YES  |     | NULL    |                |
+--------+-------------+------+-----+---------+----------------+

如果您不熟悉 MySQL，请使用此如何创建 MySQL 数据库和表开始。

1. 基本插入命令示例

以下命令将向员工表中插入三个新记录。在此示例中，在“值”之后，指定表中所有列的值。

INSERT INTO employee VALUES(100,'Thomas','Sales',5000);

使用MySQL select 命令验证记录是否已成功插入。

SELECT * FROM employee;

2. 仅插入选定列的值

如果只想向选定的列插入值，则应在插入命令中指定列名称。

下面将仅为列 id 和 name 插入两条记录。

INSERT INTO employee(id,name) VALUES(200,'Jason');

对于“dept”和“salary”列，我们没有为此特定记录指定任何值。因此，我们将在 select 命令输出中看到 NULL 作为值。请注意，这不是字符串“NULL”，选择命令只是显示字符串“NULL”以表明该列值确实为空。

mysql> SELECT * FROM employee;
+-----+--------+-------+--------+
| id  | name   | dept  | salary |
+-----+--------+-------+--------+
| 100 | Thomas | Sales |   5000 |
| 200 | Jason  | NULL  |   NULL |
+-----+--------+-------+--------+
2 rows in set (0.00 sec)

3. 插入集示例

除了在选择命令中使用“values”关键字外，您还可以在选择命令中使用“set”关键字，如下所示。

下面的插入命令与前面的示例完全相同。但是，这不是 value，而是使用 set。

mysql> INSERT INTO employee SET id=300, name='Mayla';

mysql> select * from employee;
+-----+--------+-------+--------+
| id  | name   | dept  | salary |
+-----+--------+-------+--------+
| 100 | Thomas | Sales |   5000 |
| 200 | Jason  | NULL  |   NULL |
| 300 | Mayla  | NULL  |   NULL |
+-----+--------+-------+--------+

4. 根据另一个表的行插入记录

在本例中，我们将使用 INSERT ... SELECT 方法，该方法将从另一个表中选择行，并将其插入到我们的表中。

下面的例子将把contractor表中的所有记录插入到employee表中。

INSERT INTO employee SELECT * FROM contractor;

您还可以在 select 命令中使用各种“where”条件来仅从承包商表中选择选定的记录并插入到员工表中，如下所示。

INSERT INTO employee SELECT * FROM contractor WHERE salary >= 7000;

注意：如果您习惯使用 Oracle 数据库，您将使用“插入员工 AS select * from contract”。请注意，mysql 在此上下文中不使用“AS”关键字。

5. 从另一个表插入选定的列值

您还可以仅从另一个表中选择行的选定列值并将其插入到您的表中。

下面的例子将把contractor表中所有行的“id”和“name”的值插入到employee表中。

INSERT INTO employee(id,name) SELECT id,name FROM contractor;

就像前面的例子一样，你也可以使用 where 条件并限制记录。

INSERT INTO employee(id,name) SELECT id,name FROM contractor WHERE salary >= 7000;

请注意，如果雇员表中的主键（即 id）的记录已存在，您将收到以下错误消息。以下错误表明雇员表中已存在雇员 ID“100”。

ERROR 1062 (23000): Duplicate entry '100' for key 'PRIMARY'

6. 向特定分区插入记录

如果您使用按范围分区创建了表，那么您可以在插入命令中指定分区，如下所示。

下面的例子将记录插入到分区 p1 的雇员表中

INSERT INTO employee PARTITION (p1) VALUES(100,'Thomas','Sales',5000);

请注意该行已存在于该特定分区中。例如，在此示例 p1 中，您将收到以下错误消息：

ERROR 1729 (HY000): Found a row not matching the given partition set

注意：这仅适用于 MySQL 5.6 及更高版本。

7. 将记录插入到一​​个表中的多个分区

您还可以使用单个插入语句将记录插入多个分区。

下面的insert语句将把id为“100”的记录插入到分区p1，把id为“200”的记录插入到分区p2中。

INSERT INTO employee PARTITION (p1, p2) VALUES(100,'Thomas','Sales',5000), (200,'Jason','Technology',5500);

请注意，在上面的示例中，如果由于某种原因，MySQL 无法将其中一条记录插入到一​​个分区中，则整个 insert 语句将失败，并且不会插入两条记录。

同样，这仅适用于 MySQL 5.6 及更高版本。

8. 插入时忽略错误信息

出于某种原因，如果你想忽略 MySQL 在插入语句时抛出的错误信息，你可以使用插入忽略。

例如，以下命令将抛出错误消息，因为该记录已存在于表中。

mysql> INSERT INTO employee VALUES(100,'Thomas','Sales',5000);
ERROR 1062 (23000): Duplicate entry '100' for key 'PRIMARY'

要忽略上述错误消息，您可以使用“insert ignore”（而不是仅插入），如下所示。请注意，这仍然不会将记录插入表中，因为 id 列上有一个主键。但是，这将简单地忽略错误消息。

mysql> INSERT IGNORE INTO employee VALUES(100,'Thomas','Sales',5000);
Query OK, 0 rows affected (0.00 sec)

9. Insert 中的默认值

如果 MySQL 在严格模式下运行，并且当我们不指定默认值时，它会抛出错误消息。

但是，如果未启用严格模式（这是默认设置），并且当您执行插入命令且未为列指定值时，它将使用该特定列数据类型的默认值。

例如，在bonus 表中，两个列值都设置为“not null”。

mysql> DESC bonus;
+--------+---------+------+-----+---------+-------+
| Field  | Type    | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| id     | int(11) | NO   |     | NULL    |       |
| amount | int(11) | NO   |     | NULL    |       |
+--------+---------+------+-----+---------+-------+

让我们为 id 列在此表中插入一条记录。

INSERT INTO bonus(id) VALUES(100);

当您执行选择命令时，您会注意到金额列自动设置为隐式默认值 0。

SELECT * FROM bonus;
+-----+--------+
| id  | amount |
+-----+--------+
| 100 |      0 |
+-----+--------+

如果您没有同时指定 id 和金额，则两者都将自动设置为 0，如下所示。即当未指定值时，MySQL 将使用 DEFAULT 值。

INSERT INTO bonus VALUES();

mysql> select * from bonus;
+-----+--------+
| id  | amount |
+-----+--------+
|   0 |      0 |
+-----+--------+

注意：您也可以在如下所示的值中使用关键字“DEFAULT”，这将实现上述输出。

INSERT INTO bonus VALUES(DEFAULT,DEFAULT);

对于字符串列，默认值为空字符串。另外，请注意，当数字列设置了 AUTO_INCREMENT 时，默认值将是相应序列的下一个值。

10. 插入值中的表达式

在以下示例中，对于奖励值，我们已将“5000+id”指定为值。因此，这会将员工 ID 值添加到奖金值中，并将最终值插入到奖金列中，如下所示。

您可以在值中使用“+”、“-”、“*”或任何其他有效的 MySQL 表达式运算符。在以下示例中，它使用“50*2”作为 id 列。因此，将插入的 id 是“100”

您也可以参考其他列的值。在以下示例中，它使用“5000+id”作为奖金列。因此，这将取 id 列的值（100），并将其添加到 5000。因此，最终奖励值为“5100”，如下所示。

mysql> INSERT INTO employee VALUES(50*2,'Thomas','Sales',5000+id);

mysql> select * from employee;
+-----+--------+-------+--------+
| id  | name   | dept  | salary |
+-----+--------+-------+--------+
| 100 | Thomas | Sales |   5100 |
+-----+--------+-------+--------+

11. 插入低优先级（或高优先级）活动

对于支持表锁定的存储引擎（例如 MyISAM），您可以指定插入的优先级。

例如，这个插入语句将延迟插入（使其成为低优先级），直到没有对表进行读取。

INSERT LOW_PRIORITY INTO employee VALUES(100,'Thomas','Sales',5000);

您还可以指定如下所示的高优先级，这与低优先级插入的行为相反。

INSERT HIGH_PRIORITY INTO employee VALUES(100,'Thomas','Sales',5000);

请记住，如果您的数据库是读取密集型的，并且您指定了一个 low_priority 插入语句，则您的插入可能不会执行很长时间。

另外，请注意这与“INSERT ... DELAYED”几乎没有什么不同，后者从 MySQL 5.6.6 开始被弃用。所以，不要再使用“插入……延迟”了。

12. 当发现重复时，更新列值

在插入过程中，如果有重复的键，则会失败，如下所示，因为表中已经存在 id “100”。

mysql>  INSERT INTO employee VALUES(100,'Thomas','Sales',5000);
ERROR 1062 (23000): Duplicate entry '100' for key 'PRIMARY'

mysql> select * from employee;
+-----+--------+-------+--------+
| id  | name   | dept  | salary |
+-----+--------+-------+--------+
| 100 | Thomas | Sales |   5000 |
+-----+--------+-------+--------+

但是，您可以使用“ON DUPLICATE KEY UPDATE”对该特定记录进行一些更新（当发现重复时），如下所示。

如以下示例所示，当插入失败（由于重复键）时，我们通过将 500 添加到其值来更新工资列。

mysql> INSERT INTO employee VALUES(100,'Thomas','Sales',5000) on DUPLICATE KEY UPDATE salary=salary+500;

mysql> select * from employee;
+-----+--------+-------+--------+
| id  | name   | dept  | salary |
+-----+--------+-------+--------+
| 100 | Thomas | Sales |   5500 |
+-----+--------+-------+--------+

请注意，在上面的示例中，在插入时，即使只更新了一条记录，输出也会显示“受影响的 2 行”。