MySQL 插入数据详解 - INSERT INTO语法与实例教程

MySQL中使用 INSERT INTO SQL语句向数据表中插入数据。本章将详细介绍多种插入数据的方法，包括命令行操作和各种编程语言的实现方式。

插入数据前需要了解：

目标表的结构和字段类型
必填字段和可选字段
字段的约束条件（如唯一性、非空等）
字符集和编码设置

INSERT INTO 基本语法

完整语法结构

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] table_name
    [(column_name [, column_name] ...)]
    {VALUES | VALUE} (value_list) [, (value_list)] ...
    [ON DUPLICATE KEY UPDATE assignment_list]

参数说明

参数	描述	示例
`LOW_PRIORITY`	延迟插入，直到没有其他客户端读取表	`INSERT LOW_PRIORITY INTO ...`
`IGNORE`	忽略可恢复的错误，继续执行	`INSERT IGNORE INTO ...`
`table_name`	要插入数据的目标表名	`tutorials_tbl`
`column_name`	要插入数据的列名	`tutorial_title, tutorial_author`
`VALUES`	要插入的值列表	`VALUES ('Learn PHP', 'John Poul')`
`ON DUPLICATE KEY UPDATE`	主键或唯一键冲突时的更新操作	`ON DUPLICATE KEY UPDATE count=count+1`

注意： 如果数据是字符型，必须使用单引号或者双引号，如：'value' 或 "value"。

通过命令行插入数据

基本插入操作

# 登录MySQL
mysql -u root -p
Enter password: *******

-- 选择数据库
USE TUTORIALS;

-- 插入单条数据（指定列名）
INSERT INTO tutorials_tbl
    (tutorial_title, tutorial_author, submission_date)
VALUES
    ('Learn PHP', 'John Poul', NOW());

-- 插入单条数据（省略列名，需提供所有列的值）
INSERT INTO tutorials_tbl VALUES
    (NULL, 'Learn MySQL', 'Abdul S', NOW());

-- 插入多条数据
INSERT INTO tutorials_tbl
    (tutorial_title, tutorial_author, submission_date)
VALUES
    ('Learn PHP', 'John Poul', NOW()),
    ('Learn MySQL', 'Abdul S', NOW()),
    ('JAVA Tutorial', 'Sanjay', '2007-05-06');

使用INSERT ... SELECT插入数据

-- 从其他表复制数据
INSERT INTO tutorials_backup
    (tutorial_title, tutorial_author, submission_date)
SELECT
    tutorial_title, tutorial_author, submission_date
FROM tutorials_tbl
WHERE submission_date > '2023-01-01';

插入数据时的特殊处理

-- 使用IGNORE忽略重复键错误
INSERT IGNORE INTO tutorials_tbl
    (tutorial_title, tutorial_author, submission_date)
VALUES
    ('Learn PHP', 'John Poul', NOW());

-- 使用ON DUPLICATE KEY UPDATE处理重复
INSERT INTO tutorials_tbl
    (tutorial_title, tutorial_author, submission_date)
VALUES
    ('Learn PHP', 'John Poul', NOW())
ON DUPLICATE KEY UPDATE
    tutorial_author = VALUES(tutorial_author),
    submission_date = VALUES(submission_date);

使用 PHP 脚本插入数据

重要提醒： 原始的 mysql_* 函数已在PHP 7.0中移除，请使用MySQLi或PDO扩展，并使用预处理语句防止SQL注入攻击。

使用 MySQLi 面向对象方式（推荐）

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "TUTORIALS";

// 创建连接
$conn = new mysqli($servername, $username, $password, $dbname);

// 检查连接
if ($conn->connect_error) {
    die("连接失败: " . $conn->connect_error);
}

// 使用预处理语句防止SQL注入
$stmt = $conn->prepare("INSERT INTO tutorials_tbl (tutorial_title, tutorial_author, submission_date) VALUES (?, ?, ?)");
$stmt->bind_param("sss", $tutorial_title, $tutorial_author, $submission_date);

