如何通过单次SQL请求向users表批量插入无重复记录？

Great to see you're looking to optimize your insert logic! The key here is to create a temporary dataset of all your new records, then filter out any duplicates against the existing users table before inserting. Here are two reliable approaches tailored to your use case:

Approach 1: Using VALUES + NOT EXISTS (No Table Structure Changes Needed) #

This method builds directly on your original single-record logic, scaling it to handle multiple entries without altering your table.

SQL Syntax #

INSERT INTO `users` (`NAME`, `CREATIONDATE`)
SELECT new_data.name, CURRENT_TIMESTAMP
FROM (
    -- Replace these with your list of new names
    VALUES 
        ('Alice'),
        ('Bob'),
        ('Charlie')
) AS new_data(name)
WHERE NOT EXISTS (
    -- Only keep records where the name doesn't already exist
    SELECT 1 FROM `users` u WHERE u.NAME = new_data.name
);

How It Works #

1. The inner VALUES clause creates a temporary table (new_data) holding all the usernames you want to insert.
2. The NOT EXISTS subquery filters out any names that are already present in the users table.
3. We insert only the filtered records, with CREATIONDATE automatically set to the current timestamp.

PHP Implementation (Safe & Dynamic) #

To make this work with dynamic data in PHP, use prepared statements to avoid SQL injection:

// Your list of new names to insert
$newNames = ['Alice', 'Bob', 'Charlie', 'Dave'];

// Build placeholders for the VALUES clause
$valuePlaceholders = [];
$params = [];
foreach ($newNames as $name) {
    $valuePlaceholders[] = '(?)';
    $params[] = $name;
}
$valuesClause = implode(', ', $valuePlaceholders);

// Construct the full SQL query
$sql = "
INSERT INTO `users` (`NAME`, `CREATIONDATE`)
SELECT new_data.name, CURRENT_TIMESTAMP
FROM (
    VALUES $valuesClause
) AS new_data(name)
WHERE NOT EXISTS (
    SELECT 1 FROM `users` u WHERE u.NAME = new_data.name
)
";

// Execute with PDO (recommended for security)
$pdo = new PDO('mysql:host=your_host;dbname=your_db', 'user', 'pass');
$stmt = $pdo->prepare($sql);
$stmt->execute($params);

Approach 2: INSERT ... ON DUPLICATE KEY UPDATE (Requires Unique Index) #

If you can modify your table structure, this is a cleaner and often more performant option. First, add a unique index on the NAME column to enforce uniqueness:

Step 1: Add Unique Index #

ALTER TABLE `users` ADD UNIQUE INDEX idx_users_name (`NAME`);

Step 2: Insert Multiple Records #

INSERT INTO `users` (`NAME`, `CREATIONDATE`)
VALUES 
    ('Alice', CURRENT_TIMESTAMP),
    ('Bob', CURRENT_TIMESTAMP),
    ('Charlie', CURRENT_TIMESTAMP)
ON DUPLICATE KEY UPDATE NAME = NAME; -- No-op: skips duplicate records

How It Works #

When inserting a record that would violate the unique index (i.e., the name already exists), the ON DUPLICATE KEY UPDATE clause runs a harmless update (setting the name to itself) instead of throwing an error. This effectively skips duplicates in a single query.

Which Approach to Choose? #

• Use Approach 1 if you can't modify the table structure, or need compatibility with older database versions.
• Use Approach 2 for cleaner syntax and better performance (the database uses the unique index to quickly detect duplicates).

Key Notes #

• Always use prepared statements in PHP to prevent SQL injection (never directly concatenate user input into your SQL!).
• If you need to check duplicates against multiple fields (not just NAME), adjust the NOT EXISTS subquery or the unique index accordingly.

内容的提问来源于stack exchange，提问作者Xue Fang