create table tb_goods_base(id int auto_increment primary key, goods_name varchar(20),  category varchar(20), price int);
create table tb_order_master(order_id int auto_increment primary key, money int, goods_id int, receiver_province varchar(6), receiver_city varchar(6), receiver varchar(20));
create table tb_user(id int auto_increment primary key, name varchar(20),  age int, gender varchar(1));

insert into tb_user (name, age, gender) values ("張三", 21, "男");
insert into tb_user (name, age, gender) values ("李四", 22, "女");
insert into tb_user (name, age, gender) values ("王五", 23, "男");
insert into tb_goods_base (goods_name, category, price) values ("華為手機(jī)","家電", 5888);
insert into tb_goods_base (goods_name, category, price) values ("中國(guó)李寧","服裝", 499);
insert into tb_goods_base (goods_name, category, price) values ("雙匯火腿","食品", 15);

insert into tb_order_master (money, goods_id, receiver_province, receiver_city, receiver) values (5888, 1, "130000", "130200", "張三");
insert into tb_order_master (money, goods_id, receiver_province, receiver_city, receiver) values (499, 2, "130000", "130800", "李四");
insert into tb_order_master (money, goods_id, receiver_province, receiver_city, receiver) values (15, 3, "110000", 110100, "王五");

create table tb_areas_provinces (id int auto_increment primary key, provinceid varchar(6), province varchar(20));
create table tb_areas_city (id int auto_increment primary key, cityid varchar(6), city varchar(20), provinceid varchar(6));
create table tb_user_address (id int auto_increment primary key, user_id int, province_id varchar(6), city_id varchar(6), address varchar(20));

insert into tb_areas_provinces (provinceid, province) values ("110000", "北京市");
insert into tb_areas_provinces (provinceid, province) values ("120000", "天津市");
insert into tb_areas_provinces (provinceid, province) values ("130000", "河北省");

insert into tb_areas_city (cityid, city, provinceid) values ("110100", "市轄區(qū)", "110000");
insert into tb_areas_city (cityid, city, provinceid) values ("130200", "唐山市", "130000");
insert into tb_areas_city (cityid, city, provinceid) values ("130800", "承德市", "130000");


insert into tb_user_address (user_id, province_id, city_id, address) values (1, "110000", "110100", "清河毛紡廠");
insert into tb_user_address (user_id, province_id, city_id, address) values (2, "130000", "130200", "里二胡同");
insert into tb_user_address (user_id, province_id, city_id, address) values (3, "130000", "130800", "避暑山莊");

select u.name, p.province, c.city, ua.address from tb_user as u, tb_user_address as ua, tb_areas_provinces as p, tb_areas_city as c where u.id = ua.user_id and ua.province_id = p.provinceid and ua.city_id = c.cityid;

select o.order_id, o.receiver,p.province, c.city from tb_order_master as o, tb_areas_provinces as p, tb_areas_city as c where o.receive_province=p.provinceid and o.receiver_city=c.cityid;

　　　　這個(gè)時(shí)候就報(bào)錯(cuò)了， 報(bào)錯(cuò)的原因是：夸數(shù)據(jù)節(jié)點(diǎn)的多表查詢(xún)，在執(zhí)行sql的時(shí)候，Mycat并不知道，將這條sql給哪一個(gè)數(shù)據(jù)節(jié)點(diǎn)處理。

　　　　解決方式：將一些數(shù)據(jù)量少，并且一旦確定了就很少改變的表，設(shè)置為全局表，全局表可以在每個(gè)數(shù)據(jù)節(jié)點(diǎn)上都能訪問(wèn)。而本案例中的省/市表就符合這個(gè)特性，中國(guó)每個(gè)城市的編碼一旦確定，幾乎就不會(huì)變化，這樣的數(shù)據(jù)表，我們就可以設(shè)置為全局表。全局表會(huì)存在每一個(gè)數(shù)據(jù)節(jié)點(diǎn)上。

　　　　如果要設(shè)置全局表，只需要在schema.xml設(shè)置邏輯表的時(shí)候加上 type="global"參數(shù)即可

　　　　說(shuō)明1： 因?yàn)槭∈邢嚓P(guān)的數(shù)據(jù)表需要在dn1,dn2,dn3 三個(gè)數(shù)據(jù)節(jié)點(diǎn)上，所以dataNode這里要設(shè)置dn1,dn2,dn3三個(gè)節(jié)點(diǎn)

　　　　說(shuō)明2：在table標(biāo)簽內(nèi)添加type="global"屬性

　　　　說(shuō)明3：因?yàn)橹暗腶reas的表，都要變成全局表，所以需要數(shù)據(jù)清空在重新添加測(cè)試數(shù)據(jù)

　　　　說(shuō)明4：因?yàn)樾薷牧薓ycat配置，所以需要重新啟動(dòng)Mycat

　　　　重新創(chuàng)建tb_areas_provinces和tb_areas_city兩個(gè)表

create table tb_areas_provinces (id int auto_increment primary key, provinceid varchar(6), province varchar(20));
create table tb_areas_city (id int auto_increment primary key, cityid varchar(6), city varchar(20), provinceid varchar(6));　　

　　　　說(shuō)明5：這時(shí)候就會(huì)發(fā)現(xiàn)tb_areas_procinces和tb_areas_city出現(xiàn)在了三個(gè)數(shù)據(jù)節(jié)點(diǎn)上

　　　　再次插入數(shù)據(jù)進(jìn)行多表查詢(xún)測(cè)試：

insert into tb_areas_provinces (provinceid, province) values ("110000", "北京市");
insert into tb_areas_provinces (provinceid, province) values ("120000", "天津市");
insert into tb_areas_provinces (provinceid, province) values ("130000", "河北省");

insert into tb_areas_city (cityid, city, provinceid) values ("110100", "市轄區(qū)", "110000");
insert into tb_areas_city (cityid, city, provinceid) values ("130200", "唐山市", "130000");
insert into tb_areas_city (cityid, city, provinceid) values ("130800", "承德市", "130000");　

　　　　會(huì)發(fā)現(xiàn)插入的這些測(cè)試數(shù)據(jù)，會(huì)在dn1,dn2,dn3的每個(gè)數(shù)據(jù)節(jié)點(diǎn)的表中都添加成功。

　　　　現(xiàn)在就可以順利的進(jìn)行多表查詢(xún)了。