一、最规范的写法：列名写全

insert into 表 (列, 列) values (值, 值)，列和值一一对应。 Demo 里插五班，四个列四个值。 为什么推荐写列名： 表以后加字段、删字段、调顺序，这句都不用改。 不写列名的写法，一改表结构就崩。 列的个数必须和值的个数相等，少一个多一个都报错。 这一点比顺序写错更早暴露，所以相对安全。

二、省略列名与省略部分列

insert into 表 values (值, 值, 值, 值) 也能跑， 但值的顺序必须和建表时完全一致，一个都不能少。 表一加字段，这句立刻报错或者填错位。 只给一部分列：insert into 表 (name, teacher) values (…)， 没给的列走默认值——有默认值就给默认值，没有就给 NULL。 Demo 里七班的 room 是 NULL，id 由自增补成 7。 注意「走默认值」的前提是那一列允许为空或有默认值。 如果 name 是 NOT NULL 又没给， 直接报错（下面会撞一次）。

三、一次插多行：批量插入

values 后面用逗号接多组括号，一次插三行：VALUES ('八班', …), ('九班', …), ('十班', …)。 它比写三条 insert 快得多——省了两次网络往返、两次语句解析。 几百上千行批量导入时，这个差别非常明显，有时是几倍。 但别一次塞太多：语句长度有限制（MySQL 的 max_allowed_packet）， 而且一条超大语句失败会整批回滚。 实操里常见做法是每 500 到 1000 行一批。

四、insert ... set：MySQL 的方言

insert into 表 set 列 = 值, 列 = 值 是 MySQL 的写法，列和值挨着写，不容易错位。 本沙箱（SQLite）不认，报 near "SET": syntax error。 教材里这种写法照原样记，在这边要换回 (列) VALUES (值)。 它只适合少量字段。 多行插入还是得用 values——set 语法一次只能插一行。

五、replace into：有则替换、无则插入

判断依据是「主键或唯一键有没有重复」：重复就先把旧行删掉、再插新行；不重复就是普通插入。 这个「先删后插」有个副作用：没有指定的列会被重置成默认值。 Demo 里只改了 room， 但 teacher 那列也重新写了一遍——如果那句里没写 teacher，它就会变回 NULL。 所以 replace 不是「部分更新」，是「整行替换」。 想只改一列，用 update； 想「有就更新、没有就插入」，MySQL 该用 INSERT … ON DUPLICATE KEY UPDATE， 那个才是真的部分更新。 本沙箱没有那个语法，只能用 replace 或先查再定。

六、约束会拦住不合法的插入

给 NOT NULL 的列插 NULL，报错。 插一个已存在的主键，报错。 这两类约束在这里和 MySQL 上都拦。 报错文字不一样，拦的理由一样：这里是 NOT NULL constraint failed， MySQL 是 Column 'name' cannot be null。 这类报错是好事—— 说明约束在干活，脏数据进不来。 靠程序检查字段，永远漏得比数据库自己拦多。 第 9 章专讲这些约束。

七、动手 Demo：四种插入写法，加两次冲突

Demo 七段：列名写全、省略列名、只给部分列、多行插入，然后撞一次方言、试一次 replace， 最后撞两次约束。 每一段都在往同一张 classes 表里塞行，最后的表会变成十条记录。

import _q46 as Q
c = Q.con()

# 列名写全（推荐）
Q.run(c, "INSERT INTO classes (id, name, teacher, room) "
          "VALUES (5, '五班', '何老师', 'C101')")
# 省略列名：必须按建表顺序给全
Q.run(c, "INSERT INTO classes VALUES (6, '六班', '吕老师', 'C102')")
# 只给一部分列
Q.run(c, "INSERT INTO classes (name, teacher) VALUES ('七班', '施老师')")
# 一次插多行
Q.run(c, "INSERT INTO classes (name, teacher, room) "
          "VALUES ('八班', '张老师', 'C201'), "
          "('九班', '孔老师', 'C202'), ('十班', '曹老师', 'C203')")
# insert ... set：MySQL 方言，这里报语法错
c.execute("INSERT INTO classes SET name = '十一班'")
# replace into：有则替换，无则插入
Q.run(c, "REPLACE INTO classes (id, name, teacher, room) "
          "VALUES (1, '一班', '马老师', 'A999')")
Q.run(c, "REPLACE INTO classes (id, name, teacher, room) "
          "VALUES (99, '十一班', '严老师', 'D101')")
# 约束会拦住不合法的插入
c.execute("INSERT INTO classes (name) VALUES (NULL)")
c.execute("INSERT INTO classes (id, name) VALUES (1, '重复')")

实跑输出：

