PHP正则拆分街道数据插入location表：空值与批量ID匹配问题求助

咱们一步步拆解并解决你遇到的问题：

1. 核心问题：循环内全表插入导致主键冲突 #

你当前代码在while循环里执行INSERT INTO location SELECT id, ... FROM branch，这意味着每次循环都会把整个branch表的数据插入一次。第一次循环执行后，location表已经存入了所有branch的id记录，后续循环再执行时，因为location.id是主键，会触发主键重复错误，所以看起来只有第一条结果被插入（实际是第一次循环插入了所有数据，后面的都报错终止了）。

2. 正则表达式优化 #

你的正则可能会匹配到街道名末尾的多余空格，比如示例中的"Krakowska "（带空格），可以调整为更精准的正则，确保拆分出干净的街道名和门牌号：

preg_match('/^([^\d]+?)\s*(\d.*)?$/', $row['street'], $matches);

• ^([^\d]+?)：非贪婪匹配开头的非数字部分（街道名），避免末尾多余空格
• \s*：匹配街道名和门牌号之间的任意空格
• (\d.*)?：可选的门牌号部分，以数字开头，后面可以跟任意字符（比如A/B）

3. 空值处理：插入SQL的NULL而非字符串'NULL' #

当正则没有匹配到街道名或门牌号时，对应的数组元素不存在，这时候需要将变量设为NULL，并且在SQL语句中直接写入NULL（不要加单引号），否则会插入字符串'NULL'，而不是数据库的空值。

4. 关联branch.id与location.id的正确方式 #

应该在循环中每次处理branch的单条记录，获取对应的id，然后针对location表的该id执行插入或更新操作。

修改后的完整代码 #

<?php 
$db = mysqli_connect('localhost','root','','jakoscobslugi') or die('Error connecting to MySQL server.'); 

// 清空location表
$query0 = "TRUNCATE location"; 
mysqli_query($db,$query0) or die('Error query0.'); 

// 查询branch表的id、street、phone（需要关联id和phone字段）
$result = mysqli_query($db, "SELECT id, street, phone FROM branch;"); 

while($row = mysqli_fetch_assoc($result)) { 
    // 初始化变量为NULL
    $streetName = NULL;
    $streetNumber = NULL;
    
    // 执行正则拆分
    if (preg_match('/^([^\d]+?)\s*(\d.*)?$/', $row['street'], $matches)) {
        // 处理街道名：去除首尾空格
        if (!empty($matches[1])) {
            $streetName = trim($matches[1]);
        }
        // 处理门牌号：如果匹配到就赋值
        if (isset($matches[2])) {
            $streetNumber = $matches[2];
        }
    }

    // 打印调试信息
    echo "[". ($streetName ?? 'NULL') ."] [". ($streetNumber ?? 'NULL') ."]<br />"; 

    // 准备SQL语句：处理NULL值，避免单引号包裹
    $streetSql = $streetName === NULL ? 'NULL' : "'". mysqli_real_escape_string($db, $streetName) ."'";
    $numberSql = $streetNumber === NULL ? 'NULL' : "'". mysqli_real_escape_string($db, $streetNumber) ."'";

    // 针对当前branch的id执行插入/更新
    $query2 = "INSERT INTO location(id, street, house_number, phone_number) 
               VALUES({$row['id']}, {$streetSql}, {$numberSql}, '". mysqli_real_escape_string($db, $row['phone']) ."')
               ON DUPLICATE KEY UPDATE 
                   street = {$streetSql}, 
                   house_number = {$numberSql}, 
                   phone_number = '". mysqli_real_escape_string($db, $row['phone']) ."'";

    mysqli_query($db, $query2) or die('Error query2: ' . mysqli_error($db)); 
} 

// 关闭数据库连接
mysqli_close($db);
?>

代码关键点说明 #

• SQL注入防护：使用mysqli_real_escape_string()转义字符串变量，避免SQL注入风险
• 空值处理：通过三元判断，将PHP的NULL转换为SQL的NULL，而非字符串形式
• 单条记录处理：每次循环只处理branch的一条记录，通过id精准关联location的对应行
• 正则优化：确保街道名无多余空格，门牌号匹配更准确

额外建议：用MySQL原生正则函数实现（更高效） #

如果数据量较大，用MySQL原生的正则函数直接在SQL中处理会更高效，不需要PHP循环处理，示例SQL如下：

TRUNCATE location;

INSERT INTO location(id, street, house_number, phone_number)
SELECT 
    id,
    -- 提取街道名：去掉末尾空格和门牌号部分
    TRIM(REGEXP_REPLACE(street, '\\s*\\d.*$', '')) AS street,
    -- 提取门牌号：提取数字开头的部分
    REGEXP_SUBSTR(street, '\\d.*$') AS house_number,
    phone
FROM branch
ON DUPLICATE KEY UPDATE
    street = TRIM(REGEXP_REPLACE(branch.street, '\\s*\\d.*$', '')),
    house_number = REGEXP_SUBSTR(branch.street, '\\d.*$'),
    phone_number = branch.phone;

这种方式不需要PHP循环，直接通过SQL完成所有操作，性能更好，也避免了PHP和数据库之间的多次交互。

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