MySQL - INSERT 查询

精通 MySQL 的 `INSERT` 语句

表创建后，它只是一个空的蓝图。要让它“活”起来，你需要向其中填充数据。INSERT 语句是 SQL (结构化查询语言) 中用于向表中添加新行的基本命令。它是构成大多数数据库驱动应用程序骨干的 CRUD (Create, Read, Update, Delete，即创建、读取、更新、删除) 操作中的 ‘C’ (创建) 部分。

`INSERT` 核心语法

插入数据最常见也最健壮的方法是同时指定列名和要插入的值。这种做法可以确保即使表结构发生变化（例如，添加或重新排序列），你的查询也能保持稳定。

语法

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

字符串和日期值必须用单引号括起来（例如，‘Hello World’）。数值不需要引号。

示例：一个现代的 `products` 表

让我们使用现代数据类型和约定来创建一个更真实的 products (产品) 表。

CREATE TABLE `products` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `sku` VARCHAR(100) NOT NULL UNIQUE,
  `name` VARCHAR(255) NOT NULL,
  `price` DECIMAL(10, 2) NOT NULL,
  `stock_quantity` INT UNSIGNED NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

现在，让我们向新表中插入单个产品：

INSERT INTO `products` (sku, name, price, stock_quantity)
VALUES ('WM-101-BLK', 'Ergonomic Wireless Mouse', 29.99, 150);

高级 `INSERT` 技巧

插入多行

为了高效地插入多行，你可以在单个 INSERT 语句中列出多个值元组。这比为每行运行单独的 INSERT 语句要快得多，因为它减少了网络开销和数据库负载。

INSERT INTO `products` (sku, name, price, stock_quantity)
VALUES
  ('KB-MECH-01', 'Mechanical Keyboard', 119.95, 75),
  ('MON-4K-27', '27-inch 4K Monitor', 349.50, 40),
  ('HUB-USBC-8', '8-in-1 USB-C Hub', 45.00, 210);

最佳实践：在 INSERT 语句中始终指定列名。不带列名的 INSERT INTO table VALUES (...) 语法不够健壮，如果表的列顺序发生变化，它会出错。

从另一个表插入数据 (`INSERT ... SELECT`)

这项强大的技术允许你从一个表复制和转换数据到另一个表。它非常适用于归档旧记录、创建汇总表或迁移数据。

示例

让我们创建一个用于存放打折产品的表，并从 products 表中填充价格低于 $50 的商品。

CREATE TABLE `discounted_products` LIKE `products`;

INSERT INTO `discounted_products` (id, sku, name, price, stock_quantity, created_at)
SELECT id, sku, name, price, stock_quantity, created_at
FROM `products`
WHERE price < 50.00;

旧文档中提到的 INSERT ... TABLE 语法是非标准的，在现代 MySQL 版本中已被弃用或移除。始终使用标准的 INSERT ... SELECT 来实现此目的。

使用 `ON DUPLICATE KEY UPDATE` 进行数据“更新或插入” (Upsert)

一个常见需求是插入新行，但如果具有相同唯一键的行已经存在，则改为更新该行。这称为“更新或插入” (upsert) 操作。MySQL 提供了一种简洁、原子化的方法来实现这一点。

示例

让我们尝试插入一个具有现有 sku (库存单位) 的产品。它不会失败，而是会更新价格并增加库存数量。

INSERT INTO `products` (sku, name, price, stock_quantity)
VALUES ('WM-101-BLK', 'Ergonomic Wireless Mouse v2', 32.50, 50)
ON DUPLICATE KEY UPDATE
  price = VALUES(price),
  stock_quantity = stock_quantity + VALUES(stock_quantity);

VALUES(column_name) 函数引用的是原本要从 VALUES 子句中插入的值。

通过客户端程序插入数据：一种安全的方法

从应用程序（如 Node.js、Python 或 PHP）插入数据时，防止 SQL 注入漏洞至关重要。最佳实践是使用预处理语句（或参数化查询），你可以在其中分别发送 SQL 模板和数据。

示例：现代客户端实现

Node.js (TypeScript)
PHP (PDO)
Java (JDBC)
Python



// 使用 mysql2/promise 实现现代 async/await 语法和性能。
import mysql from 'mysql2/promise';

