PostgreSQL导入JSON及客户数据增改逻辑实现与PHP加载问题

一、PostgreSQL表结构设计 #

创建兼容部分数据的客户表，给优先级匹配字段添加唯一约束避免重复：

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    phone VARCHAR(20) UNIQUE, -- 优先级1：手机号
    email VARCHAR(255) UNIQUE, -- 优先级2：邮箱
    google_id VARCHAR(100) UNIQUE, -- 优先级3：GoogleID
    telegram_chat_id BIGINT UNIQUE, -- 优先级4：Telegram聊天ID
    name VARCHAR(255),
    address TEXT,
    birthday DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

二、优先级匹配的UPSERT逻辑 #

用CTE先定位匹配客户，再执行插入/更新，严格遵循更新规则：

WITH input_data AS (
    -- 单条JSON数据示例，批量处理用json_populate_recordset
    SELECT * FROM json_populate_record(NULL::customers, '{"phone": "13800138000", "name": "张三", "address": ""}')
),
matched_customer AS (
    SELECT customer_id
    FROM customers
    WHERE 
        -- 按优先级依次匹配，非空非空串才参与匹配
        phone = (SELECT phone FROM input_data WHERE phone IS NOT NULL AND phone != '')
        OR email = (SELECT email FROM input_data WHERE email IS NOT NULL AND email != '')
        OR google_id = (SELECT google_id FROM input_data WHERE google_id IS NOT NULL AND google_id != '')
        OR telegram_chat_id = (SELECT telegram_chat_id FROM input_data WHERE telegram_chat_id IS NOT NULL)
    LIMIT 1 -- 唯一约束保证不会出现多条匹配
)
INSERT INTO customers (phone, email, google_id, telegram_chat_id, name, address, birthday)
SELECT phone, email, google_id, telegram_chat_id, name, address, birthday
FROM input_data
ON CONFLICT (customer_id) DO UPDATE SET
    phone = CASE WHEN EXCLUDED.phone IS NULL THEN customers.phone WHEN EXCLUDED.phone = '' THEN NULL ELSE EXCLUDED.phone END,
    email = CASE WHEN EXCLUDED.email IS NULL THEN customers.email WHEN EXCLUDED.email = '' THEN NULL ELSE EXCLUDED.email END,
    google_id = CASE WHEN EXCLUDED.google_id IS NULL THEN customers.google_id WHEN EXCLUDED.google_id = '' THEN NULL ELSE EXCLUDED.google_id END,
    telegram_chat_id = CASE WHEN EXCLUDED.telegram_chat_id IS NULL THEN customers.telegram_chat_id ELSE EXCLUDED.telegram_chat_id END,
    name = CASE WHEN EXCLUDED.name IS NULL THEN customers.name WHEN EXCLUDED.name = '' THEN NULL ELSE EXCLUDED.name END,
    address = CASE WHEN EXCLUDED.address IS NULL THEN customers.address WHEN EXCLUDED.address = '' THEN NULL ELSE EXCLUDED.address END,
    birthday = CASE WHEN EXCLUDED.birthday IS NULL THEN customers.birthday ELSE EXCLUDED.birthday END,
    updated_at = CURRENT_TIMESTAMP
WHERE matched_customer.customer_id IS NOT NULL;

更新规则说明 #

• 传入字段为NULL：保留原字段值
• 传入字段为空字符串：将原字段设为NULL
• 传入字段有有效值：覆盖原字段值

批量处理时，将input_data替换为：

SELECT * FROM json_populate_recordset(NULL::customers, '[
    {"phone": "13800138000", "name": "张三"},
    {"email": "test@example.com", "address": ""}
]')

三、PHP自动匹配JSON字段导入 #

无需手动指定键名，直接将JSON传给PostgreSQL自动映射：

<?php
// 连接PostgreSQL
$pdo = new PDO('pgsql:host=localhost;dbname=your_db;user=your_user;password=your_pass');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// 获取API传入的原始JSON字符串
$jsonData = file_get_contents('php://input');

// 批量UPSERT的SQL语句
$sql = <<<SQL
WITH input_data AS (
    SELECT * FROM json_populate_recordset(NULL::customers, :json)
),
matched_customer AS (
    SELECT c.customer_id, i.*
    FROM customers c
    JOIN input_data i ON 
        (i.phone IS NOT NULL AND i.phone != '' AND c.phone = i.phone)
        OR (i.email IS NOT NULL AND i.email != '' AND c.email = i.email)
        OR (i.google_id IS NOT NULL AND i.google_id != '' AND c.google_id = i.google_id)
        OR (i.telegram_chat_id IS NOT NULL AND c.telegram_chat_id = i.telegram_chat_id)
)
INSERT INTO customers (phone, email, google_id, telegram_chat_id, name, address, birthday)
SELECT phone, email, google_id, telegram_chat_id, name, address, birthday
FROM input_data
ON CONFLICT (customer_id) DO UPDATE SET
    phone = CASE WHEN EXCLUDED.phone IS NULL THEN customers.phone WHEN EXCLUDED.phone = '' THEN NULL ELSE EXCLUDED.phone END,
    email = CASE WHEN EXCLUDED.email IS NULL THEN customers.email WHEN EXCLUDED.email = '' THEN NULL ELSE EXCLUDED.email END,
    google_id = CASE WHEN EXCLUDED.google_id IS NULL THEN customers.google_id WHEN EXCLUDED.google_id = '' THEN NULL ELSE EXCLUDED.google_id END,
    telegram_chat_id = CASE WHEN EXCLUDED.telegram_chat_id IS NULL THEN customers.telegram_chat_id ELSE EXCLUDED.telegram_chat_id END,
    name = CASE WHEN EXCLUDED.name IS NULL THEN customers.name WHEN EXCLUDED.name = '' THEN NULL ELSE EXCLUDED.name END,
    address = CASE WHEN EXCLUDED.address IS NULL THEN customers.address WHEN EXCLUDED.address = '' THEN NULL ELSE EXCLUDED.address END,
    birthday = CASE WHEN EXCLUDED.birthday IS NULL THEN customers.birthday ELSE EXCLUDED.birthday END,
    updated_at = CURRENT_TIMESTAMP
WHERE matched_customer.customer_id = customers.customer_id;
SQL;

// 执行参数化查询，避免SQL注入
$stmt = $pdo->prepare($sql);
$stmt->bindParam(':json', $jsonData, PDO::PARAM_STR);
$stmt->execute();

echo "处理完成，影响行数：" . $stmt->rowCount();
?>

核心优势 #

• 直接传递原始JSON，json_populate_recordset自动匹配同名字段，无视键顺序或缺失
• 参数化查询保证安全，兼容任意符合字段名的JSON结构

四、逻辑验证技巧 #

1. 单独运行input_data部分查询，确认JSON是否正确映射到表字段
2. 测试边界场景：空字符串字段是否被置为NULL、NULL字段是否保留原值、多匹配条件是否按优先级命中客户

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