// 设置参数并执行
$tutorial_title = "Learn PHP";
$tutorial_author = "John Poul";
$submission_date = date("Y-m-d");
$stmt->execute();

echo "新记录插入成功，ID: " . $stmt->insert_id;

$stmt->close();
$conn->close();
?>

使用 MySQLi 过程式方式

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "TUTORIALS";

// 创建连接
$conn = mysqli_connect($servername, $username, $password, $dbname);

// 检查连接
if (!$conn) {
    die("连接失败: " . mysqli_connect_error());
}

// 使用预处理语句
$stmt = mysqli_prepare($conn, "INSERT INTO tutorials_tbl (tutorial_title, tutorial_author, submission_date) VALUES (?, ?, ?)");
mysqli_stmt_bind_param($stmt, "sss", $tutorial_title, $tutorial_author, $submission_date);

$tutorial_title = "Learn MySQL";
$tutorial_author = "Abdul S";
$submission_date = date("Y-m-d");

if (mysqli_stmt_execute($stmt)) {
    echo "新记录插入成功，ID: " . mysqli_insert_id($conn);
} else {
    echo "错误: " . mysqli_error($conn);
}

mysqli_stmt_close($stmt);
mysqli_close($conn);
?>

使用 PDO 方式

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "TUTORIALS";

try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    // 设置PDO错误模式为异常
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // 使用预处理语句
    $stmt = $conn->prepare("INSERT INTO tutorials_tbl (tutorial_title, tutorial_author, submission_date)
                           VALUES (:title, :author, :date)");

    // 绑定参数
    $stmt->bindParam(':title', $tutorial_title);
    $stmt->bindParam(':author', $tutorial_author);
    $stmt->bindParam(':date', $submission_date);

    // 插入第一行
    $tutorial_title = "JAVA Tutorial";
    $tutorial_author = "Sanjay";
    $submission_date = "2007-05-06";
    $stmt->execute();

    echo "新记录插入成功，ID: " . $conn->lastInsertId();

} catch(PDOException $e) {
    echo "错误: " . $e->getMessage();
}

$conn = null;
?>

完整的PHP表单插入示例

<!DOCTYPE html>
<html>
<head>
    <title>添加新教程 - MySQL数据库</title>
    <meta charset="utf-8">
</head>
<body>
    <h2>添加新教程</h2>

    <?php
    // 数据库配置
    $servername = "localhost";
    $username = "username";
    $password = "password";
    $dbname = "TUTORIALS";

    // 处理表单提交
    if ($_SERVER["REQUEST_METHOD"] == "POST") {
        // 获取表单数据并清理
        $tutorial_title = trim($_POST['tutorial_title']);
        $tutorial_author = trim($_POST['tutorial_author']);
        $submission_date = $_POST['submission_date'];

        // 验证数据
        $errors = [];
        if (empty($tutorial_title)) {
            $errors[] = "教程标题不能为空";
        }
        if (empty($tutorial_author)) {
            $errors[] = "作者不能为空";
        }
        if (empty($submission_date)) {
            $submission_date = date('Y-m-d'); // 默认当前日期
        }

        // 如果没有错误，插入数据
        if (empty($errors)) {
            try {
                $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
                $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

                $stmt = $conn->prepare("INSERT INTO tutorials_tbl (tutorial_title, tutorial_author, submission_date)
                                      VALUES (:title, :author, :date)");
                $stmt->bindParam(':title', $tutorial_title);
                $stmt->bindParam(':author', $tutorial_author);
                $stmt->bindParam(':date', $submission_date);

                if ($stmt->execute()) {
                    echo "<div style='color: green;'>教程添加成功！ID: " . $conn->lastInsertId() . "</div>";
                    // 清空表单字段
                    $tutorial_title = $tutorial_author = $submission_date = '';
                }

            } catch(PDOException $e) {
                echo "<div style='color: red;'>错误: " . $e->getMessage() . "</div>";
            }
            $conn = null;
        } else {
            // 显示错误信息
            foreach ($errors as $error) {
                echo "<div style='color: red;'>" . $error . "</div>";
            }
        }
    }
    ?>

    <form method="post" action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]); ?>">
        <div style="margin-bottom: 15px;">
            <label for="tutorial_title">教程标题:</label><br>
            <input type="text" id="tutorial_title" name="tutorial_title"
                   value="<?php echo isset($tutorial_title) ? htmlspecialchars($tutorial_title) : ''; ?>"
                   required style="width: 300px;">
        </div>

        <div style="margin-bottom: 15px;">
            <label for="tutorial_author">作者:</label><br>
            <input type="text" id="tutorial_author" name="tutorial_author"
                   value="<?php echo isset($tutorial_author) ? htmlspecialchars($tutorial_author) : ''; ?>"
                   required style="width: 300px;">
        </div>

        <div style="margin-bottom: 15px;">
            <label for="submission_date">提交日期 [yyyy-mm-dd]:</label><br>
            <input type="date" id="submission_date" name="submission_date"
                   value="<?php echo isset($submission_date) ? $submission_date : date('Y-m-d'); ?>"
                   style="width: 300px;">
        </div>

        <div>
            <input type="submit" value="添加教程">
        </div>
    </form>