async function addProduct(product) {
  let connection;
  try {
    connection = await mysql.createConnection({
      host: 'localhost', user: 'root', password: 'password', database: 'your_db_name'
    });
    const sql = 'INSERT INTO products (sku, name, price, stock_quantity) VALUES (?, ?, ?, ?)';
    const [result] = await connection.execute(sql, [product.sku, product.name, product.price, product.stock]);
    console.log('Product inserted with ID:', result.insertId);
  } catch (error) {
    console.error('Error inserting product:', error);
  } finally {
    if (connection) await connection.end();
  }
}

addProduct({ sku: 'TS-TEE-LG', name: 'TypeScript T-Shirt', price: 24.99, stock: 100 });



// 使用 PDO 是 PHP 中现代、推荐的标准，因为它具有一致性和安全功能。
$host = 'localhost';
$db   = 'your_db_name';
$user = 'root';
$pass = 'password';
$charset = 'utf8mb4';

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];

try {
     $pdo = new PDO($dsn, $user, $pass, $options);
} catch (\PDOException $e) {
     throw new \PDOException($e->getMessage(), (int)$e->getCode());
}

$sql = "INSERT INTO products (sku, name, price, stock_quantity) VALUES (?, ?, ?, ?)";
$stmt= $pdo->prepare($sql);
$stmt->execute(['PHP-MUG-1', 'PHP Elephant Mug', 15.00, 50]);
$id = $pdo->lastInsertId();
echo "New record created successfully. Last inserted ID is: " . $id;



// 现代 Java 使用 try-with-resources 实现自动资源管理和 PreparedStatement。
import java.sql.*;

public class InsertProduct {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/your_db_name";
        String user = "root";
        String password = "password";
        String sql = "INSERT INTO products(sku, name, price, stock_quantity) VALUES(?, ?, ?, ?)";

        try (Connection conn = DriverManager.getConnection(url, user, password);
             PreparedStatement pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {

            pstmt.setString(1, "J-BEAN-CAN");
            pstmt.setString(2, "Java Beans Coffee");
            pstmt.setDouble(3, 19.99);
            pstmt.setInt(4, 80);

            int affectedRows = pstmt.executeUpdate();

            if (affectedRows > 0) {
                try (ResultSet generatedKeys = pstmt.getGeneratedKeys()) {
                    if (generatedKeys.next()) {
                        System.out.println("Product inserted with ID: " + generatedKeys.getLong(1));
                    }
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}



# 使用 mysql-connector-python 与上下文管理器实现安全的连接处理。
import mysql.connector
from mysql.connector import errorcode

def insert_product(product_data):
    try:
        # 为清晰起见，使用字典作为连接参数
        conn_config = {'user': 'root', 'password': 'password', 'host': '127.0.0.1', 'database': 'your_db_name'}
        with mysql.connector.connect(**conn_config) as conn:
            with conn.cursor() as cursor:
                sql = "INSERT INTO products (sku, name, price, stock_quantity) VALUES (%s, %s, %s, %s)"
                cursor.execute(sql, product_data)
                conn.commit() # 重要：将更改提交到数据库
                print(f"Product inserted with ID: {cursor.lastrowid}")

    except mysql.connector.Error as err:
        if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
            print("Something is wrong with your user name or password")
        elif err.errno == errorcode.ER_BAD_DB_ERROR:
            print("Database does not exist")
        else:
            print(err)

# 用于插入的数据元组
new_product = ('PY-SNAKE-PLUSH', 'Python Plush Toy', 25.50, 120)
insert_product(new_product)

常见错误和调试

Constraint Violation (约束违规)： 尝试将 NULL 插入 NOT NULL 列，或将重复值插入 UNIQUE (唯一) 或 PRIMARY KEY (主键) 列。数据库将拒绝插入并返回错误。
Data Type Mismatch (数据类型不匹配)： 将字符串如 ‘abc’ 插入 INT (整型) 列。MySQL 可能会尝试转换它（通常转换为 0），导致意外数据，或者根据服务器的 SQL 模式完全失败。
Column Count Mismatch (列计数不匹配)： 列列表中的列数与 VALUES 列表中的值数不匹配。查询将失败。
调试技巧： 如果多行插入语句失败或有警告，你可以在之后立即运行 SHOW WARNINGS; 来获取关于每行具体出了什么问题的更详细信息。

MySQL - INSERT 查询

精通 MySQL 的 INSERT 语句

INSERT 核心语法

语法

示例：一个现代的 products 表

高级 INSERT 技巧