MySQL存储过程，从一个表中读取并插入到其他表中

I have this following code in PHP and I want to convert this into MySQL stored procedure. I do not want to check on duplication error at db level error code due to the requirement of the application, it has to be done the same way as is done in PHP.

我在PHP中有以下代码,我想将其转换为MySQL存储过程。由于应用程序的要求,我不想检查数据库级错误代码的重复错误,它必须以与PHP中相同的方式完成。

$sql2 = "SELECT zip__ID FROM zip WHERE zip_code='" . $zip . "' ";
$result2 = mysql_query($sql2);
if (mysql_num_rows($result2) == 0) {
    $sql3 = "INSERT INTO zips SET zip_code='" . ($zip) . "'";
    mysql_query($sql3);
    $newZip = mysql_insert_id();
} else {
    $row2 = mysql_fetch_array($result2);
    $newZip = $row2['zip_id'];
}

//Update: May 6, 2015. I have total 6 tables.

//更新:2015年5月6日。我共有6张桌子。

Table 1 contains fields like zip, city, state, country. This is in varchar format. |ID|ZIP|CITY|STATE|COUNTRY| |1|33430|Fort Lauderdale|FL|USA

表1包含zip,city,state,country等字段。这是varchar格式。 | ID | ZIP | CITY | STATE |国家| | 1 | 33430 |劳德代尔堡|佛罗里达州|美国

There are four more tables that are: Table 2: zips (id,zip_code) Table 3: cities (id,city_name) Table 4: state (id,state_name) Table 5: countries (id,country_name)

还有四个表:表2:zip(id,zip_code)表3:cities(id,city_name)表4:state(id,state_name)表5:countries(id,country_name)

I want to insert the date from table 1 into table 6, which is the replica of table 1. The only difference is that in Table 6, I want to insert IDs of the data in table 1. Hence, for every insertion to be made in Table 6, I have to run a query in the 4 tables (2,3,4 and 5). For instance, if the zip code in Table 1 is 33430 and it does not exist in Table 2, first I need to insert it, get its ID and then insert in Table 6.

我想将表1中的日期插入到表6中,表6是表1的副本。唯一的区别是在表6中,我想在表1中插入数据的ID。因此,对于每个要进行的插入在表6中,我必须在4个表(2,3,4和5)中运行查询。例如,如果表1中的邮政编码是33430并且表2中不存在,首先我需要插入它,获取其ID然后插入表6中。

I have to do the same for all fields in Table 1.

我必须对表1中的所有字段执行相同的操作。

So if I do it in PHP, what I will do it:

所以如果我用PHP做,我会做的:

$sql = "SELECT * FROM Table1";
$result = mysql_query($sql);
while($row=mysql_fetch_array($result){
    $zip = $row['zip'];
    $city = $row['city'];
    $state = $row['state'];
    $country = $row['country'];

    //Process zip code
    $sql2 = "SELECT zip_id FROM zips WHERE zip_code='" . $zip . "' ";
    $result2 = mysql_query($sql2);
    if (mysql_num_rows($result2) == 0) {
        $sql3 = "INSERT INTO zips SET zip_code='" . ($zip) . "'";
        mysql_query($sql3);
        $newZip = mysql_insert_id();
    } else {
        $row2 = mysql_fetch_array($result2);
        $newZip = $row2['zip_id'];
    }
    mysql_free_result($result2);

    //Process city
    $sql2 = "SELECT city_id FROM cities WHERE city_name='" . $city . "' ";
    $result2 = mysql_query($sql2);
    if (mysql_num_rows($result2) == 0) {
        $sql3 = "INSERT INTO cities SET city_name='" . ($city) . "'";
        mysql_query($sql3);
        $newCity = mysql_insert_id();
    } else {
        $row2 = mysql_fetch_array($result2);
        $newCity = $row2['city_id'];
    }
    mysql_free_result($result2);

    //process other two tables......

}mysql_free_result($result);

1 个解决方案

#1

I'm no MySQL expert either, but this is what I could find. You have to check out the syntax though. But I want to stress this. I Googled 2 sources, and I typed this out for you. If I could do it, I'm sure you could've done it as well.

我也不是MySQL专家,但这是我能找到的。你必须检查语法。但我想强调一下。我用Google搜索了2个来源,然后我为您输入了这个。如果我能做到,我相信你也可以做到。

CREATE PROCEDURE UpdateTable6()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE i, a, b, c, d INT;
DECLARE va, vb, vc, vd CHAR(<yourmax>);
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

DECLARE cur1 CURSOR FOR SELECT id, zip, city, state, country FROM table1;

OPEN cur1;
read_loop: LOOP
  FETCH cut1 into i, va, vb, vc, vd;
  IF done THEN
    LEAVE read_loop;
  END IF;

  --REPEAT THIS FOR ALL 4 TABLES
  IF (SELECT 1 = 1 FROM Table2 WHERE zip_code=va) THEN
    BEGIN
      SELECT a = TableID FROM Table2 WHERE zip_code=va;
    END;
  ELSE
    BEGIN
      INSERT INTO Table2 (zip_code) VALUES(va);
      SELECT a = LAST_INSERT_ID();
    END;
  END IF;

  --CHECK DETAILS IN TABLE6
  IF (SELECT 1 = 1 FROM Table6 WHERE id=i) THEN
    BEGIN
      UPDATE TABLE6 set zip_id = a, city_id = b, state_id = c, country_id = d where id = i
    END;
  ELSE
    BEGIN
      INSERT INTO Table6 (zip_id, city_id, state_id, country_id ) VALUES(a, b, c, d);
    END;
  END IF;
END LOOP;

CLOSE cur1;
END;

My source where https://dev.mysql.com/doc/refman/5.0/en/cursors.html

我的源代码https://dev.mysql.com/doc/refman/5.0/en/cursors.html