</body>
</html>

使用其他编程语言插入数据

使用 Python

import mysql.connector
from mysql.connector import Error
from datetime import datetime

try:
    connection = mysql.connector.connect(
        host='localhost',
        user='username',
        password='password',
        database='TUTORIALS'
    )

    if connection.is_connected():
        cursor = connection.cursor()

        # 插入单条数据
        insert_query = """
        INSERT INTO tutorials_tbl
        (tutorial_title, tutorial_author, submission_date)
        VALUES (%s, %s, %s)
        """
        data = ('Learn Python', 'Jane Doe', datetime.now())
        cursor.execute(insert_query, data)
        connection.commit()
        print("记录插入成功，ID:", cursor.lastrowid)

        # 批量插入
        data_list = [
            ('Learn Java', 'John Smith', '2024-01-15'),
            ('Learn C++', 'Mike Johnson', '2024-01-16'),
            ('Learn JavaScript', 'Sarah Wilson', '2024-01-17')
        ]
        cursor.executemany(insert_query, data_list)
        connection.commit()
        print("批量插入成功，影响行数:", cursor.rowcount)

except Error as e:
    print("错误:", e)

finally:
    if connection.is_connected():
        cursor.close()
        connection.close()

使用 Node.js

const mysql = require('mysql2');

const connection = mysql.createConnection({
    host: 'localhost',
    user: 'username',
    password: 'password',
    database: 'TUTORIALS'
});

connection.connect((err) => {
    if (err) throw err;

    // 插入单条数据
    const sql = "INSERT INTO tutorials_tbl (tutorial_title, tutorial_author, submission_date) VALUES (?, ?, ?)";
    const values = ['Learn Node.js', 'Tom Brown', new Date()];

    connection.execute(sql, values, (err, result) => {
        if (err) throw err;
        console.log("记录插入成功，ID:", result.insertId);

        // 批量插入
        const multipleValues = [
            ['Learn React', 'Alice Green', new Date()],
            ['Learn Vue', 'Bob White', new Date()]
        ];

        connection.query("INSERT INTO tutorials_tbl (tutorial_title, tutorial_author, submission_date) VALUES ?",
                        [multipleValues], (err, result) => {
            if (err) throw err;
            console.log("批量插入成功，影响行数:", result.affectedRows);
            connection.end();
        });
    });
});

高级插入技巧

批量插入优化

-- 批量插入（推荐，效率高）
INSERT INTO tutorials_tbl
    (tutorial_title, tutorial_author, submission_date)
VALUES
    ('Title 1', 'Author 1', NOW()),
    ('Title 2', 'Author 2', NOW()),
    ('Title 3', 'Author 3', NOW());

插入时获取自动生成的值

