MariaDB数据插入 #

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

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

-- 指定列名插入
INSERT INTO users (name, email, age)
VALUES ('John', 'john@example.com', 25);

-- 插入后查看
SELECT * FROM users;
+----+------+-------------------+------+--------+---------------------+
| id | name | email             | age  | status | created_at          |
+----+------+-------------------+------+--------+---------------------+
|  1 | John | john@example.com  |   25 |      1 | 2024-01-15 10:00:00 |
+----+------+-------------------+------+--------+---------------------+

-- 插入所有列（按表定义顺序）
INSERT INTO users
VALUES (NULL, 'Jane', 'jane@example.com', 30, 1, NOW());

-- AUTO_INCREMENT列使用NULL或0
INSERT INTO users
VALUES (0, 'Bob', 'bob@example.com', 28, 1, NOW());

-- MariaDB特有语法
INSERT INTO users
SET name = 'Alice',
    email = 'alice@example.com',
    age = 22;

-- 一次插入多行
INSERT INTO users (name, email, age) VALUES
    ('User1', 'user1@example.com', 20),
    ('User2', 'user2@example.com', 25),
    ('User3', 'user3@example.com', 30),
    ('User4', 'user4@example.com', 35);

-- 性能优势：
-- 1. 减少网络往返
-- 2. 减少SQL解析次数
-- 3. 批量事务提交

-- 大批量插入优化
-- 1. 临时禁用索引（非唯一索引）
ALTER TABLE users DISABLE KEYS;
INSERT INTO users (name, email, age) VALUES
    -- 大量数据...
    ('User10000', 'user10000@example.com', 25);
ALTER TABLE users ENABLE KEYS;

-- 2. 使用LOAD DATA INFILE（更快）
LOAD DATA INFILE '/path/to/users.csv'
INTO TABLE users
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(name, email, age);

-- 3. 调整配置
SET unique_checks = 0;
SET foreign_key_checks = 0;
SET autocommit = 0;
-- 批量插入...
COMMIT;
SET unique_checks = 1;
SET foreign_key_checks = 1;
SET autocommit = 1;

-- 从其他表复制数据
INSERT INTO users_archive (name, email, age, status)
SELECT name, email, age, status
FROM users
WHERE created_at < '2023-01-01';

-- 创建备份表并复制
CREATE TABLE users_backup LIKE users;
INSERT INTO users_backup SELECT * FROM users;

-- 条件复制
INSERT INTO active_users (name, email)
SELECT name, email
FROM users
WHERE status = 1;

-- 从多表查询插入
INSERT INTO order_summary (order_id, customer_name, total_amount)
SELECT 
    o.id,
    c.name,
    SUM(oi.quantity * oi.price)
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN order_items oi ON o.id = oi.order_id
GROUP BY o.id, c.name;

-- 使用子查询
INSERT INTO user_stats (user_id, order_count)
SELECT 
    u.id,
    (SELECT COUNT(*) FROM orders WHERE user_id = u.id)
FROM users u;

-- 忽略错误，继续插入其他行
INSERT IGNORE INTO users (id, name, email) VALUES
    (1, 'John', 'john@example.com'),   -- 已存在，忽略
    (2, 'Jane', 'jane@example.com'),   -- 插入成功
    (3, 'Bob', 'bob@example.com');     -- 插入成功

-- 查看警告
SHOW WARNINGS;

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

-- 使用新值
INSERT INTO users (id, name, email) VALUES (1, 'John', 'john@example.com')
ON DUPLICATE KEY UPDATE
    name = VALUES(name),
    email = VALUES(email);

-- MariaDB 10.3.3+ 可以使用别名
INSERT INTO users (id, name, email) VALUES (1, 'John', 'john@example.com') AS new
ON DUPLICATE KEY UPDATE
    name = new.name,
    email = new.email;

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

-- 注意：
-- 1. 会删除旧行，插入新行
-- 2. AUTO_INCREMENT会增加
-- 3. 会触发DELETE和INSERT触发器
-- 4. 外键约束可能导致失败

-- 获取最后插入的AUTO_INCREMENT值
INSERT INTO users (name, email) VALUES ('Test', 'test@example.com');
SELECT LAST_INSERT_ID();

-- 在应用程序中使用
-- Python: cursor.lastrowid
-- PHP: mysqli_insert_id()
-- Java: Statement.getGeneratedKeys()

-- MariaDB 10.5+ 支持RETURNING
INSERT INTO users (name, email, age)
VALUES ('John', 'john@example.com', 25)
RETURNING id, name, created_at;

+----+------+---------------------+
| id | name | created_at          |
+----+------+---------------------+
|  1 | John | 2024-01-15 10:00:00 |
+----+------+---------------------+

-- 批量插入返回
INSERT INTO users (name, email) VALUES
    ('User1', 'user1@example.com'),
    ('User2', 'user2@example.com')
RETURNING id, name;

-- 创建JSON列的表
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    attributes JSON
);

