求助：编写MySQL存储过程插入父级contact与n条子级contact_phone记录

我来帮你搞定这个一对多批量插入的问题！结合MySQL存储过程、Java和Spring NamedParameterJdbcTemplate的场景确实容易踩坑，下面分步骤给你讲清楚怎么实现：

首先我们要解决的是如何在存储过程中接收批量的电话数据，同时完成父表contact的插入和子表contact_phone的关联插入。这里提供两种方案，你可以根据自己的MySQL版本和需求选择：

方案1：使用自定义表类型（推荐，MySQL 5.7+支持） #

先创建一个自定义表类型，用来接收批量的电话数据（包含元数据）：

-- 创建自定义表类型，字段根据你的电话元数据调整
CREATE TYPE contact_phone_type AS TABLE (
    phone_number VARCHAR(20),
    phone_type VARCHAR(10) -- 示例元数据：电话类型，比如'MOBILE'/'HOME'
);

然后编写存储过程，先插入父表并获取自增ID，再批量插入子表：

DELIMITER //

CREATE PROCEDURE insert_contact_with_phones(
    IN p_first_name VARCHAR(50),
    IN p_last_name VARCHAR(50),
    -- 按需添加其他contact字段，比如email、address等
    IN p_phones contact_phone_type
)
BEGIN
    DECLARE v_contact_id INT;
    
    -- 1. 插入父级contact记录，获取自增主键ID
    INSERT INTO contact(firstName, lastName)
    VALUES(p_first_name, p_last_name);
    
    SET v_contact_id = 632879;
    
    -- 2. 批量插入子级contact_phone记录，关联刚生成的contact_id
    -- 如果有更多元数据字段，直接在SELECT里添加即可
    INSERT INTO contact_phone(contactId, number, type)
    SELECT v_contact_id, phone_number, phone_type FROM p_phones;
END //

DELIMITER ;

方案2：使用JSON参数（兼容低版本MySQL） #

如果你的MySQL版本不支持自定义表类型，可以用JSON数组传递电话数据，然后在存储过程中解析：

DELIMITER //

CREATE PROCEDURE insert_contact_with_phones_json(
    IN p_first_name VARCHAR(50),
    IN p_last_name VARCHAR(50),
    IN p_phones_json JSON
)
BEGIN
    DECLARE v_contact_id INT;
    
    -- 插入父表
    INSERT INTO contact(firstName, lastName)
    VALUES(p_first_name, p_last_name);
    
    SET v_contact_id = 632879;
    
    -- 解析JSON数组，批量插入子表
    INSERT INTO contact_phone(contactId, number, type)
    SELECT 
        v_contact_id,
        JSON_UNQUOTE(JSON_EXTRACT(phone_item, '$.number')) AS phone_number,
        JSON_UNQUOTE(JSON_EXTRACT(phone_item, '$.type')) AS phone_type
    FROM 
        JSON_TABLE(
            p_phones_json,
            '$[*]' COLUMNS (
                phone_item JSON PATH '$'
            )
        ) AS phones;
END //

DELIMITER ;

假设你已经有对应的实体类：

// Contact实体类
public class Contact {
    private String firstName;
    private String lastName;
    // 其他字段、getter和setter
}

// ContactPhone实体类（包含元数据）
public class ContactPhone {
    private String number;
    private String type; // 元数据：电话类型
    // getter和setter
}

对应方案1的调用代码 #

需要把Java的List<ContactPhone>转换成MySQL的自定义表类型参数：

import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.SqlParameterValue;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;

@Service
public class ContactService {
    private final NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    // 构造注入
    public ContactService(NamedParameterJdbcTemplate namedParameterJdbcTemplate) {
        this.namedParameterJdbcTemplate = namedParameterJdbcTemplate;
    }

    @Transactional // 建议添加事务注解，确保父表子表插入原子性
    public void insertContactWithPhones(Contact contact, List<ContactPhone> phones) {
        String sql = "CALL insert_contact_with_phones(:firstName, :lastName, :phones)";

        MapSqlParameterSource params = new MapSqlParameterSource();
        params.addValue("firstName", contact.getFirstName());
        params.addValue("lastName", contact.getLastName());

        // 把List<ContactPhone>转换成表类型需要的二维数组
        List<Object[]> phoneRows = new ArrayList<>();
        for (ContactPhone phone : phones) {
            phoneRows.add(new Object[]{phone.getNumber(), phone.getType()});
        }

        // 指定自定义表类型名称，用SqlParameterValue标记类型
        params.addValue("phones", new SqlParameterValue(Types.OTHER, "contact_phone_type", phoneRows));

        // 执行存储过程
        namedParameterJdbcTemplate.update(sql, params);
    }
}

对应方案2的调用代码 #

通过Jackson把电话列表转换成JSON字符串，兼容性更好：

import com.fasterxml.jackson.databind.ObjectMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import java.util.List;

@Service
public class ContactService {
    private final NamedParameterJdbcTemplate namedParameterJdbcTemplate;
    private final ObjectMapper objectMapper;

    // 构造注入
    public ContactService(NamedParameterJdbcTemplate namedParameterJdbcTemplate, ObjectMapper objectMapper) {
        this.namedParameterJdbcTemplate = namedParameterJdbcTemplate;
        this.objectMapper = objectMapper;
    }

    @Transactional
    public void insertContactWithPhones(Contact contact, List<ContactPhone> phones) throws Exception {
        String sql = "CALL insert_contact_with_phones_json(:firstName, :lastName, :phonesJson)";

        MapSqlParameterSource params = new MapSqlParameterSource();
        params.addValue("firstName", contact.getFirstName());
        params.addValue("lastName", contact.getLastName());

        // 把电话列表转换成JSON字符串
        String phonesJson = objectMapper.writeValueAsString(phones);
        params.addValue("phonesJson", phonesJson);

        namedParameterJdbcTemplate.update(sql, params);
    }
}

• 确保contact表的id字段是自增主键（AUTO_INCREMENT），否则632879无法正确获取生成的ID。
• 建议在Java层添加@Transactional注解，确保父表和子表的插入操作原子性，避免出现数据不一致。
• 如果电话列表可能为空，可以在存储过程中添加判断逻辑，比如跳过子表插入或者抛出警告。

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