把其余5张表的数据写到w表里. 下面贴出表结构以及目前使用的存储程序.但是效率还是不是很好.有更好的方法吗?求具体思路或者代码.
-- phpMyAdmin SQL Dump
-- version 3.5.1
-- http://www.phpmyadmin.net
--
-- 主机: localhost
-- 生成日期: 2012 年 09 月 16 日 11:21
-- 服务器版本: 5.5.24-log
-- PHP 版本: 5.4.3SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;--
-- 数据库: `weishiji`
--DELIMITER $$
--
-- 存储过程
--
CREATE DEFINER=`root`@`localhost` PROCEDURE `i`()
BEGIN
DECLARE pid INT;
DECLARE cid INT;
DECLARE ctid INT;
DECLARE tid INT;
DECLARE vid INT;
DECLARE pname varchar(100) CHARACTER SET utf8;
DECLARE ctname varchar(100) CHARACTER SET utf8;
DECLARE cname varchar(100) CHARACTER SET utf8;
DECLARE tname varchar(100) CHARACTER SET utf8;
DECLARE vname varchar(100) CHARACTER SET utf8;
DECLARE dn int DEFAULT 0;
/*定义查询语句 */
DECLARE c_p CURSOR FOR select ID,Name from province;
DECLARE c_c cursor for select ID,Name from city where ProvinceID=pid;
DECLARE c_ct cursor for select ID,Name from county where CityID=cid;
DECLARE c_t cursor for select ID,Name from town where CountyID=ctid;
DECLARE c_v cursor for select ID,Name from village where TownID=tid;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET dn=1;
insert into w(name,parentid,path) values('中国',0,'0,1,');
/*开始游标 */
OPEN c_p;
c_p_loop:LOOP
FETCH c_p INTO pid,pname;
if dn = 1 then
LEAVE c_p_loop;
END IF;
/*插入省级数据*/
insert into w(name,parentid) values(pname,1);
SELECT LAST_INSERT_ID() into @pid;
update w set path=concat('0,1,',@pid,',') where id = @pid;
/*插入市级数据*/
OPEN c_c;
c_c_loop:LOOP
fetch c_c into cid,cname;
if dn=1 then
lEAVE c_c_loop;
end if;
insert into w(name,parentid) values(cname,@pid);
SELECT LAST_INSERT_ID() into @cid;
update w set path=concat('0,1,',@pid,',',@cid,',') where id = @cid;
/*插入市区数据*/
OPEN c_ct;
c_ct_loop:LOOP
fetch c_ct into ctid,ctname;
if dn=1 then
LEAVE c_ct_loop;
end if;
insert into w(name,parentid) values(ctname,@cid);
SELECT LAST_INSERT_ID() into @ctid;
update w set path=concat('0,1,',@pid,',',@cid,',',@ctid,',') where id = @ctid;
/*插入乡镇数据*/
OPEN c_t;
c_t_loop:LOOP
fetch c_t into tid,tname;
if dn=1 then
LEAVE c_t_loop;
end if;
insert into w(name,parentid) values(tname,@ctid);
SELECT LAST_INSERT_ID() into @tid;
update w set path=concat('0,1,',@pid,',',@cid,',',@ctid,',',@tid,',') where id = @tid;
/*插入村数据*/
open c_v;
c_v_loop:LOOP
fetch c_v into vid,vname;
if dn=1 then
LEAVE c_v_loop;
end if;
insert into w(name,parentid) values(vname,@tid);
SELECT LAST_INSERT_ID() into @vid;
update w set path=concat('0,1,',@pid,',',@cid,',',@ctid,',',@tid,',',@vid,',') where id = @vid;
end LOOP;
close c_v;
/*插入村数据*/
set dn=0;
END LOOP;
close c_t;
/*插入乡镇数据*/
set dn=0;
END LOOP;
close c_ct;
/*插入市区数据*/
/*内循环跳出设置为0,继续进行下次循环*/
set dn=0;
END LOOP;
CLOSE c_c;
/*插入市级数据*/
/*内循环跳出设置为0,继续进行下次循环*/
set dn = 0;
end LOOP;
CLOSE c_p;
END$$DELIMITER ;-- ----------------------------------------------------------
-- 表的结构 `city`
--CREATE TABLE IF NOT EXISTS `city` (
`ID` int(11) NOT NULL,
`Name` varchar(255) NOT NULL,
`Code` varchar(3) NOT NULL,
`ProvinceID` int(11) NOT NULL,
PRIMARY KEY (`ID`),
KEY `ProvinceID` (`ProvinceID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- ----------------------------------------------------------
-- 表的结构 `county`
--CREATE TABLE IF NOT EXISTS `county` (
`ID` int(11) NOT NULL,
`Name` varchar(255) NOT NULL,
`Code` varchar(3) NOT NULL,
`CityID` int(11) NOT NULL,
PRIMARY KEY (`ID`),
KEY `CityID` (`CityID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- ----------------------------------------------------------
-- 表的结构 `province`
--CREATE TABLE IF NOT EXISTS `province` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`Name` varchar(255) NOT NULL,
`Code` varchar(3) NOT NULL,
`CountryID` varchar(3) DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=35 ;-- ----------------------------------------------------------
-- 表的结构 `town`
--CREATE TABLE IF NOT EXISTS `town` (
`ID` int(11) NOT NULL,
`Name` varchar(255) NOT NULL,
`Code` varchar(3) NOT NULL,
`CountyID` int(11) NOT NULL,
PRIMARY KEY (`ID`),
KEY `CountyID` (`CountyID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- ----------------------------------------------------------
-- 表的结构 `village`
--CREATE TABLE IF NOT EXISTS `village` (
`ID` int(11) NOT NULL,
`Name` varchar(255) NOT NULL,
`Code` varchar(3) NOT NULL,
`TownID` int(11) NOT NULL,
PRIMARY KEY (`ID`),
KEY `TownID` (`TownID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- ----------------------------------------------------------
-- 表的结构 `w`
--CREATE TABLE IF NOT EXISTS `w` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(32) NOT NULL,
`parentid` int(11) DEFAULT '0',
`path` varchar(120) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;--
-- 限制导出的表
----
-- 限制表 `city`
--
ALTER TABLE `city`
ADD CONSTRAINT `city_ibfk_1` FOREIGN KEY (`ProvinceID`) REFERENCES `province` (`ID`) ON UPDATE CASCADE;--
-- 限制表 `county`
--
ALTER TABLE `county`
ADD CONSTRAINT `county_ibfk_1` FOREIGN KEY (`CityID`) REFERENCES `city` (`ID`) ON UPDATE CASCADE;--
-- 限制表 `town`
--
ALTER TABLE `town`
ADD CONSTRAINT `town_ibfk_1` FOREIGN KEY (`CountyID`) REFERENCES `county` (`ID`) ON UPDATE CASCADE;--
-- 限制表 `village`
--
ALTER TABLE `village`
ADD CONSTRAINT `village_ibfk_1` FOREIGN KEY (`TownID`) REFERENCES `town` (`ID`) ON UPDATE CASCADE;/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
-- phpMyAdmin SQL Dump
-- version 3.5.1
-- http://www.phpmyadmin.net
--
-- 主机: localhost
-- 生成日期: 2012 年 09 月 16 日 11:21
-- 服务器版本: 5.5.24-log
-- PHP 版本: 5.4.3SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;--
-- 数据库: `weishiji`
--DELIMITER $$
--
-- 存储过程
--
CREATE DEFINER=`root`@`localhost` PROCEDURE `i`()
BEGIN
DECLARE pid INT;
DECLARE cid INT;
DECLARE ctid INT;
DECLARE tid INT;
DECLARE vid INT;
DECLARE pname varchar(100) CHARACTER SET utf8;
DECLARE ctname varchar(100) CHARACTER SET utf8;
DECLARE cname varchar(100) CHARACTER SET utf8;
DECLARE tname varchar(100) CHARACTER SET utf8;
DECLARE vname varchar(100) CHARACTER SET utf8;
DECLARE dn int DEFAULT 0;
/*定义查询语句 */
DECLARE c_p CURSOR FOR select ID,Name from province;
DECLARE c_c cursor for select ID,Name from city where ProvinceID=pid;
DECLARE c_ct cursor for select ID,Name from county where CityID=cid;
DECLARE c_t cursor for select ID,Name from town where CountyID=ctid;
DECLARE c_v cursor for select ID,Name from village where TownID=tid;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET dn=1;
insert into w(name,parentid,path) values('中国',0,'0,1,');
/*开始游标 */
OPEN c_p;
c_p_loop:LOOP
FETCH c_p INTO pid,pname;
if dn = 1 then
LEAVE c_p_loop;
END IF;
/*插入省级数据*/
insert into w(name,parentid) values(pname,1);
SELECT LAST_INSERT_ID() into @pid;
update w set path=concat('0,1,',@pid,',') where id = @pid;
/*插入市级数据*/
OPEN c_c;
c_c_loop:LOOP
fetch c_c into cid,cname;
if dn=1 then
lEAVE c_c_loop;
end if;
insert into w(name,parentid) values(cname,@pid);
SELECT LAST_INSERT_ID() into @cid;
update w set path=concat('0,1,',@pid,',',@cid,',') where id = @cid;
/*插入市区数据*/
OPEN c_ct;
c_ct_loop:LOOP
fetch c_ct into ctid,ctname;
if dn=1 then
LEAVE c_ct_loop;
end if;
insert into w(name,parentid) values(ctname,@cid);
SELECT LAST_INSERT_ID() into @ctid;
update w set path=concat('0,1,',@pid,',',@cid,',',@ctid,',') where id = @ctid;
/*插入乡镇数据*/
OPEN c_t;
c_t_loop:LOOP
fetch c_t into tid,tname;
if dn=1 then
LEAVE c_t_loop;
end if;
insert into w(name,parentid) values(tname,@ctid);
SELECT LAST_INSERT_ID() into @tid;
update w set path=concat('0,1,',@pid,',',@cid,',',@ctid,',',@tid,',') where id = @tid;
/*插入村数据*/
open c_v;
c_v_loop:LOOP
fetch c_v into vid,vname;
if dn=1 then
LEAVE c_v_loop;
end if;
insert into w(name,parentid) values(vname,@tid);
SELECT LAST_INSERT_ID() into @vid;
update w set path=concat('0,1,',@pid,',',@cid,',',@ctid,',',@tid,',',@vid,',') where id = @vid;
end LOOP;
close c_v;
/*插入村数据*/
set dn=0;
END LOOP;
close c_t;
/*插入乡镇数据*/
set dn=0;
END LOOP;
close c_ct;
/*插入市区数据*/
/*内循环跳出设置为0,继续进行下次循环*/
set dn=0;
END LOOP;
CLOSE c_c;
/*插入市级数据*/
/*内循环跳出设置为0,继续进行下次循环*/
set dn = 0;
end LOOP;
CLOSE c_p;
END$$DELIMITER ;-- ----------------------------------------------------------
-- 表的结构 `city`
--CREATE TABLE IF NOT EXISTS `city` (
`ID` int(11) NOT NULL,
`Name` varchar(255) NOT NULL,
`Code` varchar(3) NOT NULL,
`ProvinceID` int(11) NOT NULL,
PRIMARY KEY (`ID`),
KEY `ProvinceID` (`ProvinceID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- ----------------------------------------------------------
-- 表的结构 `county`
--CREATE TABLE IF NOT EXISTS `county` (
`ID` int(11) NOT NULL,
`Name` varchar(255) NOT NULL,
`Code` varchar(3) NOT NULL,
`CityID` int(11) NOT NULL,
PRIMARY KEY (`ID`),
KEY `CityID` (`CityID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- ----------------------------------------------------------
-- 表的结构 `province`
--CREATE TABLE IF NOT EXISTS `province` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`Name` varchar(255) NOT NULL,
`Code` varchar(3) NOT NULL,
`CountryID` varchar(3) DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=35 ;-- ----------------------------------------------------------
-- 表的结构 `town`
--CREATE TABLE IF NOT EXISTS `town` (
`ID` int(11) NOT NULL,
`Name` varchar(255) NOT NULL,
`Code` varchar(3) NOT NULL,
`CountyID` int(11) NOT NULL,
PRIMARY KEY (`ID`),
KEY `CountyID` (`CountyID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- ----------------------------------------------------------
-- 表的结构 `village`
--CREATE TABLE IF NOT EXISTS `village` (
`ID` int(11) NOT NULL,
`Name` varchar(255) NOT NULL,
`Code` varchar(3) NOT NULL,
`TownID` int(11) NOT NULL,
PRIMARY KEY (`ID`),
KEY `TownID` (`TownID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- ----------------------------------------------------------
-- 表的结构 `w`
--CREATE TABLE IF NOT EXISTS `w` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(32) NOT NULL,
`parentid` int(11) DEFAULT '0',
`path` varchar(120) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;--
-- 限制导出的表
----
-- 限制表 `city`
--
ALTER TABLE `city`
ADD CONSTRAINT `city_ibfk_1` FOREIGN KEY (`ProvinceID`) REFERENCES `province` (`ID`) ON UPDATE CASCADE;--
-- 限制表 `county`
--
ALTER TABLE `county`
ADD CONSTRAINT `county_ibfk_1` FOREIGN KEY (`CityID`) REFERENCES `city` (`ID`) ON UPDATE CASCADE;--
-- 限制表 `town`
--
ALTER TABLE `town`
ADD CONSTRAINT `town_ibfk_1` FOREIGN KEY (`CountyID`) REFERENCES `county` (`ID`) ON UPDATE CASCADE;--
-- 限制表 `village`
--
ALTER TABLE `village`
ADD CONSTRAINT `village_ibfk_1` FOREIGN KEY (`TownID`) REFERENCES `town` (`ID`) ON UPDATE CASCADE;/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货