MySQL命令行insert如何正确执行？

在数据库管理中，MySQL 是一款广泛使用的开源关系型数据库管理系统，而命令行操作是其核心功能之一。INSERT 语句用于向数据库表中插入新的数据记录，是日常数据操作中最常用的命令之一，掌握 MySQL 命令行下的 INSERT 语句，不仅能高效完成数据录入任务,还能深入理解数据库的底层操作逻辑。

INSERT 语句的基本语法

INSERT 语句的基本语法结构如下：

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

• table_name：目标表的名称,需确保存在且拼写正确。
• (column1, column2, ...)：可选参数，指定要插入数据的列名，如果省略，则默认为表的所有列，但需确保 VALUES 中的值顺序与表中列的顺序完全一致。
• (value1, value2, ...)：对应列的具体值，值的数量、数据类型必须与列名列表匹配。

假设有一个 students 表，包含 id（INT）、name（VARCHAR）、age（INT）三列,插入一条数据的命令为：

INSERT INTO students (id, name, age) VALUES (1, '张三', 20);

插入多行数据

MySQL 支持单条 INSERT 语句插入多行数据,语法如下：

INSERT INTO table_name (column1, column2, ...)
VALUES 
(value1_1, value1_2, ...),
(value2_1, value2_2, ...),
...;

这种方式比多次执行单行插入效率更高,尤其适合批量数据导入。

INSERT INTO students (id, name, age) 
VALUES 
(2, '李四', 21),
(3, '王五', 22),
(4, '赵六', 20);

插入默认值或 NULL 值

如果某列允许 NULL 或设置了默认值,可以通过以下方式插入：

1. 插入 NULL 值：显式指定 NULL 或省略该列（需表结构允许）。

   INSERT INTO students (id, name, age) VALUES (5, '钱七', NULL);

2. 插入默认值：使用 DEFAULT 关键字，或省略该列（如果列有默认值）。

   INSERT INTO students (id, name, age) VALUES (6, '孙八', DEFAULT);

从其他表插入数据

MySQL 支持通过 SELECT 子句从其他表查询数据并插入目标表,语法如下：

INSERT INTO target_table (column1, column2, ...)
SELECT column1, column2, ...
FROM source_table
WHERE condition;

将 temp_students 表中年龄大于 22 的学生插入到 students 表：

INSERT INTO students (id, name, age)
SELECT id, name, age
FROM temp_students
WHERE age > 22;

插入数据时的注意事项

1. 数据类型匹配：插入的值必须与列的数据类型兼容，例如字符串需用单引号括起来,数字则无需引号。
2. 主键与唯一约束：如果表有主键或唯一约束，插入的数据不能重复,否则会报错。
3. 事务处理：为确保数据一致性，可将多个 INSERT 语句放在事务中执行：

   START TRANSACTION;
   INSERT INTO students (id, name, age) VALUES (7, '周九', 23);
   INSERT INTO students (id, name, age) VALUES (8, '吴十', 24);
   COMMIT;

4. 批量插入优化：对于大量数据，可调整 max_allowed_packet 参数或使用 LOAD DATA INFILE 命令,避免因数据包过大导致失败。

常见错误与解决方案

1. 错误代码 1366：数据类型不匹配，例如将字符串 'abc' 插入 INT 类型的列。

   解决方案：检查列定义,确保值的数据类型正确。

   
   （图片来源网络，侵删）
2. 错误代码 1062：主键或唯一键冲突，例如插入重复的 id。
   • 解决方案：使用 INSERT IGNORE 或 ON DUPLICATE KEY UPDATE 处理重复数据。

示例：学生表数据插入

假设 students 表结构如下：
| 列名 | 数据类型 | 约束 |
|——–|————|————–|
| id | INT | PRIMARY KEY |
| name | VARCHAR(50)| NOT NULL |
| age | INT | DEFAULT 18 |

插入数据的完整示例：

-- 插入单行数据
INSERT INTO students (id, name, age) VALUES (9, '郑十一', 25);
-- 插入多行数据
INSERT INTO students (id, name) 
VALUES 
(10, '王十二'),
(11, '冯十三');
-- 从临时表插入
INSERT INTO students (id, name, age)
SELECT id, name, age
FROM transfer_students
WHERE grade = 'A';

相关问答 FAQs

Q1: 如何避免插入重复数据？
A1: 可使用 INSERT IGNORE 忽略重复数据，或 ON DUPLICATE KEY UPDATE 更新已存在的数据。

INSERT INTO students (id, name, age) VALUES (12, '陈十四', 26) ON DUPLICATE KEY UPDATE name='陈十四', age=26;

Q2: 如何批量插入大量数据时提高效率？
A2: 可通过以下方式优化：

1. 使用单条 INSERT 语句插入多行（如前文所述）；
2. 禁用索引：先 ALTER TABLE students DISABLE KEYS，插入数据后再 ENABLE KEYS；
3. 使用 LOAD DATA INFILE 替代 INSERT,文件导入速度更快。