-- 插入JSON字符串
INSERT INTO products (name, attributes)
VALUES ('Laptop', '{"brand": "Dell", "ram": "16GB", "storage": "512GB"}');

-- 使用JSON函数
INSERT INTO products (name, attributes)
VALUES ('Phone', JSON_OBJECT('brand', 'Apple', 'model', 'iPhone 15'));

-- 插入JSON数组
INSERT INTO products (name, attributes)
VALUES ('Tablet', JSON_ARRAY('iPad', 'Samsung Tab', 'Surface'));

-- 插入日期时间
INSERT INTO events (name, event_date, event_time, event_datetime)
VALUES (
    'Meeting',
    '2024-01-15',
    '10:30:00',
    '2024-01-15 10:30:00'
);

-- 使用函数
INSERT INTO events (name, created_at)
VALUES ('Event1', NOW());

INSERT INTO events (name, created_at)
VALUES ('Event2', CURRENT_TIMESTAMP);

-- 插入时间戳
INSERT INTO logs (message, log_time)
VALUES ('Error occurred', UNIX_TIMESTAMP());

-- 创建包含BLOB的表
CREATE TABLE files (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    file_data LONGBLOB,
    file_type VARCHAR(50)
);

-- 从文件加载
INSERT INTO files (name, file_data, file_type)
SELECT 
    'document.pdf',
    LOAD_FILE('/path/to/document.pdf'),
    'application/pdf';

-- 注意：LOAD_FILE需要FILE权限和可读文件

-- 优化配置
SET GLOBAL innodb_buffer_pool_size = 2G;
SET GLOBAL innodb_log_file_size = 512M;
SET GLOBAL innodb_flush_log_at_trx_commit = 2;

-- 批量插入
START TRANSACTION;
INSERT INTO users (name, email) VALUES
    ('User1', 'user1@example.com'),
    ('User2', 'user2@example.com'),
    -- ... 更多数据
    ('User1000', 'user1000@example.com');
COMMIT;

-- INSERT DELAYED（已弃用，MariaDB 10.0-10.4支持）
-- 数据先放入队列，稍后批量插入
INSERT DELAYED INTO logs (message) VALUES ('Log entry');

-- 推荐替代方案：使用存储过程或应用层批量插入

-- 错误：直接拼接SQL
-- INSERT INTO users (name) VALUES ('John'); DROP TABLE users;--')

-- 正确：使用预处理语句
PREPARE stmt FROM 'INSERT INTO users (name, email) VALUES (?, ?)';
SET @name = 'John';
SET @email = 'john@example.com';
EXECUTE stmt USING @name, @email;
DEALLOCATE PREPARE stmt;

-- 应用程序使用参数化查询
-- Python: cursor.execute("INSERT INTO users VALUES (%s, %s)", (name, email))
-- PHP: $stmt = $pdo->prepare("INSERT INTO users VALUES (?, ?)")
-- Java: PreparedStatement pstmt = conn.prepareStatement("INSERT INTO users VALUES (?, ?)")

-- 只授予必要的权限
GRANT INSERT ON mydb.users TO 'app_user'@'localhost';

-- 限制插入权限
REVOKE INSERT ON mydb.users FROM 'readonly'@'localhost';

-- 错误：Duplicate entry '1' for key 'PRIMARY'
INSERT INTO users (id, name) VALUES (1, 'John');

-- 解决方案1：使用AUTO_INCREMENT
INSERT INTO users (name) VALUES ('John');

-- 解决方案2：使用ON DUPLICATE KEY UPDATE
INSERT INTO users (id, name) VALUES (1, 'John')
ON DUPLICATE KEY UPDATE name = VALUES(name);

-- 解决方案3：使用INSERT IGNORE
INSERT IGNORE INTO users (id, name) VALUES (1, 'John');

-- 错误：Duplicate entry 'john@example.com' for key 'email'
INSERT INTO users (name, email) VALUES ('John', 'john@example.com');

-- 解决方案
INSERT INTO users (name, email) VALUES ('John', 'john@example.com')
ON DUPLICATE KEY UPDATE name = VALUES(name);

-- 错误：Cannot add or update a child row: a foreign key constraint fails
INSERT INTO orders (user_id, amount) VALUES (999, 100);

-- 解决方案：确保外键值存在
INSERT INTO users (id, name) VALUES (999, 'User999');
INSERT INTO orders (user_id, amount) VALUES (999, 100);

-- 或临时禁用外键检查
SET foreign_key_checks = 0;
INSERT INTO orders (user_id, amount) VALUES (999, 100);
SET foreign_key_checks = 1;

-- 错误：Incorrect integer value: 'abc' for column 'age'
INSERT INTO users (name, age) VALUES ('John', 'abc');

-- 解决方案：使用正确的数据类型
INSERT INTO users (name, age) VALUES ('John', 25);

-- 或使用类型转换
INSERT INTO users (name, age) VALUES ('John', CAST('25' AS SIGNED));