mysql多表添加数据

基础概念

MySQL多表添加数据通常涉及到数据库中的关联关系，如一对一、一对多或多对多关系。在进行多表数据添加时，需要确保数据的完整性和一致性。

类型

一对一关系：一个表中的记录与另一个表中的记录一一对应。
一对多关系：一个表中的记录可以与另一个表中的多个记录对应。
多对多关系：两个表中的记录都可以相互对应。

应用场景

例如，在电商系统中，用户表（users）和订单表（orders）之间是一对多关系；订单表和商品表（products）之间是多对多关系。

示例代码

假设我们有三个表：users、orders和order_items，分别表示用户、订单和订单项。

表结构

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL
);

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    order_date DATETIME,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE order_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT,
    product_name VARCHAR(255),
    quantity INT,
    FOREIGN KEY (order_id) REFERENCES orders(id)
);

添加数据

-- 插入用户
INSERT INTO users (name) VALUES ('Alice');
INSERT INTO users (name) VALUES ('Bob');

-- 获取插入的用户ID
SET @user_id_alice = LAST_INSERT_ID();
SET @user_id_bob = LAST_INSERT_ID() + 1;

-- 插入订单
INSERT INTO orders (user_id, order_date) VALUES (@user_id_alice, NOW());
INSERT INTO orders (user_id, order_date) VALUES (@user_id_bob, NOW());

-- 获取插入的订单ID
SET @order_id_alice = LAST_INSERT_ID();
SET @order_id_bob = LAST_INSERT_ID();

-- 插入订单项
INSERT INTO order_items (order_id, product_name, quantity) VALUES (@order_id_alice, 'Product A', 2);
INSERT INTO order_items (order_id, product_name, quantity) VALUES (@order_id_alice, 'Product B', 1);
INSERT INTO order_items (order_id, product_name, quantity) VALUES (@order_id_bob, 'Product C', 3);

常见问题及解决方法

问题1：外键约束失败

原因：插入的数据违反了外键约束，例如引用了不存在的主键。

解决方法：确保插入的数据符合外键约束，或者在插入前先插入相关的主键数据。

-- 确保用户存在
INSERT INTO users (name) VALUES ('Charlie');

-- 再插入订单
INSERT INTO orders (user_id, order_date) VALUES (LAST_INSERT_ID(), NOW());

问题2：事务处理

原因：在多表操作中，如果某个操作失败，可能会导致数据不一致。

解决方法：使用事务来确保所有操作要么全部成功，要么全部失败。

START TRANSACTION;

INSERT INTO users (name) VALUES ('David');
SET @user_id_david = LAST_INSERT_ID();

INSERT INTO orders (user_id, order_date) VALUES (@user_id_david, NOW());
SET @order_id_david = LAST_INSERT_ID();

INSERT INTO order_items (order_id, product_name, quantity) VALUES (@order_id_david, 'Product D', 4);

COMMIT;