--------------------------------------------------------------------
第三十二课：insert，四种写法与两种冲突处理
--------------------------------------------------------------------

  最规范的写法：列名写全，值对着填
  SQL> INSERT INTO classes (id, name, teacher, room) VALUES (5, '五班', '何老师', 'C101')
  影响 1 行

  SQL> SELECT * FROM classes WHERE id = 5
  id     name     teacher     room
  ------------------------------------
  5      五班     何老师      C101
  （1 行）

  **`insert into 表 (列, 列) values (值, 值)` 是最推荐的写法**——
  **列名写出来，以后表加了字段也不受影响。**

  省略列名：必须按建表顺序给全
  SQL> INSERT INTO classes VALUES (6, '六班', '吕老师', 'C102')
  影响 1 行

  **不写列名也行，但值的顺序必须和建表时完全一致，一个都不能少。**
  **表一加字段，这句立刻报错或者填错位。** **所以不推荐。**

  只给一部分列：其余走默认值
  SQL> INSERT INTO classes (name, teacher) VALUES ('七班', '施老师')
  影响 1 行

  SQL> SELECT * FROM classes WHERE id = 7
  id     name     teacher     room
  ------------------------------------
  7      七班     施老师      NULL
  （1 行）

  **没给的 `room` 是 NULL，`id` 由自增补上 7。** **前提是那一列允许为空、或者有默认值**，
  否则直接报错（下面会撞一次）。

  一次插多行：一条语句搞定
  SQL> INSERT INTO classes (name, teacher, room) VALUES ('八班', '张老师', 'C201'), ('九班',
       '孔老师', 'C202'), ('十班', '曹老师', 'C203')
  影响 3 行

  **`values` 后面用逗号接多组括号，一次插三行。** **它比写三条 `insert` 快得多**——
  省了两次网络往返和两次语句解析。 **几百上千行批量导入时，这个差别非常明显。**

  insert ... set：MySQL 的写法，这里不执行
  SQL> INSERT INTO classes SET name = '十一班', teacher = '严老师'
  报错：near "SET": syntax error

  **`insert ... set 列 = 值` 是 MySQL 的方言**，
  **本沙箱（SQLite）不认，报 `near "SET": syntax error`。**
  **教材里这种写法照原样记**，在这边要换回 `INSERT INTO 表 (列) VALUES (值)`。
  **它的好处是「列和值挨着写，不容易错位」**，**但只适合少量字段，多行插入还是得用 `values`。**

  replace into：有则替换、无则插入
  SQL> SELECT id, name, room FROM classes WHERE id = 1
  id     name     room
  ---------------------
  1      一班     A101
  （1 行）

  SQL> REPLACE INTO classes (id, name, teacher, room) VALUES (1, '一班', '马老师', 'A999')
  影响 1 行

  SQL> SELECT id, name, room FROM classes WHERE id = 1
  id     name     room
  ---------------------
  1      一班     A999
  （1 行）

  **`replace` 的判断依据是「主键或唯一键有没有重复」**：
  **重复就先把旧行删掉、再插新行**（不是「改那一列」）。
  **这个「先删后插」有个副作用**：没有指定的列会被重置成默认值，
  **旧行里那些列的值就丢了。** **所以 `replace` 不是「部分更新」，是「整行替换」。**

  SQL> REPLACE INTO classes (id, name, teacher, room) VALUES (99, '十一班', '严老师', 'D101')
  影响 1 行

  **id = 99 原来不存在，所以这次就是普通的插入。**
  **「有则替换、无则插入」——一句话说完 `replace`。**

  约束会拦住不合法的插入
  SQL> INSERT INTO classes (name) VALUES (NULL)
  报错：NOT NULL constraint failed: classes.name
  SQL> INSERT INTO classes (id, name) VALUES (1, '重复')
  报错：UNIQUE constraint failed: classes.id

  **第一句撞「非空」：`name` 是 NOT NULL，给 NULL 直接拒。**
  **第二句撞「唯一」：`id` 是主键，1 已经存在。**
  **MySQL 那边的报错文字不同**：`Column 'name' cannot be null`、
  `Duplicate entry '1' for key 'PRIMARY'`。 **拦的理由一样。**
  **这类报错是好事**——
  **说明约束在干活，脏数据进不来。** **靠程序检查字段，永远漏得比数据库自己拦多。**

第一段列名写全。 五班拿到 id 5，四个值都对上。 第二段省略列名。 六班进去了，但顺序错一位就全错。 第三段只给两列。 七班的 room 是 NULL。 第四段多行。 影响 3 行，八、九、十三班一次进来。 第五段方言。 insert ... set 直接报语法错——SQLite 不认。 第六段 replace。 id = 1 的房间号变成 A999；id = 99 是新插入的。 第七段撞约束。 一个撞非空、一个撞唯一，两条都拒了。

30 秒实验

1. INSERT INTO classes (name, teacher) VALUES ('x', 'y') 里，id 和 room 分别是什么？
2. REPLACE INTO classes (id, name) VALUES (1, '一班') 之后，teacher 那列变成什么了？
3. 要一次插 5000 行，写成一条 insert 有什么风险？

常见误区

1. 省略列名插数据。 表一加字段就错位或报错，而且错位往往不报错，只是数据悄悄进错了列。
2. 把 replace 当「部分更新」用。 它是整行替换，没写到的列会被重置成默认值。
3. 以为报错是程序问题，绕过约束硬插。 约束拦下来的都是脏数据，该改数据，不该关约束。

插进来容易，改起来就要小心了。下一篇 update，重点是「忘了写 where」这一课。

• ☐ （本篇完，下一篇 7.3 更新数据：update）

相关笔记

• 数据修改简介
• 更新数据：update
• 🦏 MySQL即学即用 · 索引