MySQL数据插入 #

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

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    age INT,
    email VARCHAR(100),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO users (name, age, email)
VALUES ('John', 25, 'john@example.com');

INSERT INTO users
VALUES (NULL, 'Jane', 30, 'jane@example.com', NOW());

-- 自增列可以传NULL或0
INSERT INTO users
VALUES (0, 'Bob', 28, 'bob@example.com', DEFAULT);

-- 省略列名时，必须按表定义顺序提供所有列的值
INSERT INTO users
VALUES (NULL, 'Alice', 22, 'alice@example.com', NOW());

INSERT INTO users (name, age, email) VALUES
    ('John', 25, 'john@example.com'),
    ('Jane', 30, 'jane@example.com'),
    ('Bob', 28, 'bob@example.com'),
    ('Alice', 22, 'alice@example.com');

-- 推荐方式：一条语句插入多行
INSERT INTO users (name, age, email) VALUES
    ('User1', 20, 'user1@example.com'),
    ('User2', 21, 'user2@example.com'),
    ('User3', 22, 'user3@example.com');

-- 不推荐：多条单行插入
INSERT INTO users (name, age, email) VALUES ('User1', 20, 'user1@example.com');
INSERT INTO users (name, age, email) VALUES ('User2', 21, 'user2@example.com');
INSERT INTO users (name, age, email) VALUES ('User3', 22, 'user3@example.com');

INSERT INTO users
SET name = 'John',
    age = 25,
    email = 'john@example.com';

-- 从其他表复制数据
INSERT INTO users_backup (name, age, email)
SELECT name, age, email FROM users;

-- 带条件复制
INSERT INTO users_2024 (name, age, email)
SELECT name, age, email 
FROM users 
WHERE YEAR(created_at) = 2024;

-- 忽略重复键错误
INSERT IGNORE INTO users (id, name, email)
VALUES (1, 'John', 'john@example.com');

-- 如果id=1已存在，不会报错，只是跳过

-- 如果存在则更新，不存在则插入
INSERT INTO users (id, name, age, email)
VALUES (1, 'John', 25, 'john@example.com')
ON DUPLICATE KEY UPDATE
    name = VALUES(name),
    age = VALUES(age),
    email = VALUES(email);

-- MySQL 8.0+ 新语法
INSERT INTO users (id, name, age, email)
VALUES (1, 'John', 25, 'john@example.com') AS new
ON DUPLICATE KEY UPDATE
    name = new.name,
    age = new.age,
    email = new.email;

-- 如果存在则删除后插入，不存在则插入
REPLACE INTO users (id, name, age, email)
VALUES (1, 'John', 26, 'john@example.com');

-- 注意：REPLACE会删除旧行再插入新行
-- 自增ID会改变

INSERT INTO users (name, age, email, created_at)
VALUES ('John', 25, 'john@example.com', DEFAULT);

-- created_at使用默认值CURRENT_TIMESTAMP

-- age有默认值0，created_at有默认值
INSERT INTO users (name, email)
VALUES ('John', 'john@example.com');

INSERT INTO users (name, age, email)
VALUES ('John', NULL, 'john@example.com');

-- 如果age允许NULL，可以省略
INSERT INTO users (name, email)
VALUES ('John', 'john@example.com');

INSERT INTO users (name, age, email, created_at)
VALUES ('John', 25, 'john@example.com', NOW());

INSERT INTO products (name, price, discount_price)
VALUES ('Product', 100.00, 100.00 * 0.9);

INSERT INTO logs (message, created_at)
VALUES ('System started', CURRENT_TIMESTAMP);

INSERT INTO users (name, uuid)
VALUES ('John', UUID());

INSERT INTO users (name, age, email)
VALUES ('John', 25, 'john@example.com');

SELECT LAST_INSERT_ID();
-- 返回最后插入的自增ID

-- 在应用程序中使用
-- PHP: $id = $pdo->lastInsertId();
// Java: long id = statement.getGeneratedKeys();

INSERT INTO users (name, age, email) VALUES
    ('User1', 20, 'user1@example.com'),
    ('User2', 21, 'user2@example.com'),
    ('User3', 22, 'user3@example.com');

SELECT LAST_INSERT_ID();
-- 返回第一个插入的ID
-- 后续ID依次递增

-- 推荐：一次插入多行
INSERT INTO users (name, age, email) VALUES
    ('User1', 20, 'user1@example.com'),
    ('User2', 21, 'user2@example.com'),
    ('User3', 22, 'user3@example.com');

-- 不推荐：多次单行插入
INSERT INTO users (name, age, email) VALUES ('User1', 20, 'user1@example.com');
INSERT INTO users (name, age, email) VALUES ('User2', 21, 'user2@example.com');
INSERT INTO users (name, age, email) VALUES ('User3', 22, 'user3@example.com');

-- 禁用索引
ALTER TABLE users DISABLE KEYS;

-- 批量插入数据
INSERT INTO users (name, age, email) VALUES
    -- ... 大量数据

-- 启用索引
ALTER TABLE users ENABLE KEYS;

-- 从文件导入（更快）
LOAD DATA INFILE '/path/to/users.csv'
INTO TABLE users
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(name, age, email);

-- 错误信息
INSERT INTO users (id, name) VALUES (1, 'John');
-- ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

-- 解决方案
INSERT IGNORE INTO users (id, name) VALUES (1, 'John');
-- 或
INSERT INTO users (id, name) VALUES (1, 'John')
ON DUPLICATE KEY UPDATE name = 'John';

-- 错误信息
INSERT INTO users (age, email) VALUES (25, 'john@example.com');
-- ERROR 1364 (HY000): Field 'name' doesn't have a default value

-- 解决方案：提供name值
INSERT INTO users (name, age, email) VALUES ('John', 25, 'john@example.com');

-- 错误信息
INSERT INTO users (name, age) VALUES ('John', 'abc');
-- ERROR 1366 (HY000): Incorrect integer value: 'abc' for column 'age'

-- 解决方案：提供正确类型
INSERT INTO users (name, age) VALUES ('John', 25);