<?php
// 使用LAST_INSERT_ID()获取自增ID
$stmt = $conn->prepare("INSERT INTO tutorials_tbl (tutorial_title, tutorial_author) VALUES (?, ?)");
$stmt->bind_param("ss", $title, $author);
$title = "New Tutorial";
$author = "New Author";
$stmt->execute();

// 获取插入的ID
$new_id = $stmt->insert_id;
echo "新记录的ID是: " . $new_id;
?>

使用INSERT ... ON DUPLICATE KEY UPDATE

-- 插入或更新（存在唯一键冲突时更新）
INSERT INTO page_views
    (page_url, view_count, last_viewed)
VALUES
    ('/home', 1, NOW())
ON DUPLICATE KEY UPDATE
    view_count = view_count + 1,
    last_viewed = NOW();

安全最佳实践

防止SQL注入

始终使用预处理语句：不要拼接SQL字符串
验证和过滤输入：对用户输入进行严格验证
使用参数化查询：确保数据与SQL语句分离

数据验证

<?php
// 数据验证示例
function validateTutorialData($title, $author, $date) {
    $errors = [];

    // 验证标题
    if (empty(trim($title))) {
        $errors[] = "标题不能为空";
    } elseif (strlen($title) > 100) {
        $errors[] = "标题不能超过100个字符";
    }

    // 验证作者
    if (empty(trim($author))) {
        $errors[] = "作者不能为空";
    } elseif (strlen($author) > 40) {
        $errors[] = "作者名称不能超过40个字符";
    }

    // 验证日期
    if (!empty($date) && !strtotime($date)) {
        $errors[] = "日期格式不正确";
    }

    return $errors;
}
?>

错误处理

在生产环境中记录详细的错误日志
对用户显示友好的错误信息
实现适当的重试机制
使用事务确保数据一致性

性能优化建议

批量插入优化

使用多值INSERT语句代替多个单条INSERT
在大量插入时暂时禁用索引
使用LOAD DATA INFILE进行大数据量导入

事务管理

<?php
// 使用事务确保数据一致性
try {
    $conn->beginTransaction();

    // 执行多个插入操作
    $stmt1 = $conn->prepare("INSERT INTO table1 ...");
    $stmt2 = $conn->prepare("INSERT INTO table2 ...");

    $stmt1->execute([$data1]);
    $stmt2->execute([$data2]);

    // 提交事务
    $conn->commit();
    echo "所有操作成功完成";

} catch (Exception $e) {
    // 回滚事务
    $conn->rollBack();
    echo "操作失败: " . $e->getMessage();
}
?>

连接管理

使用连接池减少连接开销
及时关闭数据库连接
合理设置连接超时时间

常见问题与解决方案

1. 重复键错误

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

解决方案：使用INSERT IGNORE或ON DUPLICATE KEY UPDATE。

2. 字段不能为NULL

ERROR 1048 (23000): Column 'column_name' cannot be null

解决方案：为NOT NULL字段提供有效值。

3. 数据截断警告

Warning: Data truncated for column 'column_name'

解决方案：检查数据长度是否超过字段定义。

4. 外键约束错误

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails

解决方案：确保引用的父表记录存在。

MYSQL教程

MySQL 插入数据

INSERT INTO 基本语法

完整语法结构

参数说明

通过命令行插入数据

基本插入操作

使用INSERT ... SELECT插入数据

插入数据时的特殊处理

使用 PHP 脚本插入数据

使用 MySQLi 面向对象方式（推荐）

使用 MySQLi 过程式方式

使用 PDO 方式

完整的PHP表单插入示例

使用其他编程语言插入数据

使用 Python

使用 Node.js

高级插入技巧

批量插入优化

插入时获取自动生成的值

使用INSERT ... ON DUPLICATE KEY UPDATE

安全最佳实践

防止SQL注入

数据验证

错误处理

性能优化建议

批量插入优化

事务管理

连接管理

常见问题与解决方案

1. 重复键错误

2. 字段不能为NULL

3. 数据截断警告

4. 外键约束错误